Calling Stored Procedures from Entity Framework 6 Code First

Just a quick solution to something I was banging my head against for a while.

If you use the EDMX/Database First way of creating your DbContext, you can import Stored Procedures to your context. The result is something like this:

public virtual ObjectResult<Customer_LoadStatus_Result> Customer_LoadStatus(Nullable<int> customerId)
{
    var customerIdParameter = customerId.HasValue ?
        new ObjectParameter("CustomerId", customerId) :
        new ObjectParameter("CustomerId", typeof(int));
 
    return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction<Customer_LoadStatus_Result>("Customer_LoadStatus", customerIdParameter);
}

If you try to copy this method to your Code First context, you're likely to get an error saying something like, "The FunctionImport 'Customer_LoadStatus' could not be found in the container 'CustomerDbContext'." There's probably a way to register it in the ModelBuilder, but 15min of web searches came up with nothing (maybe my Google-Fu is weak?).

Long story short, it seems you can't just copy this to your context if you're using Code First. Instead, do this:

public virtual Customer_LoadStatus_Result Customer_LoadStatus(int? customerId)
{
    var customerIdParameter = customerId.HasValue ?
        new SqlParameter("CustomerId", customerId) :
        new SqlParameter("CustomerId", typeof(int));

    return this.Database.SqlQuery<Customer_LoadStatus_Result>("Customer_LoadStatus @customerId",
        customerIdParameter).SingleOrDefault();
}

As a bonus, in my example I'm just returning the object I expect rather than the wrapped one you'd get from the generated code.

Menu