CODEWITHSUNDEEP
c#entity-framework
Chris Dutrow
Chris Dutrow

Reputation: 50412

Entity framework - exclude list of values

Is there a way to exclude a list of values for an object attribute when querying the database through entity framework?

I tried to be slick and pull this number:

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

It compiled, but after I ran it, it gave me this error:

Unable to create a constant value of type 'Closure type'. Only primitive types ('such as Int32, String, and Guid') are supported in this context.

'Closure type'?? How about MY closure!! Entity framework... you broke my heart.

Upvotes: 4

Views: 2945

Answers (2)

Thomas Levesque
Thomas Levesque

Reputation: 292695

What about that ?

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

It works fine for me. However the generated SQL is quite awful :

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

It builds a subquery which is the union of other subqueries, rather than using "NOT IN"... probably not very efficient !

Upvotes: 3

Konamiman
Konamiman

Reputation: 50323

Try this instead:

where !StringList.Contains(x.the_string_I_dont_want_it_to_be)

Upvotes: 0

Related Questions