Lambda expression "IN" operator Exists?

Question

I'm looking for to build the Lambda expression like the below

IQueryable<Object> queryEntity = 
                _db.Projects.Where(Project=>Project.Id.IN(1,2,3,4));

I don't find any IN operator in Lambda expression.

Anybody have suggestions?

Solution 1

Use IEnumerable.Contains for this.

var idList = new[] { 1, 2, 3, 4 };
IQueryable<Object> queryEntity = 
                _db.Projects.Where(Project => idList.Contains(Project.Id));

You could construct the idList inline of course.

Solution 2

There is no in operator, but there is a contains. Just invert your logic:

IQueryable<Object> queryEntity = _db.Projects.Where(Project=>(new []{1,2,3,4}).Contains(Project.Id));

Comments

[Tilak]

and how is it different from msdn.microsoft.com/en-us/library/bb352880.aspx

[Maxim Zabolotskikh]

But keep in mind that if this goes to DB with really big idList (say 8000 IDs) it may fail. I think this is for exceeding batch size or sth - at least I had the case.

[Anders Arpi]

Yes - all the problems that can arise from constructing weird LINQ queries that get translated into slow SQL calls apply. But that goes for anything you do with IQueryable really. :)

[Anders Arpi]

This isn't usable in an IQueryable context, since there is no way to translate this to SQL the server would understand.

[Joker_vD]

If idList is really big, then there's a good chance it came as a DB query result, in which case you should instead JOIN ON against that query, right?

[sivaL]

This is the correct answer for my question. Its working also. But Ids list might exceed 50,000 records. Will it be a problem? var Ids = _db.Projects.Where(Project=>Project.Title!="test23rdoct").Se‌​lect(pro => pro.Id); var compIds = _db.Companies.Where(comp => comp.Participants.Any(part => Ids.Contains(part.Project.Id)));

[Anders Arpi]

That should be translated into a pretty big IN clause, but it will only hit your database once. The first query you construct will get incorporated into the second one, once the second one is executed.

[Maxim Zabolotskikh]

Look in SQL Server Profiler what is the resulting query. As Ids is IQueryable, IDs are not fetched from DB until you actually execute sth, let's say compIds.ToList(). Only at that point SQL statement will be constructred. May be EF is clever enough to construct a IN (SELECT ...) but better check this out.

[Anders Arpi]

@MaximZabolotskikh It's absolutely that smart. Only thing I'm curious about is if the navigation property (comp.Participants) will cause some sort of problem for the deferred SQL call. But really, the best way is to test it against the DB and check out the resulting query.

[sivaL]

I agree with both, However I will test. Thanks for your insight discussions. It helped me a lot.