Linq outer join with conditions

Question

I need an efficient LINQ query (in method syntax if possible) to get all items from a collection A that have no corresponding key in a second collection B (1 to n) or if there exist elements in B, take only those that have MyValue null. In short: return all elements of A that do not exist in B or if they exist in B where at minimum one row has MyValue = null.

table A
{
  int MyKey (primary_key);
}

table B
{ 
  int MyKey (foreign_key to A.MyKey);
  string MyValue;
}

I was trying Except(), but this only works, when both collections are of the same type. I was trying GroupJoin(), but I did not find a way how to remove the duplicates after joining.

a.GroupJoin(
 b.Where(item => item.Value = null), 
 el => el.MyKey, 
 el2 => el2.MyKey,
 (el3, el4) => el3);

With this, I filter out items in B, that are after joined again because they don't exist anymore.

In pure sql it is very easy to achive:

select * from A a left join B b on a.MyKey = b.MyKey where MyValue is null;

score 2 · Accepted Answer · answered Sep 13 '13 at 13:04

Well, it's much prettier in LINQ syntax:

var result = (
    from a in aCollection
    join b in bCollection on a.Key equals b.AKey into bSubCollection
    where !bSubCollection.Any(x => x.Value != null)
    select a
);

But here it is in method syntax as well:

var result = aCollection
    .GroupJoin(bCollection, a => a.Key, b => b.AKey, (a, bSub) => new { a, bSub })
    .Where(c => !c.bSub.Any(x => x.Value != null))
    .Select(c => c.a);

Essentially you are group joining into an anonymous type with the a and a collection of b then just filtering the collection of c (a is already distinct) by whether there are any bs with a non-null Value.

That's it! I only used "c.bSub.All(x => x.Value == null)" instead of "!c.bSub.Any(x => x.Value != null))" — StefanG, Sep 13 '13 at 13:28

score 0 · Answer 2 · edited May 23 '17 at 12:29

you need to translate in method syntax but the query syntax for the left outer join you wrote should be the following:

 var query = from itemA in a
        join itemB in b on
            itemA.MyKey equals itemB.MyKey into joinTable
        from itemB in joinTable.DefaultIfEmpty()
        where (itemB == null) || (itemB.MyValue == null)
        select itemA;

you need to apply a distinct to the result. You can see the following post: Distinct by property of class by linq

or use DistinctBy in MoreLinq

Linq outer join with conditions

2 Answers2