Left Outer Join SQL to LINQ Extension Methods

Question

I been looking for a way to convert this SQL statement to LINQ extension methods but have not found any approach.

Select A.* FROM (A LEFT OUTER JOIN B ON A.ID=B.ID) Where B IS NULL

Should I do the where first, then the join and finally the select. What methods can I use.

Check this link https://msdn.microsoft.com/en-us/vstudio/ee908647.aspx#leftouterjoin it may help you — Max, Sep 11 '18 at 14:55
You could use a not `Contains` (or not `Any`) test instead of translating the SQL directly, or just translate the SQL using my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786). — NetMage, Sep 11 '18 at 17:58
Does `A` have navigation property `B`? Or is there some relation between `A` and `B` configured in EF? — Fabio, Sep 12 '18 at 01:56

score 1 · Answer 1 · answered Sep 12 '18 at 01:49

You can use the sequence of GroupJoin and SelectMany methods. I made an example, in which DbSet instances are presented with simple collection. Just to make it clear and reproducable:

[Test]
public void Linq_LeftOuterJoin()
{
    // Arrange
    List<A> A = new List<A>
    {
        new A { Id = 1},
        new A { Id = 2},
        new A { Id = 3},
        new A { Id = 4}
    };

    List<B> B = new List<B>
    {
        new B { Id = 3},
        new B { Id = 4}
    };

    // Act
    var join = A.GroupJoin(B, a => a.Id, b => b.Id, (a, b) => new {a, b})
                .SelectMany(t => t.b.DefaultIfEmpty(), (a, b) => new {a.a, b})
                .Where(t => t.b == null)
                .Select(t => t.a);

    //Assert
    Assert.AreEqual(join.Count(), 2);
    Assert.AreEqual(join.Count(a => a.Id == 1), 1);
    Assert.AreEqual(join.Count(a => a.Id == 2), 1);
}

Also please take a look on this question with many examples. Hope it helps.

score 0 · Answer 2 · answered Sep 12 '18 at 05:28

0

Try this solution:

var result = (from A in listA
              join B in listB on A.ID equlas B.ID into subs
              from sub in subs.DefaultIfEmpty()
              where sub == null
              select A).ToList();

answered Sep 12 '18 at 05:28

Slava Utesinov

13,410
2
19
26

Left Outer Join SQL to LINQ Extension Methods

2 Answers2