How to use DbContext.Database.SqlQuery(s

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

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 2

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.

Answer 3

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 4

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 5

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

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

Answer 6

@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)