Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

IQueryable for entities .Where( property is in local array)

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?

like image 943
Alex Avatar asked Sep 04 '13 15:09

Alex


People also ask

What inherits from IQueryable?

The IQueryable interface inherits the IEnumerable interface so that if it represents a query, the results of that query can be enumerated. Enumeration causes the expression tree associated with an IQueryable object to be executed.

Is IQueryable faster than list?

Here is a test that i have setup this evening. It was made to prove something different, but the outcome was not quite as i expected. I'm running a test with 10000 random queries on an IQueryable and while testing i found out that if i do the same on a List, my test is 20 times faster.

Should I use IEnumerable or IQueryable C#?

IEnumerable: IEnumerable is best suitable for working with in-memory collection (or local queries). IEnumerable doesn't move between items, it is forward only collection. IQueryable: IQueryable best suits for remote data source, like a database or web service (or remote queries).

Is IQueryable a collection?

Both IEnumerable and IQueryable are forward collection. Querying data from a database, IEnumerable execute a select query on the server side, load data in-memory on a client-side and then filter data.


Video Answer


1 Answers

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)
like image 166
Raphaël Althaus Avatar answered Oct 26 '22 10:10

Raphaël Althaus