Filtering lists using LINQ

Question

I've got a list of People that are return from an external app and I'm creating an exculsion lists in my local app to give me the option of manually removing people from the list.

I have a composite key which I have created that is common to both and I want to find an efficient way of removing people from my List using my List

e.g

class Person
{
    prop string compositeKey { get; set; }
}

class Exclusions
{
    prop string compositeKey { get; set; }
}

List<Person> people = GetFromDB;

List<Exclusions> exclusions = GetFromOtherDB;

List<Person> filteredResults = People - exclustions using the composite key as a comparer

I would have thought that LINQ would be an ideal way of doing this but after trying joins, extension methods, using yields, etc. I'm still having trouble.

If this were SQL I would use a 'not in (?,?,?)' query.

Thanks in advance.

Answer 1

I couldn't figure out how to do this in pure MS LINQ, so I wrote my own extension method to do it:

public static bool In<T>(this T objToCheck, params T[] values)
{
    if (values == null || values.Length == 0) 
    {
        return false; //early out
    }
    else
    {
        foreach (T t in values)
        {
            if (t.Equals(objToCheck))
                return true;   //RETURN found!
        }

        return false; //nothing found
    }
}

Answer 2

This article might give you the answer.

Answer 3

I would just use the FindAll method on the List class. i.e.:

List<Person> filteredResults = 
    people.FindAll(p => return !exclusions.Contains(p));

Not sure if the syntax will exactly match your objects, but I think you can see where I'm going with this.

Answer 4

You can use the "Except" extension method (see http://msdn.microsoft.com/en-us/library/bb337804.aspx)

In your code

var difference = people.Except(exclusions);

Answer 5

Have a look at the Except method, which you use like this:

var resultingList = 
    listOfOriginalItems.Except(listOfItemsToLeaveOut, equalityComparer)

You'll want to use the overload I've linked to, which lets you specify a custom IEqualityComparer. That way you can specify how items match based on your composite key. (If you've already overridden Equals, though, you shouldn't need the IEqualityComparer.)

Edit: Since it appears you're using two different types of classes, here's another way that might be simpler. Assuming a List<Person> called persons and a List<Exclusion> called exclusions:

var exclusionKeys = 
        exclusions.Select(x => x.compositeKey);
var resultingPersons = 
        persons.Where(x => !exclusionKeys.Contains(x.compositeKey));

In other words: Select from exclusions just the keys, then pick from persons all the Person objects that don't have any of those keys.

Answer 6

I would do something like this but i bet there is a simpler way. i think the sql from linqtosql would use a select from person Where NOT EXIST(select from your exclusion list)

static class Program
{
    public class Person
    {
        public string Key { get; set; }
        public Person(string key)
        {
           Key = key;
        }
    }
    public class NotPerson
    {
        public string Key { get; set; }
        public NotPerson(string key)
        {
           Key = key;
        }
    }
    static void Main()
    {

       List<Person> persons = new List<Person>()
       { 
           new Person ("1"),
           new Person ("2"),
           new Person ("3"),
           new Person ("4")
       };

       List<NotPerson> notpersons = new List<NotPerson>()
       { 
           new NotPerson ("3"),
           new NotPerson ("4")
       };

       var filteredResults = from n in persons
                             where !notpersons.Any(y => n.Key == y.Key)
                             select n;

       foreach (var item in filteredResults)
       {
          Console.WriteLine(item.Key);
       }
    }
 }

Answer 7

This LINQ below will generate the SQL for a left outer join and then take all of the results that don't find a match in your exclusion list.

List<Person> filteredResults =from p in people
        join e in exclusions on p.compositeKey equals e.compositeKey into temp
        from t in temp.DefaultIfEmpty()
        where t.compositeKey == null
        select p

let me know if it works!

Answer 8

Many thanks for this guys.

I mangaged to get this down to one line:

  var results = from p in People 
                where !(from e in exclusions 
                        select e.CompositeKey).Contains(p.CompositeKey) 
                select p;

Thanks again everyone.