A few years ago at DevConnections I had a long talk with Gert Drapers (from the SQL Server team) about EF when it was still in an early beta.
I'm now at DevConnections in Las Vegas and caught up with Gert again and had an interesting follow up conversation about EF now that it is released. I really like getting the perspective of folks who bring a different expertise to the table. We talked a lot about stored procs especially the DBA perspective.
The EF designer currently [handily] supports a narrow set of stored procedures for reading and writing. The procedures need to align with entities in the data model.
Read Stored Procs often return all different shapes of data . You can map a read stored procedure to an existing entity if its resultset exactly matches the entity . Then you can call this sproc as a function of the object context, passing in any required parameters.
You can also do function mapping for read stored procs that return a scalar value or randomly shaped data. The designer supports this as well with the Function Mapping wizard, however these functions are not surfaced as methods in your objectcontext class. Instead, you need to call them with EntityClient using ExecuteScalar or ExecuteReader. (The latter was a trick I discovered when experimenting. When doing the mapping, just select maps to "None" in the wizard.)
If the entity doesn't exist, I have been creating a view in the db to represent the resultset and letting the wizard create the entity & entity set & mappings for me. Then I can do the function mapping between the sproc and the entity and call the function as necessary.
Either way however, as a function, these don't benefit from any of the querying capabilities of EF. You can query the results in memory using LINQ to Object, but any additional LINQ methods you apply to the functions will get processed locally and not be part of the store command. Nor do they benefit from the deferred execution that queries give us.
Gert made, what now seems like a totally obvious point and may also be obvious to others . He said
Why not just query against the views? As long as you've got the view, why not just use that instead of the function?
Rather than being tied to the function (stored procedures) and their parameter(s), you can query against the view any way you want providing any additional filters or other operators that you like, which will be part of the store command and be executed on the server. Additionally, you can use LINQ to Entities or ObjectQueries against any of the views, rather than being forced to use EntityClient. The only downside is that you don't get the function arguments to let you know what parameters are normally expected for the procedure.
