Entity Framework (Code First): the easy way to run stored procedures

Hello,

an ordinal way to execute stored procedure in EF CF is something like this:

public Product GetProduct(int id)
{
    Product product = null;

    using (var context = new NorthwindData())
    {
        string query = "Product_GetByID @productId";
        SqlParameter productId = new SqlParameter("@productId", id);

        product = context.Database.SqlQuery<Product>(query, productId).FirstOrDefault();
    }

    return product;
}

If you have more parameters for SP, you should specify all of them. Sometimes it's boring... So, I wrote several simple extensions to Database class to simplify this task.

See code below how GetProduct method will look like when using extensions:

public Product GetProduct(int id)
{
    Product product = null;

    using (var context = new NorthwindData())
    {
        product = context.Database.SqlQuerySmart<Product>("Product_GetByID", new
        {
            productId = id
        }).FirstOrDefault();
    }

    return product;
}

As you can see, you just create anonymous type with fields that has names exactly as parameters of SP and extension takes care about correct SQL code and other things. But, extension works only in simple cases, when there are no OUT parameters in SP.

Code of extension provided below, enjoy!

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data.Entity;
using System.Data.SqlClient;
using System.Reflection;

namespace HennadiyKurabko.Data.EF
{
    public static class Extensions
    {
        public static int ExecuteSqlCommandSmart(this Database self, string storedProcedure, object parameters = null)
        {
            if (self == null)
                throw new ArgumentNullException("self");
            if (string.IsNullOrEmpty(storedProcedure))
                throw new ArgumentException("storedProcedure");

            var arguments = PrepareArguments(storedProcedure, parameters);
            return self.ExecuteSqlCommand(arguments.Item1, arguments.Item2);
        }

        public static IEnumerable<TElement> SqlQuerySmart<TElement>(this Database self, string storedProcedure, object parameters = null)
        {
            if (self == null)
                throw new ArgumentNullException("self");
            if (string.IsNullOrEmpty(storedProcedure))
                throw new ArgumentException("storedProcedure");

            var arguments = PrepareArguments(storedProcedure, parameters);
            return self.SqlQuery<TElement>(arguments.Item1, arguments.Item2);
        }

        public static IEnumerable SqlQuerySmart(this Database self, Type elementType, string storedProcedure, object parameters = null)
        {
            if (self == null)
                throw new ArgumentNullException("self");
            if (elementType == null)
                throw new ArgumentNullException("elementType");
            if (string.IsNullOrEmpty(storedProcedure))
                throw new ArgumentException("storedProcedure");

            var arguments = PrepareArguments(storedProcedure, parameters);
            return self.SqlQuery(elementType, arguments.Item1, arguments.Item2);
        }

        private static Tuple<string, object[]> PrepareArguments(string storedProcedure, object parameters)
        {
            var parameterNames = new List<string>();
            var parameterParameters = new List<object>();

            if (parameters != null)
            {
                foreach (PropertyInfo propertyInfo in parameters.GetType().GetProperties())
                {
                    string name = "@" + propertyInfo.Name;
                    object value = propertyInfo.GetValue(parameters, null);

                    parameterNames.Add(name);
                    parameterParameters.Add(new SqlParameter(name, value ?? DBNull.Value));
                }
            }

            if (parameterNames.Count > 0)
                storedProcedure += " " + string.Join(", ", parameterNames);

            return new Tuple<string, object[]>(storedProcedure, parameterParameters.ToArray());
        }
    }
}