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

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".

What exception are you getting from your example? Also, I didn't even know you could use a parameter for the name of the table in a query... — Tim, Jul 25 '13 at 15:33
SqlQuery executes direct SQL against the database. I get various errors depending on the approach I try. I'll put some in the question. — NibblyPig, Jul 25 '13 at 15:36
Easy reference: http://msdn.microsoft.com/en-us/library/system.data.entity.dbset.sqlquery%28v=vs.103%29.aspx — Charlie Brown, Jul 25 '13 at 15:38
Indeed, MSDN is very helpful, it says `The parameters to apply to the SQL query string.`. Thanks, Microsoft. — NibblyPig, Jul 25 '13 at 15:38
You cannot use a variable as a table name: http://stackoverflow.com/questions/14003241/must-declare-the-table-variable-table. You need something like: `var result = context.Users.SqlQuery("SELECT * FROM " + tableName + " WHERE @p0 = '@p1'", new SqlParameter("p0", propertyName), new SqlParameter("p1", searchQuery));` — nemesv, Jul 25 '13 at 15:47
So when I said "I didn't even know you could use a parameter for the name of the table" I guess I shouldn't have been embarrassed to not know that... — Tim, Jul 25 '13 at 15:54

score 19 · Accepted Answer · edited May 23 '17 at 12:08

19

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

edited May 23 '17 at 12:08

Community

1
1

answered Jul 25 '13 at 15:52

nemesv

138,284
16
416
359

Problem solved, THANK YOU. I was ready to hulk out and smash things. I hope this saves some poor sap in the future. – NibblyPig Jul 25 '13 at 15:53
2

@SLC Make sure to sanitize table name if it's coming from user input to avoid sql injection. For example see here: http://stackoverflow.com/q/13887202/284111 – Andrew Savinykh Feb 17 '14 at 19:05

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

1 Answers1