实体框架 - 排除值列表

Question

有没有一种方法可以在通过实体框架查询数据库时排除对象属性的值列表？

我试图聪明地提取这个数字：

List<String> StringList = new List<String>();
StringList.Add("ya_mama");
StringList.Add("has");
StringList.Add("fleas");

servicesEntities context = new servicesEntities();
var NoFleasQuery = (from x in context.person
                   where !StringList.Any(y => y.CompareTo(x.the_string_I_dont_want_it_to_be) == 0)  // <--- the part where I thought I was slick
                   select x);

代码编译通过，但运行时出现以下错误：

无法创建“闭包类型”常量值。在此上下文中只支持原始类型（如 Int32、String 和 Guid）。

“闭包类型”？？我的闭包呢！Entity Framework……你伤了我的心。

- Chris Dutrow

Answer 1

那方面的问题？

    var NoFleasQuery = (from x in context.person
                        where !StringList.Any(y => x.the_string_I_dont_want_it_to_be == y)
                        select x);

我觉得它能正常工作。但是生成的SQL语句相当糟糕：

SELECT 
1 AS [C1], 
[Extent1].[Id] AS [Id], 
[Extent1].[Name] AS [Name], 
[Extent1].[Address_Id] AS [Address_Id]
FROM [dbo].[Person] AS [Extent1]
WHERE  NOT EXISTS (SELECT 
    1 AS [C1]
    FROM  (SELECT 
        N'John Doe' AS [C1]
        FROM  ( SELECT 1 AS X ) AS [SingleRowTable1]
    UNION ALL
        SELECT 
        N'Jack Black' AS [C1]
        FROM  ( SELECT 1 AS X ) AS [SingleRowTable2]) AS [UnionAll1]
    WHERE [Extent1].[Name] = [UnionAll1].[C1]
)

它构建了一个子查询，该子查询是其他子查询的联合，而不是使用“NOT IN”…可能效率不高！

- Thomas Levesque

Answer 2

试试这个：

where !StringList.Contains(x.the_string_I_dont_want_it_to_be)

- Konamiman

---

它给了我这个数字：LINQ to Entities 不认识 'Boolean Contains(System.String)' 方法，而且这个方法无法被转换成存储表达式。 - Chris Dutrow