How to use DbContext.Database.SqlQuery`<TElement>`(sql, params) with stored procedure? EF Code First CTP5

sql

sql-exceptions

sql-query

stored-procedures

byAnton Shumikhin·Aug 31, 2024

Let's dive straight in. Here's the quick recipe to call a stored procedure using DbContext.Database.SqlQuery<T> in Entity Framework:

var results = context.Database.SqlQuery<YourType>("EXEC StoredProcedureName @p1, @p2", 
             new SqlParameter("p1", value1), 
             new SqlParameter("p2", value2)).ToList();

Eye on the ball:

The result type maps to YourType.
Replace StoredProcedureName, @p1, @p2 with your stored procedure's name and params.
Use SqlParameter instances to bind values to parameters.

Tip: Laplace would urge you to get your parameters in sync with your stored procedure to ensure predictive programming.

A step further with Complex Types

Just when you thought life was simple, enters complex types. Thankfully, with EF, it's a breeze:

Define a class that captures the result schema of your SP.

public class MyComplexType
{
    // The properties here reflect the structure of the SP result set
}

Just call the stored procedure.

var complexResults = context.Database.SqlQuery<MyComplexType>("EXEC MyComplexStoredProcedure @p1, @p2", new SqlParameter("@p1", value1), new SqlParameter("@p2", value2)).ToList();

Tip: Naming convention at play! Make sure your complex class property names match with your SP returned columns for smoother mapping.

Handling exceptions like a Predictive Programmer

Even the best coders can miss a beat. The force of SqlExceptions is strong with wrong parameters:

try
{
    var results = context.Database.SqlQuery<YourType>("EXEC mySpName @param1, @param2", new SqlParameter("param1", value1), new SqlParameter("param2", value2)).ToList();
    // Ninja code, no '#yolo' here
}
catch (SqlException ex)
{
    // Oopsie-doodle! Handle the SQL error here
}

Best Practices: The Narwhal Bacons at Midnight

When night falls, the legendary Narwhal rises with using block & parameter arrays:

Use a using block with your DbContext for good housekeeping. Trust me, it's the adult thing to do.
Where there's smoke, there's fire. But when there are just too many parameters, simply separate them from the SqlQuery call.

object[] params = {/* Your SqlParameter instances here */}
var results = dbContext.Database.SqlQuery<YourType>("EXEC MyStoredProcedure @p1, @p2", params).ToList();

Clarity is Power

Empower yourself and the team with clean, readable code:

When dealing with complex types, create a dedicated class for those return types. No one likes a surprise party in their code.
Label each SqlParameter for what it represents, in sync with the stored procedure's expected parameters.

The Path Less Taken

For dynamic types, journey down the non-generic version of SqlQuery and serialize the DbDataReader:

// For those living on the edge
var results = context.Database.SqlQuery(typeof(YourType), "EXEC StoredProcedureName", parameters).GetEnumerator();
while (results.MoveNext())
{
    var record = (YourType)results.Current;
    // Time to crunch the data, nom nom!
}