Entity Framework & Stored Procedures?

The entities themselves don't allow stored procedures for selecting. There are several reasons. Off the top of my head: 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 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 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 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.

The entities themselves don't allow stored procedures for selecting. There are several reasons. Off the top of my head: 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. 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. 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.

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.

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.

I went through the Quickstart. I saw nothing about using stored procedures to select data. – Dismissile Nov 5 '10 at 17:34 Hmmm, the Quickstart is sorely lacking there.

Try this page, it seems to have a more comprehensive set of links: msdn.microsoft. Com/en-us/library/bb738448. Aspx – AJ.

Nov 5 '10 at 17:40.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.