Use IN operator in LINQ

Question

I have List<Candidate> Candidates, List<Seat> Seats

The Model defined as shown below

 public class Seat
    {

         public string CollegeId { get; set; }
         public bool isFilled { get; set; }
         public string SeatType { get; set; }
         public string RollNumber { get; set; }
    }
     public class Candidate
     {
         public string RollNumber { get; set; }
         public bool isAllotted { get; set; }
         public string Quota { get; set; }
         public int CandidateRank { get; set; }
         public List<OptionPriority> SeatingPriorities { get; set; }

     }
     public class OptionPriority
     {
         public string CollegeId { get; set; }
         public int PriorityRank { get; set; }
     }

I need to filter List<Seat> from List<Seat> Seats where Seats.CollegeId IN List of CollegeID in SeatingPriorities.

One more note - [mutable structs are evil](http://stackoverflow.com/questions/441309/why-are-mutable-structs-evil). Consider to use classes for your data — Sergey Berezovskiy, Jul 22 '13 at 09:11

Sergey Berezovskiy · Accepted Answer · 2013-07-22T08:05:44.073

4

// same as EXISTS
Seats.Where(s => SeatingPriorities.Any(sp => sp.CollegeId == s.CollegeId))

Also you can join seatings with seatings priorities:

// same as INNER JOIN
var prioritySeats = from s in Seats
                    join sp in SeatingPriorities
                         on s.CollegeId equals sp.CollegeId
                    select s;

NOTE: Both of queries above will not generate IN clause if you will execute them in LINQ to SQL or LINQ to Entities. IN is generated when you use Contains method of primitive types list:

var ids = SeatingPriorities.Select(sp => sp.CollegeId).ToList();
// same as IN
var prioritySeats = Seats.Where(s => ids.Contains(s.CollegeId));

edited Jul 22 '13 at 08:05

answered Jul 22 '13 at 07:34

Sergey Berezovskiy

232,247
41
429
459

Error 92 'System.Collections.Generic.List' does not contain a definition for 'Where' and the best extension method overload 'System.Linq.Enumerable.Where(System.Collections.Generic.IEnumerable, System.Func)' has some invalid arguments C:\Users\Kiran\Desktop\entrance-kiran\AllotmetBL\FirstAllotmentBL.cs 34 30 AllotmentBL – Subin Jacob Jul 22 '13 at 07:45
@SubinJacob what type of parameters your `Where` method has? It should have overload with parameter of type `Func`. Ah I see, seating priorities is not simple list of id? Hold on – Sergey Berezovskiy Jul 22 '13 at 07:53
The `var ids = SeatingPriorities.Select(sp => sp.CollegeId).ToList();` is needed because Linq doesn't know EF doesn't know about SeatingPriorities. So its happy to pass a List of SeatingPriorities to EF from the Linq Query. EF obviously can't convert a list of SeatingPriorities into SQL. – Aron Jul 22 '13 at 08:31
1

Also of note, is that EF is unable to query cache WHERE IN queries. So you have a bit of a performance hit on the SQL generation here...FYI – Aron Jul 22 '13 at 08:32

score 2 · Answer 2 · answered Jul 22 '13 at 07:34

2

var results = source.Where(x => SeatingPriorities.Contains(x.CollegeId)).ToList();

answered Jul 22 '13 at 07:34

MarcinJuraszek

124,003
15
196
263

Why default to calling `ToList`? And unless `SeatingPriorities` is an string, this won't work. – Grant Thomas Jul 22 '13 at 07:45
@GrantThomas To make sure query is executed only once. – MarcinJuraszek Jul 22 '13 at 07:49

Adil · Answer 3 · 2013-07-22T07:45:59.033

2

You can use Enumerable.Contains to find out the matches like you do with in

var result = lstSeats.Where(s=>SeatingPriorities.Contains(s.CollegeId));

edited Jul 22 '13 at 07:45

answered Jul 22 '13 at 07:34

Adil

146,340
25
209
204

Error 92 'System.Collections.Generic.List' does not contain a definition for 'Where' and the best extension method overload 'System.Linq.Enumerable.Where(System.Collections.Generic.IEnumerable, System.Func)' has some invalid arguments C:\Users\Kiran\Desktop\entrance-kiran\AllotmetBL\FirstAllotmentBL.cs 34 30 AllotmentBL – Subin Jacob Jul 22 '13 at 07:44

score 1 · Answer 4 · answered Jul 22 '13 at 07:34

1

Use Contains to achieve IN functionality in LINQ

answered Jul 22 '13 at 07:34

Amit

15,217
8
46
68

Grant Thomas · Answer 5 · 2013-07-22T07:49:57.680

1

You could use Any:

seats.Where(s => SeatingPriorities.Any(i => i.Id == s.CollegeId))

Since Contains only accepts an instance to compare (along with a possible IEqualityComparer<T>), which won't work if OptionPriority isn't an comparable to CollegeId (i.e. a string).

edited Jul 22 '13 at 07:49

answered Jul 22 '13 at 07:44

Grant Thomas

44,454
10
85
129

Use IN operator in LINQ

5 Answers5