How to execute raw sql with IN statement using Entity Framework 6

Question

I have a list that contains some ID's, and I'd want to pick the records whose ID is in that list. So far I have

var ids = new List<long> {1, 2, 3};  // Actually the list is not hard coded
using(var context = new MyContext())
{
   context.MyModel.Database.SqlQuery("SELECT * FROM dbo.MyModel WHERE Id IN @p0", ids).ToList();
}

But this doesn't work, and it gives me an MySql.Data.MySqlClient.MySqlException

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '_binary 'System.Collections.Generic.List`1[System.Int64]' ' at line 1

Please don't give me a LINQ solution. Thanks in advance.

I think it should be `context.MyModel.Database.SqlQuery` I'm on mobile now, will check and let you know when I can. — uTeisT, Aug 26 '16 at 13:07
I could only get it to work by adding the lambda expression, `.Where(x => ids.Contains(x.Id))` to the `DbSqlQuery`. — Biscuits, Aug 26 '16 at 13:20
Actually I have to use union select and column name alias (the AS clause), so LINQ might not be suitable for me. — Aetherus, Aug 26 '16 at 13:22
Here's a silly way of doing something like that http://stackoverflow.com/a/337817/2707705 — Biscuits, Aug 26 '16 at 13:41

Filip Cordas · Answer 1 · 2016-08-26T15:08:28.220

I don't think you can pass array to sql, this works on SqlServer You might need to change to new MySQLParameter for MySql

var inParams = ids.Select((id, i) => new SqlParameter("p"+i, id));

var query = "select * from Employees where Id in (" + string.Join(",", inParams.Select(p => "@" + p.ParameterName)) + ")";

var data = db.Database.SqlQuery<Employee>(query, inParams.Cast<object>().ToArray()).ToList();

Also you can use LINQ for more complex queries as well ones that have AS and JOIN and UNION

How to execute raw sql with IN statement using Entity Framework 6

1 Answers1

Linked