1

Why does:

IEnumerable<MyEntity> MyFunction(string sql, object [] params)
{
    // EG:
    // "SELECT * FROM MyTable WHERE MyField0 = @0 AND MyField1 = @1"
    // params = { 1, "UK" }

    return Context.Database.SqlQuery<TEntity>(sql, params);
}

...give me the following error?: 

Must declare the scalar varliable "@0"

I know it would work if I did:

var query = Context.Database.SqlQuery<TEntity>(
   "SELECT * FROM MyTable WHERE MyField0 = @0 AND MyField1 = @1", 1, "UK"
);

... but I want to abstract the code and call it as, for example:

var x = MyFunction(
       "SELECT * FROM MyTable WHERE MyField0 = @0 AND MyField1 = @1", 1, "UK"
    );

Ie, my problem is that I can't figure out how to pass in an array of params.

EDIT:

The accepted answer answers the question, but there is another problem in there: you need to parametrize the array, ie, instead of just the values, pass them as an array of named SqlParameters. See:

SqlQuery and parameters

Community
  • 1
  • 1
awrigley
  • 13,481
  • 10
  • 83
  • 129
  • is there any problem in you do this? string.Format("SELECT * FROM MyTable WHERE MyField0 = {0} AND MyField1 = {1}", 1, "UK"); – Blau Sep 18 '11 at 00:08
  • @Blau that's a bad idea unless you want SQL Injection to happen – bluee Jul 07 '14 at 05:22

1 Answers1

1

You need to include the params keyword to the "parameters" parameter:

IEnumerable<MyEntity> MyFunction(string sql, params object[] parameters)
{
    return Context.Database.SqlQuery<TEntity>(sql, parameters);   
}
Mark Cidade
  • 98,437
  • 31
  • 224
  • 236
  • I have given you the tick, because it answers the question. However, the main problem are the fact that instead of passing the raw values in the params array, you need to parametrize them. See accepted answer:http://stackoverflow.com/questions/4873607/how-to-use-dbcontext-database-sqlquerytelementsql-params-with-stored-procedu – awrigley Sep 18 '11 at 12:53