Linq to SQL left outer join using Lambda syntax and joining on 2 columns (composite join key)

Question

I am trying to make an Inner Join on 2 columns with Linq to SQL as a Lambda expression. The normal query would look like this.

SELECT * FROM participants 
LEFT OUTER JOIN prereg_participants ON prereg_participants.barcode = participants.barcode
AND participants.event_id = prereg_participants.event_id
WHERE (participants.event_id = 123)

I am succeeding in making a Left Outer Join on one column with the following code.

var dnrs = context.participants.GroupJoin(
    context.prereg_participants,
    x => x.barcode,
    y => y.barcode,
    (x, y) => new { deelnr = x, vi = y })
    .SelectMany(
    x => x.vi.DefaultIfEmpty(),
    (x, y) => new { deelnr = x, vi = y })
    .Where(x => x.deelnr.deelnr.event_id == 123)
    .ToList();

The problem is that with the above Lambda I get too many results because it is missing the AND participants.event_id = prereg_participants.event_id part. But whatever I try i'm not getting the correct amount of participants.

I looked at the following existing questions, but none solved my problem in writing the correct lambda. And most of the solutions are nog in lambda-format or not a Left outer join on multiple columns.

How to do joins in LINQ on multiple fields in single join

LINQ to SQL - Left Outer Join with multiple join conditions

Group By using more than two columns by Lambda expression

And most of these from this Google search

Answer 1

Query:

        var petOwners =
            from person in People
            join pet in Pets
            on new
            {
                person.Id,
                person.Age,
            }
            equals new
            {
                pet.Id,
                Age = pet.Age * 2, // owner is twice age of pet
            }
            into pets
            from pet in pets.DefaultIfEmpty()
            select new PetOwner
            {
                Person = person,
                Pet = pet,
            };

Lambda:

        var petOwners = People.GroupJoin(
            Pets,
            person => new { person.Id, person.Age },
            pet => new { pet.Id, Age = pet.Age * 2 },
            (person, pet) => new
            {
                Person = person,
                Pets = pet,
            }).SelectMany(
            pet => pet.Pets.DefaultIfEmpty(),
            (people, pet) => new
            {
                people.Person,
                Pet = pet,
            });

See code, or clone my git repo, and play!

Answer 2

I was able to get this LEFT OUTER JOIN on the composite foreign key pair barcode, event_id working in both Linq2Sql, and Entity Framework, converting to lambda syntax as per this query syntax example.

This works by creating an anonymous projection which is used in match of the left and right hand sides of the join condition:

var dnrs = context.participants.GroupJoin(
    context.prereg_participants,
    x => new { JoinCol1 = x.barcode, JoinCol2 = x.event_id }, // Left table join key
    y => new { JoinCol1 = y.barcode, JoinCol2 = y.event_id }, // Right table join key
    ...

Notes

This approach relies on the automagic equality given to identical anonymous classes, viz:

Because the Equals and GetHashCode methods on anonymous types are defined in terms of the Equals and GetHashCode methods of the properties, two instances of the same anonymous type are equal only if all their properties are equal.

So for the two projections for the join keys need to be of the same type in order to be equal, the compiler needs to see them as the same anonymous class behind the scenes, i.e.:

• The number of joined columns must be the same in both anonymous projections
• The field types must be of the same type compatable
• If the field names differ, then you will need to alias them (I've used JoinColx)

I've put a sample app up on GitHub here.

Sadly, there's no support yet for value tuples in expression trees, so you'll need to stick to anonymous types in the projections.

Answer 3

If it is a LEFT OUTER JOIN, where the left entity can have zero or maximally one connection with right entity, you can use:

// Let's have enumerables "left" and "right"
// and we want to join both full entities with nulls if there's none on the right.
left.GroupJoin(
    right,
    l => l.LeftKey,
    r => r.RightKey,
    (l, r) => new { Left = l, Right = r.FirstOrDefault() });

If you want to join left with just one attribute of right:

// Let's have enumerables "left" and "right"
// and we want to join right's attribute RightId and to set 0 for those having no Id.
left.GroupJoin(
    right,
    l => l.LeftKey,
    r => r.RightKey,
    (l, r) => new { Left = l, RightId = r.FirstOrDefault()?.RightId ?? 0 });

Answer 4

You can do this by making use of anonymous types.

Example:

var result = from a in context.participants
             join b context.prereg_participants on new { X = a.barcode, Y = a.event_id } equals new { X = b.barcode, Y = b.event_id } into A
             from b in A.DefaultIfEmpty()
             where a.event_id = 123