CODEWITHSUNDEEP
c#sqlado.netlinq-to-entitiesentity-framework-ctp5
electricsheep
electricsheep

Reputation: 5224

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

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.

Upvotes: 280

Views: 390676

Answers (10)

Thulasiram
Thulasiram

Reputation: 8552

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

Upvotes: 18

Tom Stickel
Tom Stickel

Reputation: 20391

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.

Upvotes: 1

Ziggler
Ziggler

Reputation: 3500

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

Upvotes: 0

Don Cheadle
Don Cheadle

Reputation: 5576

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)

Upvotes: 11

Hossein Hajizadeh
Hossein Hajizadeh

Reputation: 1485

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

Upvotes: 7

emanuel.virca
emanuel.virca

Reputation: 598

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

Upvotes: 1

Tom Halladay
Tom Halladay

Reputation: 5751

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

Upvotes: 72

Devart
Devart

Reputation: 121902

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

Upvotes: 427

Malcolm O&#39;Hare
Malcolm O&#39;Hare

Reputation: 4999

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.

Upvotes: 3

Dan Mork
Dan Mork

Reputation: 1818

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

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

Upvotes: 137

Related Questions