Migrating from LINQ to SQL to the Entity Framework: Stored Procedures for data retrieval
LINQ to SQL supports a wide range of scenarios that involve stored procedure mapping and usage. This also happens to be an area where there is a considerable gap between the features offered by LINQ to SQL and the Entity Framework. In this post, we will look at what’s possible and what’s not in the Entity Framework in terms of using stored procedures.
Bear in mind that as we evolve the Entity Framework, these scenarios that we outline will become considerably easier. Among the many things we are working on in Entity Framework in .NET 4.0 / Visual Studio 10 includes improved support around Stored Procedures and store functions.
Stored Procedures that return Entity Collections
In LINQ to SQL, for mapping stored procedures that return entities, the approach is usually pretty straightforward:
1. You drag the stored procedure from Server Explorer to the LINQ to SQL class designer.
2. You then set the properties on the generated method to indicate the return type is of the desired entity as shown below.
3. You then execute and work with the results:
var data = new AdventureDataContext();
ISingleResult<Product> products = data.NewProductsThisWeek();
IEnumerable<Product> list = products.ToList();
Console.WriteLine("1 of {0} = {1}",
list.Count(),list.First().ProductName);In Entity Framework, you can achieve the same using the following steps:
1. From the EF Model Designer workspace press the right-mouse button and choose Update Model from Database… then choose the stored procedure you wish to use from the Add tab in the Update Wizard and press Finish.
2. Choose Add > Function Import… and then select the stored procedure name from the drop-down list.
3. Then type the name again in the Function Import Name text box (to create the method wrapper) and choose the entity return type in the Entities drop-down list and press OK.
4. Execute and work with the results (changes in bold)
var data = new AdventureModel();
ObjectResult<Product> products = data.NewProductsThisWeek();
IEnumerable<Product> list = products.ToList();
Console.WriteLine("1 of {0} = {1}", list.Count(),
list.First().ProductName);Stored Procedure Return Types for non Entity custom types
In LINQ to SQL, the default return type from a mapped stored procedure is a custom class that is automatically created with the appropriate property names matching the column names during the mapping process.
e.g.
var data = new AdventureDataContext();
IEnumerable<UniqueEmailAddressesResult> emailList =
data.ExecuteCommand("UniqueEmailAddresses")
Console.Write("Address {0} found {1} times",
emailList.First().EmailAddress, emailList.First().Count);The Entity Framework does not automatically create return types whilst mapping stored procedures into functions.
It is possible to create the types by hand and to write additional code to execute the stored procedure and materialize the results. More information can be found at http://blogs.msdn.com/meek/archive/2008/03/26/ado-entity-framework-stored-procedure-customization.aspx.
This is something we are hoping to improve in the next release of the Entity Framework.
Stored Procedures that return multiple result sets
LINQ to SQL supports mapping of stored procedures that return multiple result sets by using multiple ResultType attributes against a method that is decorated with a Function attribute.
This is a manual coding process and is not supported by either the LINQ to SQL class designer or SQL Metal command-line tool.
The Entity Framework does not support this feature at this time and it is advised to split the stored procedure into individual parts that each returns a single entity collection.
Alternatively, please refer to the EFExtensions project on MSDN Code Gallery that includes support for this functionality: http://code.msdn.microsoft.com/EFExtensions
Stored Procedures that return scalar data
The LINQ to SQL Designer allows you to map stored procedures that return scalars. While the Entity Framework does have mapping support to specify stored procedures and functions that return scalars, automatic code-generation of methods that will allow you to use the mapping is currently missing. However, this is something that is being strongly considered for the next release of Entity Framework.
In the meanwhile, the simple solution to this is to write extension methods on your context that will use the connection in order to execute the stored procedure/function and return results.
Executing Stored Procedures Directly as Commands
LINQ to SQL allows you to directly execute stored procedures using the ExecuteCommand method.
For example:
var data = new AdventureDataContext();
data.ExecuteCommand("ResetOrders");In Entity Framework, it is possible to implement ExecuteCommand as an extension method on your ObjectContext, leaving the existing code unchanged by adding this code to a static method in your project:
public static int ExecuteCommand(this ObjectContext objectContext,
string command) {
DbConnection connection = ((EntityConnection)objectContext.Connection).StoreConnection;
bool opening = (connection.State == ConnectionState.Closed);
if (opening)
connection.Open();
DbCommand cmd = connection.CreateCommand();
cmd.CommandText = command;
cmd.CommandType = CommandType.StoredProcedure;
try {
return cmd.ExecuteNonQuery();
}
finally {
if (opening && connection.State == ConnectionState.Open)
connection.Close();
}
}Once again, ExecuteCommand is being considered as an enhancement for a future release of the Entity Framework.
That covers some of the high level aspects of migrating the mapping and usage stored procedures from LINQ to SQL to the Entity Framework. We will cover the topic of using stored procedures for Create/Update/Delete behavior customization in a separate post.
Hope you find this helpful. Please send us feedback on what you would like to see in the Entity Framework to make some of this easier.
In our next post, we will look at how to migrate code that uses EntitySet and EntityRef types in LINQ to SQL to manage associations between entity types. Stay tuned and let us know what you think!
- The ADO.NET Team
