Partager via

Migrating from LINQ to SQL to the Entity Framework: Stored Procedures for data retrieval

  • Article

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.

migration1

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.

migration2

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.

migration3

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 https://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: https://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

Comments

  • Anonymous
    December 02, 2008
    PingBack from http://blog.a-foton.ru/index.php/2008/12/02/migrating-from-linq-to-sql-to-the-entity-framework-stored-procedures-for-data-retrieval/

  • Anonymous
    December 02, 2008
    The comment has been removed

  • Anonymous
    December 02, 2008
    Why migrate your L2S DAL over to something else if it is working for you? I don't understand.

  • Anonymous
    December 02, 2008
    Chris, If LINQ to SQL works for you, by all means you should feel free to continue to use it. The two technologies are fundamentally very different in how they approach database access and conceptual modeling. Because of the differences, there are some common issues that customers run into when migrating from LINQ to SQL to the Entity Framework and we are using this series to provide hints and recommendations on how to work through some of those challenges. Faisal Mohamood

  • Anonymous
    December 02, 2008
    The comment has been removed

  • Anonymous
    December 02, 2008
    Faisal, Sorry for the misunderstanding. I was questioning the pervasive doom-and-gloom attitude of some current L2S users toward EF, and specifically the first comment by John.  I understand the different problem domain that EF is looking to serve, and I like what I see coming up with v2. I also appreciate the efforts that are being made to educate and inform those interested in making the switch. I just don't understand the vitriol coming from some of the L2S user base.

  • Anonymous
    December 02, 2008
    The comment has been removed

  • Anonymous
    December 02, 2008
    The comment has been removed

  • Anonymous
    December 05, 2008
    "The Entity Framework does not automatically create return types whilst mapping stored procedures into functions." This is such a glaring omission.  For this reason alone I have to stick with LINQ->SQL.  This was a such a time saver in LINQ->SQL.

  • Anonymous
    December 09, 2008
    i would love to get my hands on the persons that are making these decisions about linq and ef. We just rewrote our application.... They need to ensure an upgrade utility to ef. man what a nightmare...

  • Anonymous
    December 10, 2008
    The comment has been removed

  • Anonymous
    December 11, 2008
    The comment has been removed

  • Anonymous
    December 12, 2008
    DAO, ADO, ODBC, OLEDB, Entity Framework, Linq to Sql, anyone remember the webstore in Exchange? WinFs anyone? I think it's fair to say the choice for data access has been overwhelming and confusing over the years. Will tomorrows entity framework be yesterdays DAO or Ling to Sql for that matter? I understand that EF is more than a data access layer, but given the state of version 1.0 I think it's fair enough for people to moan a bit. The EF looks promising for lots of reasons, but the simple fact is it doesn't do what we want right now and given the inevitable lag in adopting VS2010 it's unlikely to be adopted for some time in most shops, even if it is a stated direction (as were some of those other deprecated or lost technologies) I can't help feeling that all this effort would be better spent on proper OO database development. Or is entity framework a step in that direction ....?

  • Anonymous
    December 17, 2008
    considerable issues moving from Linq to SQL to Entity Framework

  • Anonymous
    December 17, 2008
    The comment has been removed

  • Anonymous
    December 20, 2008
    如果对于sp在EDM的映射有一些了解的话，可以很清楚的明白，在EDM中映射一个sp返回的类型包括：Entity Type，Scalars Type,None；这三中类型分别对应的sp返回内容对应到数据库，表的层面就是：（Entity）一张表的所有字段或若干字段(必须包括主键）;（Scalars）返回一个常量（int, varchar）可以是数据库的一个字段，可以是sum,count,left...等函数操作的结果;(none)自然是什么都不返回.这里自然就有一个严重的问题，对于返回（multiple

  • Anonymous
    January 01, 2009
    The comment has been removed

  • Anonymous
    January 01, 2009
    In using your example: Executing Stored Procedures Directly as Commands It doesn't work! I run the stored procedure, I even used: objectContext.SaveChanges() And upon completion of the changes made to the database, I use a linq to entities query to pull back the data, and it returns the data prior to the running of the stored procedure. It does not UPDATE! If I return the same query using ado.net SQLCommand, it does return the latest changes. Why isn't the entity model updating with the stored procedure changes?????

  • Anonymous
    January 07, 2009
    I can not use LINQ-SQL because IBM doesn't have the capabilities to do DataContext using a designer. But that doesn't stop me from using LINQ on other sections of code so hopefully LINQ is here to stay. Also hope IBM gets on the ball and gets a proper provider out to mirror SQL-Server provider and designer.

  • Anonymous
    January 14, 2009
    考虑在项目中使用ADO.NETEF，但是怎么都用不顺手，甚至比L2S少了很多关键功能。 我先列几个让我很郁闷的： 1）存储过程自动封装是一个很常用的功能，在ADO.NETEF中可以和L2S一样...

  • Anonymous
    January 23, 2009
    I'm currently trying to use the Entity Framework for a business application. The task of trying to insert a row into a  many to many table with no primary key seems mind bogglingly complicated. From what I've read on numerous forums, if I want to achieve this I will need to: a) Create a stored procedure containing a basic insert statement b) Create a function import to my model c) Because my stored procedure doesn't return anything I'll then need to create a partial class where I need to write code which is very similar to standard ADO.NET code Is this correct? I find myself writing more code than I did when all we had was the basic ADO objects. My personal opinion so far (and I'm not one to base my opinions on anything I've read) is that the EF is slow, clumsy and shoddily put together. The designer is also a nightmare and the entire model is almost unworkable in any kind of team using source control.

  • Anonymous
    February 04, 2009
    I bloged stored peoc usage here:http://ygizhitsa.spaces.live.com/blog/cns!8A7B4991A271531A!203.entry. You might find it interesting

  • Anonymous
    February 04, 2009
    I blogged stored peoc usage here:http://ygizhitsa.spaces.live.com/blog/cns!8A7B4991A271531A!203.entry. You might find it interesting

  • Anonymous
    March 10, 2009
    The comment has been removed

  • Anonymous
    March 26, 2009
    I am using VS 2008 SP1 with .Net 3.5 SP1 and this just does not work.  I created a stored procedure for each function type and even validated the methods creation in the XML in the edmx files.  I get a good compile but nothing even close to my function name is available in the namespace or class. Nothing on the web indicates how to correct this problem.  This is extremely frustrating when you can't even get a simple stored procedure to work in a new technology. Has anyone gotten any stored procedures to work in VS 2008 and ADO.Net Entity Framework?

  • Anonymous
    May 21, 2009
    would love to get my hands on the persons that are making these decisions about linq and ef. We just rewrote our application.... They need to ensure an upgrade utility to ef. man what a nightmare...

  • Anonymous
    July 04, 2009
    This is fantastic. I am going to add it to my sites. Thanks!

  • Anonymous
    July 19, 2009
    Is it possible to have the same field in both entities? In your example, I would like to have Name both in Product and in ProductImages.

  • Anonymous
    July 23, 2009
    The comment has been removed

  • Anonymous
    July 23, 2009
        would love to get my hands on the persons that are making these decisions about linq and ef. We just rewrote our application....    They need to ensure an upgrade utility to ef.    man what a nightmare...

  • Anonymous
    October 20, 2009
    I must be in the minority because I am not a happy L2S user.  Our database is accessed strictly via stored procs.  After living with poor support in VS2008 for object changes (had to use Huagati add-in) we just realized that all SPs are run as text, not SP calls.  If EF fixes that, I would migrate immediately.

  • Anonymous
    October 21, 2009
    See also this post ::http://ledomoon.blogspot.com/2009/10/stored-procedure-mapping-and-usage-into.html

  • Anonymous
    December 18, 2009
    would love to get my hands on the persons that are making these decisions about linq and ef. We just rewrote our application.... They need to ensure an upgrade utility to ef. man what a nightmare...

  • Anonymous
    January 04, 2010
    Oh boy, I was considering using EF in our new application, but from what I read its really a piece of s... Linq to SQL seems alright, but I thinking I might just be better off sticking to ADO.NET with Typed Datasets of not generating my own stuff using CodeSmith or using NHibernate.  I'm not a microsoft hater by any means, but these stuff-ups just keep coming and coming... You'd think one would learn from their mistakes with releasing half-baked technologies.

  • Anonymous
    February 23, 2010
    Thanks a lot for the wonderful information

  • Anonymous
    March 24, 2010
    For maximum performance and maximum scalability (thousands of website connections), which is best for executing CRUD operations?

  1. Ling-to-SQL without using SQL Stored Procedures.
  2. ADO.NET to call SQL Stored Procedures.
  • Anonymous
    July 03, 2013
    The comment has been removed