What goes in the params parameter of the .SqlQuery() method in Entity Framework?

c# entity-framework sql

Question

The method takes a string for the query, and an array of Object [] for the parameters, presumably to avoid SQL Injection.

However nowhere on earth is it documented what you should put into the object array.

There is another question on SO that asks this exact same thing, but the accepted answer doesn't work: When using DbSet<T>.SqlQuery(), how to use named parameters?

I've tried all forms of parameter replacement I can think of and all of them throw an exception. Any ideas?

Would that it were as simple as:

SqlQuery("SELECT * FROM @table", "Users")

Edit: Here are some things I've tried (Exception is an SqlException):

    var result = context.Users.SqlQuery<T>("SELECT * FROM @p0 WHERE @p1 = '@p2'", 
new SqlParameter("p0", tableName), 
new SqlParameter("p1", propertyName), 
new SqlParameter("p2", searchQuery));

This gives Must declare the table variable "@p0".

var result = context.Users.SqlQuery<T>("SELECT * FROM {0} WHERE {1} = '{2}'", tableName, propertyName, searchQuery);

This gives Must declare the table variable "@p0".

1
10
5/23/2017 11:53:11 AM

Accepted Answer

There is nothing wrong with your query syntax or how do you created and passed in the SqlParameter objects.

Your problem is that you try to use a variable as the table name, what you cannot do (see: Must declare the table variable @table), so you need to manually "template" the table name in your query:

Something like.

var result = context.Users.SqlQuery<T>(
"SELECT * FROM " + tableName + " WHERE @p0 = '@p1'", 
   new SqlParameter("p0", propertyName), 
   new SqlParameter("p1", searchQuery));
18
5/23/2017 12:08:57 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow