Linq to SQL Left Outer Join Using Lambda Syntax and Joining on 2 Columns (Composite Join Key)

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

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.

linq to sql join on multiple columns using lambda error

The names of the anonymous types have to match. In the first query you gave them names field1 and field2. The second query will use Soc\ ID on the first and Soc\ Code on the second while the Soc field will match up the ID name will not match the Code name. So the method syntax equivalent of your query syntax would actually be

var query= this.context.TabA.Join(
    this.context.TabB,
    s => new { field1 = s.Soc, field2 = s.ID },
    h => new { field1 = h.Soc, field2 = h.Code },
    (s, h) => s);

But you only really have to name the second property of the anonymous class since it will take the name Soc for the first in both cases.

How do I put a left join between two tables in Linq-to-SQL using the keys?

You have to use DefaultIfEmpty() on the second table to generate the outer join or use navigation properties on the store. Something along the lines of

var query = from sr in db.secs
            join s in db.subs into secSubs
            from srs in secSubs.DefaultIfEmpty()
            // rest of the query follows

Alternatively with navigation properties you might be able to do something like

var query = from sr in db.secs
            from s in sr.Subs
            select new {sr, s}

A third option is to possibly use a quasi ANSI-82 syntax rather than ANSI-92 join syntax and make the first item above more pallatable:

var query = from sr in db.secs
            from s in db.Subs.Where(s1 => s1.subId == sr.Id).DefaultIfEmpty()
            select new {sr, s}

I wrote up a more detailed post on this a while back at https://www.thinqlinq.com/Post.aspx/Title/Left-Outer-Joins-in-LINQ-with-Entity-Framework.

LINQ to SQL - Left Outer Join with multiple join conditions

You need to introduce your join condition before calling DefaultIfEmpty(). I would just use extension method syntax:

from p in context.Periods
join f in context.Facts on p.id equals f.periodid into fg
from fgi in fg.Where(f => f.otherid == 17).DefaultIfEmpty()
where p.companyid == 100
select f.value

Or you could use a subquery:

from p in context.Periods
join f in context.Facts on p.id equals f.periodid into fg
from fgi in (from f in fg
             where f.otherid == 17
             select f).DefaultIfEmpty()
where p.companyid == 100
select f.value

Lambda / Linq Left joining table(s) does not return expected result

Gert Arnold has written an excellent answer to a similar problem explaining how the Linq joins work:
Linq to Entities join vs groupjoin

It seems like you are missing the flattening part by using .SelectMany() on the Linq query.
This is described in the linked answer just above part 2.

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

The solution with the anonymous type should work fine. LINQ can only represent equijoins (with join clauses, anyway), and indeed that's what you've said you want to express anyway based on your original query.

If you don't like the version with the anonymous type for some specific reason, you should explain that reason.

If you want to do something other than what you originally asked for, please give an example of what you really want to do.

EDIT: Responding to the edit in the question: yes, to do a "date range" join, you need to use a where clause instead. They're semantically equivalent really, so it's just a matter of the optimisations available. Equijoins provide simple optimisation (in LINQ to Objects, which includes LINQ to DataSets) by creating a lookup based on the inner sequence - think of it as a hashtable from key to a sequence of entries matching that key.

Doing that with date ranges is somewhat harder. However, depending on exactly what you mean by a "date range join" you may be able to do something similar - if you're planning on creating "bands" of dates (e.g. one per year) such that two entries which occur in the same year (but not on the same date) should match, then you can do it just by using that band as the key. If it's more complicated, e.g. one side of the join provides a range, and the other side of the join provides a single date, matching if it falls within that range, that would be better handled with a where clause (after a second from clause) IMO. You could do some particularly funky magic by ordering one side or the other to find matches more efficiently, but that would be a lot of work - I'd only do that kind of thing after checking whether performance is an issue.

How would this Linq query with Join be written as a Lambda?

This part looks very weird (intended or typo?) :

....Function(p, t).Select(Function(p1)...

Something like this should work as far as I can see :

db.be_Posts.
    OrderByDescending(Function(x) x.DateCreated).
    Join(db.be_PostTag, _
            Function(p) p.PostID, _
            Function(t) t.PostID, _
            Function(p, t) New be_PostsViewModel With 
                            {
                                .Id = p.PostID,
                                ....
                                ....
                            })

And your LINQ query syntax can be translated to method syntax like this :

db.be_Posts.
    Join(db.be_PostTag, _
            Function(p) p.PostID, _
            Function(t) t.PostID, _
            Function(p, t) p.Title)

---

Related Topics