Using Entity Framework Code First with Stored Procedures that have Output Parameters

I’m working with a team on a new customer project that uses Entity Framework Code First on the backend for CRUD operations. It’s been a nice ORM to work with since it really simplifies the process of mapping relational objects to CLR objects and lets you do it through fluent mappings. Working with Plain Old CLR Objects (POCOs) is something I’ve always liked since they keep the code base super clean and shiny. I can’t say that I really miss the .edmx files that I used previously with the database first or model first approaches.

As good as the framework has been, I was adding a feature the other day and ended up hitting a brick wall trying to accomplish what appeared to be a simple task. I tweeted about the problem and didn’t find any simple solutions so I thought I’d put together a post that details what I ended up doing since several people expressed interest in hearing about potential solutions. In a nutshell, I needed to call a stored procedure that had some fairly involved SQL queries in it (which is why a stored procedure was used instead of a LINQ query). That’s easy to do with EF Code First using the built-in SqlQuery() method. It allows the resultset returned from a stored procedure to be mapped to a type as shown next: 

var items = DataContext.Database.SqlQuery<MyItem>(
    "MySproc @p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7",
    userId, subId, fromDueDate, toDueDate, showHistory, currentPage, pageSize, totalCount);

However, the stored procedure that the application needed to call included an output parameter named TotalCount that was used for paging operations. Here’s the general structure of the procedure. Notice that the final parameter is an output parameter: 

CREATE PROCEDURE [dbo].[MySproc]
    (
        @UserID int,
        @SubID int,
        @FromDueDate datetime, 
        @ToDueDate datetime,
        @ShowHistory bit = 0,
        @CurrentPage int = 1,
        @PageSize int = 100,
        @TotalCount int OUTPUT
    )
AS
BEGIN

    DECLARE @MyTableVariable TABLE 
    (
       ...
    )

    SELECT ...
END

I’ve used output parameters many times over the years and wasn’t too worried about it but as I wrote code to call the procedure I found that the output parameter wasn’t getting a value assigned to it after the procedure executed. I played around with different SqlParameter variables so that I could control the direction of the output parameter but started getting “parameter is missing” errors. I spent some time trying everything I could think of without success (I’m one of those who refuses to give up on an issue – but this one kicked my butt). After tweeting about the problem and asking for advice (Twitter can be quite useful in situations like this), one of my data access MVP friends messaged me and let me know that output parameters had issues in the current release of EF Code First but that the issue would be resolved with an upcoming update. That was great to hear, but I couldn’t wait for an update so I had to come up with some type of work around. Here’s what I ended up doing. 

My Output Parameter Workaround

Entity Framework Code first does a great job of mapping resultsets to custom types and I didn’t want to lose that functionality since it’s a huge time saver. The stored procedure being called returned a lot of data and writing the mapping code manually could lead to maintenance headaches down the road if the fields changed. I wrote a simple reflection routine to map data from a DbDataReader into a type but came across an article (I Googled it on Bing :-)) by Nick Harrison that had a nice reflection method that I decided to use instead. I converted it into an extension method so that it was available to all DbDataReader instances and modified the code to use generics more and do a few extra checks as PropertyInfo objects were iterated through. Although reflection is frowned on by some, it performs fine for me in this scenario (I’ve always said that classic ASP wasn’t even compiled yet it ran fine and is still used by many sites). Here’s what I ended up going with for the extension method:

public static class DataReaderExtensions
{
    public static List MapToList(this DbDataReader dr) where T : new()
    {
        if (dr != null && dr.HasRows)
        {
            var entity = typeof(T);
            var entities = new List();
            var propDict = new Dictionary<string, PropertyInfo>();
            var props = entity.GetProperties(BindingFlags.Instance | BindingFlags.Public);
            propDict = props.ToDictionary(p => p.Name.ToUpper(), p => p);

            while (dr.Read())
            {
                T newObject = new T();
                for (int index = 0; index < dr.FieldCount; index++)
                {
                    if (propDict.ContainsKey(dr.GetName(index).ToUpper()))
                    {
                        var info = propDict[dr.GetName(index).ToUpper()];
                        if ((info != null) && info.CanWrite)
                        {
                            var val = dr.GetValue(index);
                            info.SetValue(newObject, (val == DBNull.Value) ? null : val, null);
                        }
                    }
                }
                entities.Add(newObject);
            }
            return entities;
        }
        return null;
    }
}

The MapToList() extension method accepts a generic T type that determines the type that the data retrieved from the database should be mapped to. It creates a Dictionary object and then fills it with PropertyInfo objects from the type that we’re trying to map to. From there it iterates through the DataReader stream and maps fields to object properties using reflection. Each row found in the DataReader creates a new object which is then added into a List that is returned from the method.

The code that uses the MapToList() method (including creating parameters and setting the direction for the output parameter) is shown next. The Connection object exposed by EF Code First provides a CreateCommand() method that can be used to drop back into ADO.NET objects such as DbCommand. Once the SqlParameter objects are set it calls ExecuteReader() and then calls the MapToList() extension method to map the resulting stream to a custom type: 

using (DataContext)
{
    //Had to go this route since EF Code First doesn't support output parameters 
    //returned from sprocs very well at this point
    using (DataContext.Database.Connection)
    {
        DataContext.Database.Connection.Open();
        DbCommand cmd = DataContext.Database.Connection.CreateCommand();
        cmd.CommandText = "MySproc";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add(new SqlParameter("UserID", userID));
        cmd.Parameters.Add(new SqlParameter("SubID", subdivisionID));
        cmd.Parameters.Add(new SqlParameter("FromDueDate", fromDueDate));
        cmd.Parameters.Add(new SqlParameter("ToDueDate", toDueDate));
        cmd.Parameters.Add(new SqlParameter("ShowHistory", showHistory));
        cmd.Parameters.Add(new SqlParameter("CurrentPage", currentPage));
        cmd.Parameters.Add(new SqlParameter("PageSize", pageSize));
        var totalCountParam = new SqlParameter ("TotalCount", 0) { Direction = ParameterDirection.Output};
        cmd.Parameters.Add(totalCountParam);

        List<Task> tasks;
        using (var reader = cmd.ExecuteReader())
        {
            tasks = reader.MapToList();
        }
        //Access output variable after reader is closed
        totalCount = (totalCountParam.Value == null) ? 0 : Convert.ToInt32(totalCountParam.Value);
        return tasks;
    }
}

I’m hoping that the next release of EF Code First will let us handle output parameters more easily. Until then, this gets the job done and isn’t too bad to work with. If anyone knows of a trick to get output parameters to work without doing this extra work please leave a comment with any details. I’m hoping I missed something that allows for doing this type of thing but haven’t found that silver bullet yet. 

Update

One of the best things about blogs, twitter, etc. is the ability to share code and ideas. Rick Strahl (the awesome Rick Strahl!) had a similar chunk of mapping code that he modified after seeing the code above (which I originally grabbed from Nick Harrison and modified). He was nice enough to post it for me to take a look at so I thought I’d share it here. He has methods called DataReaderToObjectList() and DataReaderToObject() that use similar reflection techniques. I like his approach since he handles creating single objects and lists plus allows the Dictionary to be passed in (which is nice if you want to cache it due to an object or list being creating multiple times). 

View Rick’s code here.

Reference:  http://weblogs.asp.net/dwahlin/using-entity-framework-code-first-with-stored-procedures-that-have-output-parameters