IQueryable for entities .Where( property is in loc

问题:

So I know that iQueryables are translated into SQL statements and thus cannot handle all possible methods that you might put into a where clause.

But this is what I'm trying to do:

int[] alreadySelectedIds = ...
var subjects = Entities.NewInstance.Subjects.Where(x => Array.IndexOf(alreadySelectedIds, x.Id) == -1).ToList();

And reading post like these below, I'm comforted that EF5 should be able to translate this.
Getting Entities whose keys match list(or array) of ids
Using LINQ To Query Int Ids From An Array

However, I'm getting this error:

LINQ to Entities does not recognize the method 'Int32 IndexOf[Int32](Int32[], Int32)' method, and this method cannot be translated into a store expression.

And googling this error does not give me much help.

I have also tried

var newSubjects = Entities.NewInstance.Subjects.Where(x => alreadySelectedIds.Contains(x.Id)).ToList();

Unable to create a null constant value of type 'System.Int32[]'. Only entity types, enumeration types or primitive types are supported in this context.

and

List<int> alreadySelectedIds = ...

Unable to create a null constant value of type 'System.Collections.Generic.List`1'. Only entity types, enumeration types or primitive types are supported in this context.

I'm stuck and my brain is getting mushy beyond the possibility for any type of graceful recovery. Can anyone kindly save me?

回答1:

Entities.NewInstance.Subjects.Where(x => alreadySelectedIds.Contains(x.Id)).ToList();

should work, if alreadySelectedIs is not null

you can do a null check inside or before your query :

Entities.NewInstance.Subjects.Where(x => alreadySelectedIds == null 
                                         ? true // or false 
                                         : alreadySelectedIds.Contains(x.Id)
                                    ).ToList();

(which can be rewritten, depending if you want all or nothing if alreadySelectedIds is null)

//return all if null
x => alreadySelectedIds == null || alreadySelectedIds.Contains(x.Id)

or

//return nothing if null
x => alreadySelectedIds != null  && alrreadySelectedIds.Contains(x.Id)