Comparing Entity Framework 4 New Stored Procedure Support to LINQ to SQL

The next version of Entity Framework has many new features, many of which are enabling it to catch up with features previously available in other frameworks like LINQ to SQL.  One of these new features is the updated stored procedure support.  In previous versions of EF, working with stored procedures was quite limited and really only usable with CRUD operations that were mapped to already defined entities.  With EF 4, you can start with your stored procedure and have the designer automatically generate return types.  Essentially it will “sense” the shape of the parameters and (if applicable) SELECT statement and generate types that match it or allow you to map it to an already existing type.  This is certainly a great new feature to be adding but, at the same time, these stored procedure features have been in LINQ to SQL since version 1.

Although the features are similar between EF 4 and LINQ to SQL, the implementation isn’t exactly the same.  Suppose we have a typical Contact object that looks like this:

   1:  public class Contact
   2:  {
   3:      public Contact()
   4:      {
   5:          this.Addresses = new List<Address>();
   6:      }
   8:      public int ContactId { get; set; }
   9:      public string FirstName { get; set; }
  10:      public string LastName { get; set; }
  11:      public string Email { get; set; }
  12:      public string Title { get; set; }
  13:      public ICollection<Address> Addresses { get; set; }
  14:  }

and stored procedure to insert a new contact that takes all of these properties as parameters (with an output parameter for the ContactID since it’s a PK Identity).  If we use the typical edmx approach, we get generated code for this stored procedures that looks like this:

   1:  public int SaveContact(ObjectParameter contactID, global::System.String firstName, global::System.String lastName, global::System.String company, global::System.String title, global::System.String email)
   2:  {
   3:      ObjectParameter firstNameParameter;
   4:      if (firstName != null)
   5:      {
   6:          firstNameParameter = new ObjectParameter("FirstName", firstName);
   7:      }
   8:      else
   9:      {
  10:          firstNameParameter = new ObjectParameter("FirstName", typeof(global::System.String));
  11:      }
  13:      ObjectParameter lastNameParameter;
  14:      if (lastName != null)
  15:      {
  16:          lastNameParameter = new ObjectParameter("LastName", lastName);
  17:      }
  18:      else
  19:      {
  20:          lastNameParameter = new ObjectParameter("LastName", typeof(global::System.String));
  21:      }
  23:      ObjectParameter companyParameter;
  24:      if (company != null)
  25:      {
  26:          companyParameter = new ObjectParameter("Company", company);
  27:      }
  28:      else
  29:      {
  30:          companyParameter = new ObjectParameter("Company", typeof(global::System.String));
  31:      }
  33:      ObjectParameter titleParameter;
  34:      if (title != null)
  35:      {
  36:          titleParameter = new ObjectParameter("Title", title);
  37:      }
  38:      else
  39:      {
  40:          titleParameter = new ObjectParameter("Title", typeof(global::System.String));
  41:      }
  43:      ObjectParameter emailParameter;
  44:      if (email != null)
  45:      {
  46:          emailParameter = new ObjectParameter("Email", email);
  47:      }
  48:      else
  49:      {
  50:          emailParameter = new ObjectParameter("Email", typeof(global::System.String));
  51:      }
  53:      return base.ExecuteFunction("SaveContact", contactID, firstNameParameter, lastNameParameter, companyParameter, titleParameter, emailParameter);
  54:  }

It’s good that’s generated code and we don’t have to worry about writing that ourselves but it’s not the prettiest code to look at.  Let’s take a look at the exact same stored procedure call in a LINQ to SQL data context:

   1:  [Function(Name = "dbo.SaveContact")]
   2:  public int SaveContact(ref Nullable<int> contactID, string firstName, string lastName, string company, string title, string email)
   3:  {
   4:      IExecuteResult result = this.ExecuteMethodCall(this, (MethodInfo)MethodInfo.GetCurrentMethod(), contactID, firstName, lastName, company, title, email);
   5:      contactID = (Nullable<int>)result.GetParameterValue(0);
   6:      return (int)result.ReturnValue;
   7:  }

The first most obvious difference is that’s 54 lines of code for EF 4 and 7 lines of code for LINQ to SQL.  Second, LINQ to SQL allows you to just pass in the primitive types to the built-in ExecuteMethodCall() method whereas EF 4 wants a list of ObjectParameter types for its ExecuteFunction() method.  Third, LINQ to SQL can reflect over the parameters to figure out, by name, which parameters map to which stored procedure parameters (LINQ to SQL also provides the [Parameter] attribute if the names or types differ and need to be explicitly mapped). EF, on the other hand, wants the string for the name explicitly set (e.g., “FirstName”, “LastName”, etc.). Also, EF 4 specifies the name of the stored procedure as a parameter to the ExecuteFunction() method, whereas LINQ to SQL specifies it in the [Function] attribute which decorates the method.  Finally, the last interesting difference that I see is in regards to the contact ID property.  That is the PK Identity column that gets assigned from the database on insert.  LINQ to SQL automatically exposes that as a ref parameter (which is an API that makes sense from a C# perspective) and assigns the values after the stored procedure has been invoked via the GetParameterValue() method.  EF 4, on the other hand, exposes that parameter as an actual ObjectParameter (rather than a “ref int”) and all the rest of the parameters as their natural primitives. So the responsibility is on the caller to create the ObjectParameter for the PK property but not for the other ones. This allows the caller to have a reference to the variable so that, in the case of output parameters, it can get at the new value that was just assigned by the database.

Certainly there are pros and cons of each approach. But I doubt I would be putting myself in the minority to say that I prefer the (much) more succinct API that LINQ to SQL provides. So, while I love the fact that EF 4 is now allowing me to call stored procedures in a similar way to what I had with LINQ to SQL, I’m not absolutely thrilled with the API.

But, digging a little deeper, how can I add some re-usable methods to be able to give my EF 4 a more succinct API?

The first thing I want to do is to get an easy way to create all of those ObjectParameters automatically from their primitive types without all the IF statements checking to see if each one is null. If I want to add on to the designer generated code, I can create this method in a partial class. Otherwise, if I’m using the “code only” approach, I can put this method in my own base ObjectContext class xxxxx:

   1:  public class SmartObjectContext : ObjectContext
   2:  {
   3:      protected ObjectParameter[] GetObjectParameters(MethodInfo methodInfo, params object[] parameters)
   4:      {
   5:          var objectParameters = new ObjectParameter[parameters.Length];
   7:          var methodParams = methodInfo.GetParameters();
   8:          for (int i = 0; i < parameters.Length; i++)
   9:          {
  10:              var paramName = methodParams[i].Name;
  11:              var paramType = methodParams[i].ParameterType;
  12:              var paramValue = parameters[i];
  13:              if (paramValue == null)
  14:              {
  15:                  objectParameters[i] = new ObjectParameter(paramName, paramType);
  16:              }
  17:              else
  18:              {
  19:                  objectParameters[i] = new ObjectParameter(paramName, parameters[i]);
  20:              }
  21:          }
  22:          return objectParameters;
  23:      }
  24:  }

This method is clearly not as robust as the LINQ to SQL implementation which optionally takes into account parameter attributes if they exist to further customize the mapping, but it gets the job done.  This now allows my (previously 54 lines) SaveContact() method to now look like this:

   1:  public int SaveContact(ref int contactID, string firstName, string lastName, string company, string title, string email)
   2:  {
   3:      var objParams = this.GetObjectParameters((MethodInfo)MethodInfo.GetCurrentMethod(), contactID, firstName, lastName, company, title, email);
   4:      var result = this.ExecuteFunction("SaveContact", objParams);
   5:      contactID = (int)objParams.First(o => o.Name == "contactID").Value;
   6:      return result;
   7:  }

Just by adding this one method to a base class (or current class if you’re using a partial) I was able to reduce my SaveContact() method from 54 lines to 7 lines, and allow the API to be primitives for all parameters including a “ref int” for the PK Identity rather than an ObjectParameter.

I can also simplify this even further if I’m invoking stored procedures that are not assigning output parameters. For example, suppose you have a simple GetContact stored procedure which takes a single integer contactID as the parameter to the stored procedure. The auto-generated EF 4 code looks like this:

   1:  public ObjectResult<Contact> GetContact(Nullable<global::System.Int32> contactID)
   2:  {
   3:      ObjectParameter contactIDParameter;
   4:      if (contactID.HasValue)
   5:      {
   6:          contactIDParameter = new ObjectParameter("ContactID", contactID);
   7:      }
   8:      else
   9:      {
  10:          contactIDParameter = new ObjectParameter("ContactID", typeof(global::System.Int32));
  11:      }
  13:      return base.ExecuteFunction<Contact>("GetContact", contactIDParameter);
  14:  }

Notice this is using a different ExecuteFunction() method that is generic and is strongly-typed to the Contact class in this instance.  I can create my own generic ExecuteFunction() method that encapsulates the creation of the ObjectParameter(s) and also put that in my base (or partial) ObjectContext:

   1:  public ObjectResult<T> ExecuteFunction<T>(string functionName, MethodInfo methodInfo, params object[] parameters)
   2:  {
   3:      var objParams = this.GetObjectParameters(methodInfo, parameters);
   4:      return base.ExecuteFunction<T>(functionName, objParams);
   5:  }

What this now allows me to do is to refactor my GetContact() method to now look like this:

   1:  public ObjectResult<Contact> GetContact(Nullable<int> contactID)
   2:  {
   3:      return this.ExecuteFunction<Contact>("GetContact", (MethodInfo)MethodInfo.GetCurrentMethod(), contactID);
   4:  }

Essentially this is now down to 1 meaningful line of code.

A couple of other things to keep in mind – first, the technique of these helper methods can be used regardless of whether you are using EF 4 in “data first”, “model first”, or “code only” scenarios.  Secondly, if you are using edmx files, EF 4 now allows you to customize the generated code that is produced via T4 templates.  So if you don’t like the code that the EF 4 designer is creating for you out of the box, change it!

One final note on EF 4 stored procedure support: apparently it does *not* support stored procedures that have multiple result sets – this also has been available since the first version of LINQ to SQL via IMultipleResults. I’m told this functionality can be added to EF with the EF Extensions library but I have yet to use it myself.

I’m still in the process of exploring all of the new features being added to EF 4. While some of them are quite interesting (e.g., fluent mappings, etc.), many of the other features being added have already been available in other frameworks like LINQ to SQL.  Definitely good to seem them being added to EF 4 now as well.

posted on Wednesday, December 16, 2009 11:24 PM Print
# re: Comparing Entity Framework 4 New Stored Procedure Support to LINQ to SQL
12/17/2009 12:20 PM
Good stuff, just waiting for the RTM but definitely something I've been wondering about how to simplify
# re: Comparing Entity Framework 4 New Stored Procedure Support to LINQ to SQL
1/8/2010 7:47 PM
hi , i have been hearing rumours about linq to sql , that it has no space in future versions of .net framework . please clearify it's current status .

# re: Comparing Entity Framework 4 New Stored Procedure Support to LINQ to SQL
1/8/2010 8:45 PM
@jamal - see my post here:
# re: Comparing Entity Framework 4 New Stored Procedure Support to LINQ to SQL
1/17/2011 1:39 PM
grate job ,thank u very much,it is grate if u can send me a sample code for this mail address
# re: Comparing Entity Framework 4 New Stored Procedure Support to LINQ to SQL
1/17/2011 1:39 PM
grate job ,thank u very much,it is grate if u can send me a sample code for this mail address ....................
# re: Comparing Entity Framework 4 New Stored Procedure Support to LINQ to SQL
1/17/2011 2:47 PM
@Kanchana - all the code is already in the blog post.
# re: Comparing Entity Framework 4 New Stored Procedure Support to LINQ to SQL
3/4/2011 9:15 AM
Fantastic article.

We should also remember that EF4 is a pure modelling tool and meant to be database neutral. That's why a lot of the generated code seems more complex. These 2 frameworks are not really comparable for that reason and why EF4 despite the time they need to develop it is definitely the future.
# re: Comparing Entity Framework 4 New Stored Procedure Support to LINQ to SQL
4/21/2015 12:58 AM
what is the use of:
this.Addresses = new List<Address>();
why need to use HashSet inside the constructor.
public ICollection<Address> Addresses { get; set; }
property as ICollection.
please reply. thanks!!!

Post Comment

Title *
Name *
Comment *  

View Steve Michelotti's profile on LinkedIn

profile for Steve Michelotti at Stack Overflow, Q&A for professional and enthusiast programmers

Google My Blog

Tag Cloud