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

asp.netclambdalinq-to-sqlsql server

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

Best Answer

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!

Related Topic