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:
public class Contact
{
public Contact()
{
this.Addresses = new List<Address>();
}
public int ContactId { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public string Email { get; set; }
public string Title { get; set; }
public ICollection<Address> Addresses { get; set; }
}
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:
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)
{
ObjectParameter firstNameParameter;
if (firstName != null)
{
firstNameParameter = new ObjectParameter("FirstName", firstName);
}
else
{
firstNameParameter = new ObjectParameter("FirstName", typeof(global::System.String));
}
ObjectParameter lastNameParameter;
if (lastName != null)
{
lastNameParameter = new ObjectParameter("LastName", lastName);
}
else
{
lastNameParameter = new ObjectParameter("LastName", typeof(global::System.String));
}
ObjectParameter companyParameter;
if (company != null)
{
companyParameter = new ObjectParameter("Company", company);
}
else
{
companyParameter = new ObjectParameter("Company", typeof(global::System.String));
}
ObjectParameter titleParameter;
if (title != null)
{
titleParameter = new ObjectParameter("Title", title);
}
else
{
titleParameter = new ObjectParameter("Title", typeof(global::System.String));
}
ObjectParameter emailParameter;
if (email != null)
{
emailParameter = new ObjectParameter("Email", email);
}
else
{
emailParameter = new ObjectParameter("Email", typeof(global::System.String));
}
return base.ExecuteFunction("SaveContact", contactID, firstNameParameter, lastNameParameter, companyParameter, titleParameter, emailParameter);
}
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:
[Function(Name = "dbo.SaveContact")]
public int SaveContact(ref Nullable<int> contactID, string firstName, string lastName, string company, string title, string email)
{
IExecuteResult result = this.ExecuteMethodCall(this, (MethodInfo)MethodInfo.GetCurrentMethod(), contactID, firstName, lastName, company, title, email);
contactID = (Nullable<int>)result.GetParameterValue(0);
return (int)result.ReturnValue;
}
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:
public class SmartObjectContext : ObjectContext
{
protected ObjectParameter[] GetObjectParameters(MethodInfo methodInfo, params object[] parameters)
{
var objectParameters = new ObjectParameter[parameters.Length];
var methodParams = methodInfo.GetParameters();
for (int i = 0; i < parameters.Length; i++)
{
var paramName = methodParams[i].Name;
var paramType = methodParams[i].ParameterType;
var paramValue = parameters[i];
if (paramValue == null)
{
objectParameters[i] = new ObjectParameter(paramName, paramType);
}
else
{
objectParameters[i] = new ObjectParameter(paramName, parameters[i]);
}
}
return objectParameters;
}
}
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:
public int SaveContact(ref int contactID, string firstName, string lastName, string company, string title, string email)
{
var objParams = this.GetObjectParameters((MethodInfo)MethodInfo.GetCurrentMethod(), contactID, firstName, lastName, company, title, email);
var result = this.ExecuteFunction("SaveContact", objParams);
contactID = (int)objParams.First(o => o.Name == "contactID").Value;
return result;
}
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:
public ObjectResult<Contact> GetContact(Nullable<global::System.Int32> contactID)
{
ObjectParameter contactIDParameter;
if (contactID.HasValue)
{
contactIDParameter = new ObjectParameter("ContactID", contactID);
}
else
{
contactIDParameter = new ObjectParameter("ContactID", typeof(global::System.Int32));
}
return base.ExecuteFunction<Contact>("GetContact", contactIDParameter);
}
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:
public ObjectResult<T> ExecuteFunction<T>(string functionName, MethodInfo methodInfo, params object[] parameters)
{
var objParams = this.GetObjectParameters(methodInfo, parameters);
return base.ExecuteFunction<T>(functionName, objParams);
}
What this now allows me to do is to refactor my GetContact() method to now look like this:
public ObjectResult<Contact> GetContact(Nullable<int> contactID)
{
return this.ExecuteFunction<Contact>("GetContact", (MethodInfo)MethodInfo.GetCurrentMethod(), contactID);
}
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.
