Inner Join with two equalities inside on clause in LINQ Lambda

Question

I'm trying to convert a Sql query to a Linq Lambda style query. Thought this would be something easy but it turned out not.

SQL Query is as follows;

select distinct t1.ID from table1 t1
    inner Join table2 t2on (t2.FromId= t1.Id or t2.ToId= t1.Id)
where t1.TenantId = 12
and t2.wId= 51

All examples I came across are for one clause joins so far. I wrote something like this

    actStaList = _db.t1
        .Join(_db.t2,
        s => s.ID,
        wf => wf.ToId,
        (s, wf) => new { t1= s, t2= wf }
        )
        .Where(a => a.t1.Tenant.Guid == _tenantGuid)
        .Select (m=>m.t1.ID)
        .ToList();

It is obvious this won't work as the sql query above but still it's a start. Still I can't figure where should I add the second part inside INNER JOIN and Distinct keyword.

Anu Viswan · Accepted Answer · 2019-01-03T09:39:47.453

2

One option you have is to use two separate Linq Queries and concat the result(and eliminating duplicates).

 var left = t1.Join(t2,
                    s => s.ID,
                    wf => wf.ToId,
                    (s, wf) => new { t1= s, t2= wf }
                    ).Select(x=>x);

var right = t1.Join(t2,
                    s => s.ID,
                    wf => wf.FromId,
                    (s, wf) => new { t1= s, t2= wf }
                    ).Select(x=>x);

    var actStaList = left.Concat(right).Select(m=>m.t1.ID)
                                       .Distinct();

Please note I have omitted the Where Clause in the example as in the OP, both Sql version and your attempted Linq version seem to have different conditions. You can add them yourself.

edited Jan 03 '19 at 09:39

answered Jan 03 '19 at 09:38

Anu Viswan

17,797
2
22
51

Where clause is easy to add so I left it out for sake of simplicity, thanks for the heads up. – Ege Bayrak Jan 03 '19 at 09:39
Note that you can do `left.Union(right)` and remove `.Distinct()` because UNION is an implicit DISTINCT. – Gert Arnold Jan 03 '19 at 14:28

score 1 · Answer 2 · answered Jan 03 '19 at 10:29

The LINQ Join statement only supports equi-joins. For other types of equality you can't use the Join statement and have to code the equality manually. This is much easier in query syntax:

actStaList = (
    from t1 in _db.table1
    from t2 in _db.table2
    where t2.FromId == t1.Id || t2.ToId == t1.Id
    where t1.TenantId == 12 && t2.wId == 51
    select t1.ID
    ).Distinct();

For the record, you can avoid the Distinct statement by executing this as a SQL EXISTS statement:

actStaList =
    from t1 in _db.table1
    where t1.TenantId == 12 
    where (from t2 in _db.table2
           where t2.wId == 51 && (t2.FromId == t1.Id || t2.ToId == t1.Id)
           select t2).Any()
    select t1.ID;

Inner Join with two equalities inside on clause in LINQ Lambda

2 Answers2