Chuck Conway

Chuck Conway

Building Inspiring Software

Menu
  • Home
  • Projects
  • Notes
  • About
Menu

Calling Stored Procedures with Code First

Posted on October 16, 2014 by Chuck Conway

One of the weaknesses of Entity Framework 6 Code First is the lack of support for natively calling database constructs (views, stored procedures… etc). For those who have not heard of or used Code-First in Entity Framework (EF), Code-First is simply a Fluent mapping API. The idea is to create all your database mappings in code (i.e. C#) and the framework then creates and track the changes in the database schema.

In traditional Entity Framework to call a stored procedure you’d map it in your EDMX file. This is a multi-step process. Once the process is completed a method is created, which hangs off the DataContext.

I sought to making a calling stored procedure easier. At the heart of a stored procedure you have a procedure name, N number of parameters and a results set. I’ve written a small extension method that takes a procedure name, parameters and a return type. It just works. No mapping the procedure and it’s parameters.

public static List<TReturn> CallStoredProcedure<TParameters, TReturn>(this DataContext context, string storedProcedure, TParameters parameters) where TParameters : class where TReturn : class, new()
{
IDictionary<string,object> procedureParameters = new Dictionary<string, object>();
PropertyInfo[] properties = parameters.GetType().GetProperties();

var ps = new List<object>();

foreach (var property in properties)
{
object value = property.GetValue(parameters);
string name = property.Name;

procedureParameters.Add(name, value);

ps.Add(new SqlParameter(name, value));
}

var keys = procedureParameters.Select(p => string.Format("@{0}", p.Key)).ToList();
var parms = string.Join(", ", keys.ToArray());

return context.Database.SqlQuery<TReturn>(storedProcedure + " " + parms, ps.ToArray()).ToList();
}

Usage

var context = new DataContext();

List<User> users = context.CallStoredProcedure<object,User>("User_GetUserById", new{userId = 3});

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

    Archives

    • March 2022
    • November 2021
    • October 2021
    • May 2021
    • April 2021
    • March 2021
    • December 2020
    • November 2020
    • October 2020
    • September 2020
    • August 2020
    • July 2020
    • November 2019
    • October 2019
    • September 2019
    • August 2019
    • July 2019
    • June 2019
    • June 2018
    • October 2017
    • December 2015
    • November 2015
    • August 2015
    • May 2015
    • April 2015
    • March 2015
    • February 2015
    • January 2015
    • November 2014
    • October 2014
    • March 2014
    • February 2014
    • December 2013
    • March 2013
    • October 2012
    • August 2012
    • May 2012
    • January 2012
    • December 2011
    • June 2011
    • May 2011
    • December 2010
    • November 2010
    • October 2010

    Categories

    • Architecture
    • Article
    • Code
    • Conceptual
    • Design
    • General
    • Influence
    • Notes
    • Process
    • Satire
    ©2023 Chuck Conway