What is DbContext.Database and how do I use it? Is it possible to use SqlQueryTElement>(sql, params) with a stored procedure? CTP5 EF Code First

Problem

I have a stored process with three parameters, and I’ve been attempting to return the results using the following:

context.Database.SqlQuery<myEntityType>("mySpName", param1, param2, param3);

I tried using SqlParameter objects as the params at first, but this didn’t work and resulted in a SqlException with the following message:

So, how do you utilize this method with a saved procedure that requires parameters?

Thanks.

Asked by electricsheep

Solution #1

The SqlParameter objects should be supplied as follows:

context.Database.SqlQuery<myEntityType>(
    "mySpName @param1, @param2, @param3",
    new SqlParameter("param1", param1),
    new SqlParameter("param2", param2),
    new SqlParameter("param3", param3)
);

Answered by Devart

Solution #2

You may also use the “sql” option to specify a format:

context.Database.SqlQuery<MyEntityType>("mySpName @param1 = {0}", param1)

Answered by Dan Mork

Solution #3

This is (only) a solution for SQL Server 2005.

You guys are lifesavers, but you need to add EXEC to the mix, as @Dan Mork said. What was tripping me up was the following:

:

context.Database.SqlQuery<EntityType>(
    "EXEC ProcName @param1, @param2", 
    new SqlParameter("param1", param1), 
    new SqlParameter("param2", param2)
);

Answered by Tom Halladay

Solution #4

return context.Database.SqlQuery<myEntityType>("mySpName {0}, {1}, {2}",
new object[] { param1, param2, param3 });

//Or

using(var context = new MyDataContext())
{
return context.Database.SqlQuery<myEntityType>("mySpName {0}, {1}, {2}",
new object[] { param1, param2, param3 }).ToList();
}

//Or

using(var context = new MyDataContext())
{
object[] parameters =  { param1, param2, param3 };

return context.Database.SqlQuery<myEntityType>("mySpName {0}, {1}, {2}",
parameters).ToList();
}

//Or

using(var context = new MyDataContext())
{  
return context.Database.SqlQuery<myEntityType>("mySpName {0}, {1}, {2}",
param1, param2, param3).ToList();
}

Answered by Thulasiram

Solution #5

Because they rely on the sequence of the SP’s parameters, most solutions are brittle. It’s preferable to name the Stored Proc’s parameters and provide them parameterized values.

To avoid having to worry about the order of parameters when calling your SP, utilize Named params.

With ExecuteStoreQuery and ExecuteStoreCommand, you can use SQL Server named arguments.

Describes the most effective strategy. Here, Dan Mork’s explanation is superior.

E.g.:

var cmdText = "[DoStuff] @Name = @name_param, @Age = @age_param";
var sqlParams = new[]{
   new SqlParameter("name_param", "Josh"),
   new SqlParameter("age_param", 45)
};

context.Database.SqlQuery<myEntityType>(cmdText, sqlParams)

Answered by Don Cheadle

Post is based on https://stackoverflow.com/questions/4873607/how-to-use-dbcontext-database-sqlquerytelementsql-params-with-stored-proced