Problems With CRUD

What is CRUD?

• CRUD is an acronym for Create Read Update Delete.
• The idea is that you create these four data functions for each table in your database.
• Then, your C# code can access the CRUD and you don't have to write any other database functions.
• The Entity Framework is losely based in this idea.

What is the Entity Framework?

• A C# class is created in your project with the same name as every table in your database.
• The fields in your C# class match the fields in your database.
• You can use these to easily read and write to the database.
• You can write queries in C# using these classes similar to queries in SQL.

The Problems With CRUD:

• Your data is stored in the database normalized, but the user wants to see the data denormalized.
• The database can do this easily by using INNER JOIN to join several tables.
• Using INNER JOIN is outside of what CRUD offers.
• If you change the structure of your database, such as normalize or denormalize a column, you break the CRUD functionality.
• The WHERE clause when reading from a table is often more complex than the Read routine you originally wrote.
• It's a bunch of wasted code you'll never use. You'll be writing stored procedures anyways.

The Problems With Entity Framework:

• If you use stored procedures for everything, you can query the database to see what stored procedures use a certain table and column. But if you use Entity Framework, it's more difficult to search the code because
  • you get false matches in your search due to UI objects or business logic using the same names.
  • your text search can't be as complex as the WHERE clause on a database search when looking for where the field is used. For instance, you can list all the stored procedures using a certain table name, a certain column name, and have the word INSERT in them.
• Linq To SQL runs slower than a stored procedure because it generates an SQL statement that the database has to parse.
• How do you write automated tests for Linq To SQL? Your automated test has to run it and then compare the results with the database. Comparing the results with the database takes more time to write in C# than if it were another stored procedure testing it.
• Linq To SQL can only be used for simple cases. You can't create a temporary table with it like in a stored procedure.
• Linq To SQL and Entity Framework seem like it was written for developers who don't know how to write a stored procedure.
• It creates a lot of extra code that has to be updated whenever you change your table strucuture.
• If you normalize or denormalize a column, it breaks the Entity Framework code.