CODEWITHSUNDEEP
c#linq
user1202606
user1202606

Reputation: 1160

query list with linq lambda expressions

How would I get participants that are in a list of counties? I get the counties in var counties, then I want to get all of the participants that have a CountyOfParticipationId that is in the list of counties.

if (collaborationId != null)
{
    var counties = (from c in db.CountyCollaborations
                    where c.CollaborationId == collaborationId
                    select c).ToList();
    participants = participants.Where(p => p.CountyOfParticipationId in counties);


}

Upvotes: 5

Views: 38891

Answers (4)

konkked
konkked

Reputation: 3231

This might be better in some situations since you won't have to store counties separately if the linq method is translating the expression to sql behind the scences.

participants = (from p in participants 
                  join c in 
                      db.CountyCollaborations
                          .Where(cty=>cty.CollaborationId == collaborationId)
                      on p.CountyOfParticipationId equals c.CountyId
                select p);

Upvotes: 1

jods
jods

Reputation: 4591

.Where(p => counties.Contains(p.CountyOfParticipationId))

Now if there's a lot of data be careful with the complexity of this. Contains in a list is O(n), so overall the algorithm is O(n*m) with n,m being the # of participants and the # of counties.

For better performance you could store the counties in a HashSet, which has O(1) Contains, meaning O(n) overall.

Of course if the number of counties is small it doesn't matter, really.

EDIT: Just noted that your list doesn't contain the ids but full objects. For the code above to work you also need to change your linq query from select c to select c.Id or something like that (don't know the name of the field).

Upvotes: 4

AaronLS
AaronLS

Reputation: 38367

Assuming each county has a CountyId:

participants = participants.Where( p => 
  counties.Select(c=> c.CountyId ).Contains( p.CountyOfParticipationId) );

Upvotes: 0

AD.Net
AD.Net

Reputation: 13399

participants = participants
.Where(p => counties.Any(c=> c.CountyId == p.CountyOfParticipationId) )

Or 

participants.Where(p => p.County.CollaborationId == collaborationId)

should also work if you have set up relations properly

Upvotes: 3

Related Questions