ObjectContext.ExecuteStoreCommand doesn't recognise parameter

Question

ObjectContext.ExecuteStoreCommand doesn't recognise parameter

c# entity-framework sql-server

Question

What am I doing wrong here?

...
using (var ctx = ObjectContextManager<MyDataContext>.GetManager("MyDataContext"))
{
    var idsToUpdate = "2,3";

    var parameters = new[]
    {
         new SqlParameter("DesiredEndDate", SqlDbType.DateTime).Value = newUpperLimit,
         new SqlParameter("TasksToUpdate", SqlDbType.NVarChar).Value = idsToUpdate
    };

    ctx.ObjectContext.ExecuteStoreCommand("UPDATE dbo.Tasks SET DesiredEndDate = @DesiredEndDate WHERE Id IN (SELECT Id FROM dbo.fn_Split(@TasksToUpdate, N','))", parameters);

    ctx.ObjectContext.SaveChanges();
}
...

I get the error

Must declare the scalar variable "@DesiredEndDate".

Must declare the scalar variable "@TasksToUpdate".

But I cannot see what is wrong with my code :/

Kev

Popular Answer

I still don't see why the @ParamName syntax doesn't work. But there is an alternative to this, which does work:

 ctx.ObjectContext
    .ExecuteStoreCommand(
          "UPDATE dbo.Tasks SET DesiredEndDate = {0} WHERE Id IN (SELECT Id FROM dbo.fn_Split({1}, N','))",
          newUpperLimit, 
          idsToUpdate);

This has the added benefit that I don't need to create SqlParameters.

Kev

View more on Stack Overflow

qujck · Accepted Answer · 8/8/2013 1:23:55 PM

The problem is that your code:

var parameters = new[]
{
     new SqlParameter("DesiredEndDate", SqlDbType.DateTime).Value = newUpperLimit,
     new SqlParameter("TasksToUpdate", SqlDbType.NVarChar).Value = idsToUpdate
};

is not creating an array of SqlParameters - it's actually create an array of the 2 Value properties. You can confirm this by changing your code to this (which will not compile):

var parameters = new SqlParameter[]
{
     new SqlParameter("DesiredEndDate", SqlDbType.DateTime).Value = newUpperLimit,
     new SqlParameter("TasksToUpdate", SqlDbType.NVarChar).Value = idsToUpdate
};

and this (which will compile)

var parameters = new SqlParameter[]
{
     new SqlParameter("DesiredEndDate", SqlDbType.DateTime),
     new SqlParameter("TasksToUpdate", SqlDbType.NVarChar)
};

parameters[0].Value = newUpperLimit;
parameters[1].Value = idsToUpdate;

You can also do this:

var param1 = new SqlParameter("DesiredEndDate", SqlDbType.DateTime);
param1.Value = newUpperLimit;
var param2 = new SqlParameter("TasksToUpdate", SqlDbType.NVarChar);
param2.Value = idsToUpdate;

ctx.ObjectContext.ExecuteStoreCommand("...", param1, param2);

ObjectContext.ExecuteStoreCommand doesn't recognise parameter

Question

Fastest Entity Framework Extensions

Accepted Answer

Popular Answer

Performance

Expression Evaluator

Related