Entity Framework & Stored Procedures

Question

I noticed in the Entity Framework designer that you can map stored procedures for Insert, Update, and Delete operations. Is there any way to do this for Select operations as well, or is there a new direction for database access code where we don't really write stored procedures for our basic select operations any more?

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

It seems like both LINQ to SQL and Entity Framework have moved away from using stored procedures for selecting the data. Is this an accurate statement?

Just to clarify my question:

I have a table in my database called Product. I use the wizard in the Entity Framework to generate my models...so I now have an Entity called Product. When I do the following query:

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 it's not possible to do that using SingleOrDefault I'm fine with that. I'd much rather have the following:

db.Products.GetProduct(1);

Is this something that is normally done or do most people just have it dynamically generate the SQL?

Answer 1

The entities themselves don't allow stored procedures for selecting. There are several reasons. Off the top of my head:

1. How would you add a where clause to your stored procedure? You would either have to do it with LINQ to Objects and have an inefficient query, or somehow be able to add custom mapping to stored procedure properties. Though not impossible, it definitely introduces some implementation complexity.
2. The entities can have relationships which are used to do joins in the queries. With stored procedures you have a similar problem again. If you have Order and OrderItem tables, how do you join? You either run SelectOrder and for each order you run SelectOrderItem (1+n queries), or have one stored procedure that returns both, either as one result set with duplicated Order data, or two result sets. Then you have to specify how that maps to the entities. If you have to manually specify the mapping, it defeats the purpose of the entity relationships you had to set up.
3. How do you do paging? With LINQ to Entities, you can expose IQueryable to the business layer or UI layer (up to you). You then do your LINQ filtering which modifies the SQL and makes it efficient. With stored procedures, you would once again have to somehow manually define all this, or do the filtering with LINQ to objects.
4. LINQ allows you to select new { o.Column1, o.Column2 }. That generates SQL which only selects those 2 columns you need. Very useful if you have a BLOB/VARCHAR(MAX). With stored procedures you usually return every column (wasteful in many ways). You CAN split into GetOrderDetailMain and GetOrderDetailImages (or similar) stored procedures, but it's not feasible to create each combination.

In my opinion, if you'll be using the EF framework, let it do CRUD for you. Use stored procedures for complex logic like full text searching, a specific query which is too slow, etc. Otherwise you won't get much benefit from it.

Edit: Of course there are benefits to stored procedures. They are preparsed/precompiled, well defined "database API", you don't need to give access to tables (though with SP CRUD, you can in effect do the same things), easier to debug/tune the queries, easier to know which queries to tune, can do batch processing, etc. For simple CRUD, though, you have to ask yourself if the time-to-implement/manage overhead of stored procedures is worth it.

Answer 2

You can certainly use stored procedures for selects in Entity Framework. I have had great success with function imports, complex types, and stored procedures with EF4. And, they're not too difficult to set up, either.

I will warn you of one thing: if you are using SQL Server 2005, you may have to recompile any stored procedure you want to use with SET FMTONLY OFF if you want to allow the entity modeler to generate a complex type for you. See this question for more detail on that.

See the Quickstart for more information.