CODEWITHSUNDEEP
c#linq
Craig
Craig

Reputation: 18734

Filter items from database based on a List<>

I have a method that accepts two List<int> for which I need to get data from the database based on the List<>s.

So, I receive a List<PersonId> and List<NationalityId> for example, and I need to get a result set where records match the PersonIds and NationalistIds.

public List<PersonDTO> SearchPeople(List<int> persons, Lisy<int> nationalities)
{
  var results = (from c in myDbContect.People where .... select c).ToList();
}

Note that I think Lists might be null.

Is there an efficient way? I was going to try:

where ((persons != null && persons.Count > 0) && persons persons.Contains(x=>x.PersonId))

But this would generate rather inefficient SQL, and as I add more search parameters, the linq may get very messy.

Is there an efficient way to achieve this?

The join method may be easy to read, but the issue I face is that IF the input list is empty, then it shouldn't filter. That is, if nationalities is empty, don't filter any out:

    var results = (from c in entities.Persons
                   join p in persons on c.PersonId equals b
                   join n in nationalities on c.NationalityId equals n

equals n select c).ToList();

This would return no results if any of the lists were empty. Which, is bad.

Upvotes: 1

Views: 2122

Answers (2)

Michael Petito
Michael Petito

Reputation: 13171

If you join an IQueryable with an IEnumerable (in this case, entities.Persons and persons), your filtering will not happen within your query. Instead, your IQueryable is enumerated, retrieving all of your records from the database, while the join is performed in memory using the IEnumerable join method.

To perform your filtering against a list within your query, there are two main options:

  1. Join using an IQueryable on both sides. This might be possible if your list of ids comes from the execution of another query, in which case you can use the underlying query in your join instead of the resulting set of ids.

  2. Use the contains operator against your list. This is only possible with small lists, because each additional id requires its own query parameter. If you have many ids, you can possibly extend this approach with batching.

If you want to skip filtering when the list is empty, then you might consider using the extension method invocation instead of the LINQ syntax. This allows you to use an if statement:

IQueryable<Person> persons = entities.persons;
List<int> personIds = new List<int>();
if(personIds.Count > 0)
{
    persons = persons.Where(p => personIds.Contains(p.PersonId));
}
var results = persons.ToList();

Note that the Where predicate uses option #2 above, and is only applied if there are any ids in the collection.

Upvotes: 1

dlght
dlght

Reputation: 926

If you want to get all the records for persons for example if the list is empty and then filter by nationalityId list if its not empty you can do something like this:

List<int> personsIds = ...;
List<int> nationalitiesIds = ...;

var results = (from c in entities.Persons
                   join p in persons on c.PersonId equals b
                   join n in nationalities on c.NationalityId equals n
                   where ((personsIds == null || personsIds.Contains(p.Id)) 
                       && (nationalitiesIds == null || nationalitiesIds.Contains(n.Id))
select c).ToList();

Upvotes: 0

Related Questions