如何使用Entity Framework 6使用IN语句执行原始SQL [英] How to execute raw sql with IN statement using Entity Framework 6

问题描述

我有一个包含一些ID的列表，我想选择ID在该列表中的记录.到目前为止，我有

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

但是这不起作用，它给了我 MySql.Data.MySqlClient.MySqlException

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

您的SQL语法有错误；检查与您的MySQL服务器版本相对应的手册以获取正确的语法，以在'_binary'System.Collections.Generic.List`1 [System.Int64]'附近使用'在第1行

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

请不要给我一个LINQ解决方案.预先感谢.

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

推荐答案

我认为您不能将数组传递给sql，这在SqlServer上有效，您可能需要将MySql更改为新的MySQLParameter

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

此外，您还可以将LINQ用于更复杂的查询以及具有AS和JOIN和UNION的查询.

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

这篇关于如何使用Entity Framework 6使用IN语句执行原始SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！