Entity Framework Core - Mapping Stored Procedures, Fluently

Ever since the beginning of last year, I've been following the development of .NET Core and more specifically Entity Framework Core. I've noticed that there's no way to map a Stored Procedure to a basic POCO class, and I've decided to make a way for this to happen.

I, normally, wouldn't have cared about this, but I had a need to implement something for work purposes. I'm certain many companies deal with creating Stored Procedures that pull data from multiple databases and return it in a manner that doesn't map to any existing table.

With that said, I've decided to make extension methods that build a Stored Procedure result, fluently.  

First step was to create a method that would kick off the beginning of the chain and I named it, WithSqlParam. It was made to be execute from a DbContext and would generate a simple DbCommand in the end.

       public static DbCommand LoadStoredProc(this DbContext context, string storedProcName)
        {
            var cmd = context.Database.GetDbConnection().CreateCommand();
            cmd.CommandText = storedProcName;
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            return cmd;
        }    

With the notion that Stored Procedures would have parameters,  I made a chain method that would add a parameter to a DbCommand, named WithSqlParam. It's made to be a simple name and value that should be handled properly. 

        public static DbCommand WithSqlParam(this DbCommand cmd, string paramName, object paramValue)
        {
            if (string.IsNullOrEmpty(cmd.CommandText))
                throw new InvalidOperationException("Call LoadStoredProc before using this method");

            var param = cmd.CreateParameter();
            param.ParameterName = paramName;
            param.Value = paramValue;
            cmd.Parameters.Add(param);
            return cmd;
        }

This code just creates parameters and adds it to the Parameters property of the DbCommand object. When this property is executed, (even if it's not) there's enough information to execute the stored procedure and map the output to a POCO class.

I created a method named MapToList, with this in mind. The class that the Stored Procedure is mapping to should be the master in this situation because we never know whether the developer (s) are up to date with what the data that the database represents. 

        private static IList<T> MapToList<T>(this DbDataReader dr)
        {
            var objList = new List<T>();
            var props = typeof(T).GetRuntimeProperties();

            var colMapping = dr.GetColumnSchema()
                .Where(x => props.Any(y => y.Name.ToLower() == x.ColumnName.ToLower()))
                .ToDictionary(key => key.ColumnName.ToLower());

            if (dr.HasRows)
            {
                while (dr.Read())
                {
                    T obj = Activator.CreateInstance<T>();
                    foreach (var prop in props)
                    {
                        var val = dr.GetValue(colMapping[prop.Name.ToLower()].ColumnOrdinal.Value);
                        prop.SetValue(obj, val == DBNull.Value ? null : val);
                    }
                    objList.Add(obj);
                }
            }
            return objList;
        }

This method gets executed from a DbCommand and calls the ExecuteReader method which in turn makes a instance of the DbDataReader class. This class allows us to read each row as it comes in and do whatever we want to with the data. Luckily, the DbDataReader class has a GetColumSchema method, which allowed me to grab information about all of the columns that would be returned through the Stored Procedure. I used this information to map columns to the properties of the type that we're trying to map to. If the type doesn't contain a property that maps to a columns in the stored procedure, then we disregard it. 

This method then simply reads through all rows and maps the values of each column to the property of the object, adds it to a list of that said object and returns it.

At this point, we're ready for primetime. The ExecuteStoredProc<T> method executes the stored procedure and maps it to the a List whose type that's passed in as T. 

      public static IList<T> ExecuteStoredProc<T>(this DbCommand command)
        {
            using (command)
            {
                if (command.Connection.State == System.Data.ConnectionState.Closed)
                    command.Connection.Open();
                try
                {
                    using (var reader = command.ExecuteReader())
                    {
                        return reader.MapToList<T>();
                    }
                }
                finally
                {
                    command.Connection.Close();
                }
            }
        }

That's it. 

Here's an example of usage:

IList<SomeType> someTypeList = new List<SomeType>();
using(var context = new SomeDbContext())
{
    someTypeList = context.LoadStoredProc("dbo.SomeProcName")
               .WithSqlParam("someparamname",someparamvalue)
               .WithSqlParam("anotherparamname",anotherparamvalue).
               .ExecureStoredProc<SomeType>();
}

I have a lot more to update on this, such as being able to map multiple result sets to List<T> within a master object, but I felt like getting this out there for others who may be facing this.

The current source is located at : https://github.com/snickler/EFCore-FluentStoredProcedure

Feel free to improve on it anyway you feel like.