Object Structure and Stored Procedures

Question

In the Entity Framework designer, I noticed that you can map stored procedures for Insert, Update, and Delete operations. Is there a way to do this for Select operations, and is there also a new direction for the database access code in which we no longer write stored procedures for our main selection operations?

The company I'm working on is pretty adamant about using stored procedures for every database operation, even though the Entity Framework makes calls safe by calling sp_executesql.

It seems that both LINQ to SQL and Entity Framework have moved away from using stored procedures to select data. Is this an exact statement?

To clarify my question:

I have a table in my database called Product. I use the wizard in the Entity Framework to create my models ... so I now have an Entity called Product. When I make the following request:

db.Products.SingleOrDefault(p => p.Id == 1); 

It generates code similar to:

 EXEC sp_executesql N'SELECT * FROM Product' 

When I really want to do something like:

 EXEC up_GetProduct @Id = 1 

If this cannot be done using SingleOrDefault, I'm fine. I would prefer the following:

 db.Products.GetProduct(1); 

Is this something that is usually done or is most people just dynamically generating SQL?

Answer 1

Entities themselves do not allow stored procedures to be selected. There are several reasons. Above my head:

• How would you add a where clause to your stored procedure? You need to either do this with LINQ for objects, or have an inefficient query, or somehow add a custom mapping to the properties of the stored procedure. Although this is not impossible, it definitely represents an implementation difficulty.
• Entities can have relationships that are used to perform joins in queries. With stored procedures, a similar problem occurs again. If you have Order and OrderItem , how do you join? You run SelectOrder and for each order that you run SelectOrderItem (1 + n queries), or you have one stored procedure that returns both, either as one result set with duplicate Order data, or two result sets. Then you must specify how this maps to objects. If you need to manually specify a mapping, it defeats the purpose of the relationship with the objects you were supposed to configure.
• How do you pager? With LINQ to Entities, you can open IQueryable for the business level or user interface level (up to you). Then you perform LINQ filtering, which modifies SQL and makes it efficient. With the help of stored procedures, you once again have to somehow manually determine all this or perform filtering using LINQ for objects.
• LINQ allows you to select new { o.Column1, o.Column2 } . This generates SQL, which selects only those 2 columns that you need. Very useful if you have a BLOB / VARCHAR(MAX) . With stored procedures, you usually return each column (wastefully in different ways). You can split stored procedures into GetOrderDetailMain and GetOrderDetailImages (or similar), but it is not possible to create each combination.

In my opinion, if you use the EF framework, let CRUD do it for you. Use stored procedures for complex logic, such as full-text search, query too slow, etc. Otherwise, you will not get much benefit from this.

Change Of course, there are advantages to stored procedures. They are prepared / pre-compiled, the "Database APIs" are clearly defined, you do not need to provide access to tables (although with SP CRUD you can do the same things), it’s easier to debug / configure queries, it’s easier to know which one is requesting the tuning, perform batch processing, etc. For a simple CRUD, however, you should ask yourself if the runtime / management costs of stored procedures are worth it.

Answer 2

You can use stored procedures to select in the Entity Framework. I had great success with the import function , complex types and stored procedures with EF4. And they are also not difficult to configure.

I warn you about one thing: if you are using SQL Server 2005, you may have to recompile any stored procedure that you want to use with SET FMTONLY OFF if you want to allow the modeled entity to create a complex type for you, see this question for details.

See Quickstart for more information.