Longing to know

Using Stored Procedures in the latest version of Entity Framework is pretty doable. You lose a lot in terms of flexibility, but sometimes there are trade-offs that one must make. This week I was playing around with converting an application that used a Repository pattern with EF, Lambdas, and IQueryables to StoredProcs for all CRUD operations.

Here are some interesting bits that I learned.

If your models match the stored procedure parameter names, then there is nearly zero configuration. For example, all you have to do is tell EF’s ModelBuilder the names of your CUD operations:

modelBuilder 
  .Entity<MyEntity>() 
  .MapToStoredProcedures(s => 
    { 
      s.Update(u => u.HasName("spMyEntity_Update")); 
      s.Delete(d => d.HasName("spMyEntity_Delete")); 
      s.Insert(i => i.HasName("spMyEntity_Insert")); 
    }); 

If your StoredProc parameter names are different, that is handled as well. You essentially create a mapping between Model property names and StoredProc parameter names. AFAIK, there are no DataAnnotations that can handle this, so it has to be done with the ModelBuilder. Here’s how it might look if my “Id” on MyEntity is named different from the spMyEntity_Update StoredProc:

modelBuilder 
  .Entity<MyEntity>() 
  .MapToStoredProcedures(s => 
    { 
      s.Update(u => u.HasName("spMyEntity_Update"))
		.Parameter(e => e.Id, "entity_id");
      s.Delete(d => d.HasName("spMyEntity_Update")); 
      s.Insert(i => i.HasName("spMyEntity_Update")); 
    }); 

The Parameter settings are chainable. The nice aspect of this is that using these methods, you are able to hook into the EF DbContext and its other mechanisms for change tracking. In my particular, I utilize a custom DbContext and inherited Entity classes and set some properties on those entities. This all continues to work even with StoredProcs.

Things get more interesting, and more difficult, when using StoredProcs to select data. One major limitation of EF is that it currently does not support, or honor, the [Column] annotation when data mapping. So, the only easy way to use StoredProcs for data retrieval is if your Model property names match your StoredProc return Column names exactly. This is unfortunate, but is still not a deal breaker. Using Reflection and your own Data mapping mechanism would be one way to overcome this limitation, but, imho at that point, you’ve eliminated some of the benefits of utilizing the framework.

However, to make queries we using the SqlQuery method that is accessed via the DbContext.Database object. There are other helper methods in EF like ExecuteSql, ExecuteFunction, and ExecuteSqlCommand, but those are not very useful for return sets of data.

If we created, for example a simple StoredProc that allowed querying MyEntity by id or name, we could get a return set back like so:

var retSet = myContext.Database.SqlQuery<MyEntity>(
    "EXEC spMyEntity_Get @entity_id, @entity_name", 
    new SqlParameter("entity_id", id ?? DbNull.Value), 
    new SqlParameter("entity_name", name ?? DbNull.Value)
);

That’s pretty straight forward. Creating a Repository method, IRepository Get() for example, is a bit more involved. For this purpose, I created a dynamic/Expando object that could be passed into the method. Since the ExpandoObject implements IDictionary, it’s easy enough to look at all of the keys and match them up to the expected parameters in the StoredProc by key name.

public List<MyEntity> Get(dynamic parameters) {
	var dictionary = parameters as IDictionary<string, object>;
	string name = null;
	string id = null;
	foreach (var param in parameters)
	{
		var key = param.Key.ToLower();
		switch (key)
		{
			case "id":
			{
				id = param.Value.ToString();
				break;
			}
			case "name":
			{
				name = param.Value.ToString();
				break;
			}
		}
	}
	var retSet = myContext.Database.SqlQuery<MyEntity>(
		"EXEC spMyEntity_Get @entity_id, @entity_name", 
		new SqlParameter("entity_id", id ?? DbNull.Value), 
		new SqlParameter("entity_name", name ?? DbNull.Value)
	);
}

And we could call the method like so:

var parameters = new ExpandoObject() { name = "test" };
var entities = _repository.Get(parameters);

Overall, this approach works pretty well. It also keeps the Repository generic enough to play nicely with IoC containers like Ninject. Mocking/Unit tests are easily represented too.