linq - How to find if one collection's key columns are a subset of another collection?

Question

I'm using .NET 4.5 in C#, and I've got two collections of different classes that I'm trying to compare. They have some columns with similar names, but they don't implement a common interface (and can't for coding-standard reasons).

The first class looks like this:

class Foo
{
    string Key1 {get; set;}
    string Key2 {get; set;}
    string NotKey {get; set;}
    string AlsoNotKey {get; set;}
}

While the second one looks like this:

class Bar
{
    string Key1 {get; set;}
    string Key2 {get; set;}
}

What I want to do is pass a collection of Foo into a method and return true when every distinct pair of Key1 and Key2 in it has a match in a collection of Bar retrieved from a database (via EF), but I can't figure out how to do that.

My first idea looked something like this...

//IEnumerable<Foo> foos
//DbSet<Bar> bars
foos.All(foo => foo.Key1 == bars.Key1 && foo.Key2 == bars.Key2)

...but it doesn't work because bars is a collection too, and I can't just use Contains() around each column because then it won't compare both columns as a pair.

In SQL, I could do something like

SELECT COUNT(*)
FROM foos
JOIN bars
ON foos.key1 = bars.key1
AND foos.key2 = bars.key2

and compare it to the number of records in foos by itself, but how can I translate that to LINQ?

Edit: Found this related question. How to use linq `Except` with multiple properties with different class?

Might try something like this, unless there's a better way.

foos.All(f => bars.Any(b => f.Key1 == b.Key1 && f.Key2 == b.Key2))

Did you try [LINQ's join](https://msdn.microsoft.com/en-us/library/bb534675.aspx)? It might do exactly what you're looking for. — woot ness, Feb 10 '17 at 17:45
You should write a Join query in Linq to Entities. https://msdn.microsoft.com/en-us/library/bb311040.aspx https://msdn.microsoft.com/en-us/library/bb896266(v=vs.110).aspx — DaniDev, Feb 10 '17 at 17:46

score 1 · Answer 1 · answered Feb 10 '17 at 17:46

Try this:

var x = (from f in foos
         join b in bars on new { f.Key1, f.Key2 } equals new { b.Key1, b.Key2 }
         select f).Count();

I know it is a long form, but this is the case where it is more descriptive than the short form.

The big assumption is, obviously, that your keys don't duplicate. If they do you will get wrong item count

score 1 · Answer 2 · answered Feb 10 '17 at 17:54

Try this

bool IsFooASubsetOfBar(List<Foo> foos, List<Bar> bars)
{
    if (foos == null || bars == null)
        return false;

    return foos.All(foo => bars.Any(bar => bar.Key1 == foo.Key1 && bar.Key2 == foo.Key2));
}

Usage:

List<Bar> bars = new List<Bar>()
            {
                new Bar() { Key1 = "1", Key2 = "1" },
                new Bar() { Key1 = "2", Key2 = "2" },
                new Bar() { Key1 = "3", Key2 = "3" }
            };

List<Foo> foos = new List<Foo>()
            {
                new Foo() { Key1 = "2", Key2 = "2" },
                new Foo() { Key1 = "3", Key2 = "3" }
            };

bool b = IsFooASubsetOfBar(foos);

score 1 · Answer 3 · answered Feb 10 '17 at 18:10

You can use a join, but I wouldn't do a count, as that would force you to take into consideration duplicate key tuples. I'd use a left join instead:

public bool Matches(IEnumerable<Foo> foos, DbSet<Bar> bars)
{
       return (from f in foos
               join b in bars on new {f.Key1, f.Key2} equals new {b.Key1, b.Key2} into fjb
               from match in fjb.DefaultIfEmpty()
               select match != null
               ).All(isMatch => isMatch);       
}

linq - How to find if one collection's key columns are a subset of another collection?

3 Answers3