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

Question

I have a stored procedure that has three parameters and I've been trying to use the following to return the results:

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

At first I tried using SqlParameter objects as the params but this didn't work and threw a SqlException with the following message:

Procedure or function 'mySpName' expects parameter '@param1', which was not supplied.

So my question is how you can use this method with a stored procedure that expects parameters?

Thanks.

Answer 1

You should supply the SqlParameter instances in the following way:

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

Answer 2

Most answers are brittle because they rely on the order of the SP's parameters. Better to name the Stored Proc's params and give parameterized values to those.

In order to use Named params when calling your SP, without worrying about the order of parameters

Using SQL Server named parameters with ExecuteStoreQuery and ExecuteStoreCommand

Describes the best approach. Better than Dan Mork's answer here.

• Doesn't rely on concatenating strings, and doesn't rely on the order of parameters defined in the SP.

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)

Answer 3

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();
}

Answer 4

@Tom Halladay's answer is correct with the mention that you shopuld also check for null values and send DbNullable if params are null as you would get an exception like

The parameterized query '...' expects the parameter '@parameterName', which was not supplied.

Something like this helped me

public static object GetDBNullOrValue<T>(this T val)
{
    bool isDbNull = true;
    Type t = typeof(T);

    if (Nullable.GetUnderlyingType(t) != null)
        isDbNull = EqualityComparer<T>.Default.Equals(default(T), val);
    else if (t.IsValueType)
        isDbNull = false;
    else
        isDbNull = val == null;

    return isDbNull ? DBNull.Value : (object) val;
}

(credit for the method goes to https://stackoverflow.com/users/284240/tim-schmelter)

Then use it like:

new SqlParameter("@parameterName", parameter.GetValueOrDbNull())

or another solution, more simple, but not generic would be:

new SqlParameter("@parameterName", parameter??(object)DBNull.Value)

Answer 5

db.Database.SqlQuery<myEntityType>("exec GetNewSeqOfFoodServing @p0,@p1,@p2 ", foods_WEIGHT.NDB_No, HLP.CuntryID, HLP.ClientID).Single()

or

db.Database.SqlQuery<myEntityType>(
    "exec GetNewSeqOfFoodServing @param1, @param2", 
    new SqlParameter("param1", param1), 
    new SqlParameter("param2", param2)
);

or

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

db.Database.SqlQuery<myEntityType>(cmdText, @params)

or

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

Answer 6

Also, you can use the "sql" parameter as a format specifier:

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

Answer 7

I had the same error message when I was working with calling a stored procedure that takes two input parameters and returns 3 values using SELECT statement and I solved the issue like below in EF Code First Approach

 SqlParameter @TableName = new SqlParameter()
        {
            ParameterName = "@TableName",
            DbType = DbType.String,
            Value = "Trans"
        };

SqlParameter @FieldName = new SqlParameter()
        {
            ParameterName = "@FieldName",
            DbType = DbType.String,
            Value = "HLTransNbr"
        };


object[] parameters = new object[] { @TableName, @FieldName };

List<Sample> x = this.Database.SqlQuery<Sample>("EXEC usp_NextNumberBOGetMulti @TableName, @FieldName", parameters).ToList();


public class Sample
{
    public string TableName { get; set; }
    public string FieldName { get; set; }
    public int NextNum { get; set; }
}

UPDATE: It looks like with SQL SERVER 2005 missing EXEC keyword is creating problem. So to allow it to work with all SQL SERVER versions I updated my answer and added EXEC in below line

 List<Sample> x = this.Database.SqlQuery<Sample>(" EXEC usp_NextNumberBOGetMulti @TableName, @FieldName", param).ToList();

Answer 8

I did mine with EF 6.x like this:

using(var db = new ProFormDbContext())
            {
                var Action = 1; 
                var xNTID = "A239333";

                var userPlan = db.Database.SqlQuery<UserPlan>(
                "AD.usp_UserPlanInfo @Action, @NTID", //, @HPID",
                new SqlParameter("Action", Action),
                new SqlParameter("NTID", xNTID)).ToList();


            }

Don't double up on sqlparameter some people get burned doing this to their variable

var Action = new SqlParameter("@Action", 1);  // Don't do this, as it is set below already.

Answer 9

This solution is (only) for SQL Server 2005

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

• 'EXEC ' before the Proc Name
• Commas in between Params
• Chopping off '@' on the Param Definitions (not sure that bit is required though).

:

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

Answer 10

I use this method:

var results = this.Database.SqlQuery<yourEntity>("EXEC [ent].[GetNextExportJob] {0}", ProcessorID);

I like it because I just drop in Guids and Datetimes and SqlQuery performs all the formatting for me.