How to add multiple conditions in LINQ join using method syntax

Question

I need to add multiple condition on joins. In the below LINQ I need to add condition something like this. And need to use values from different entities in those conditions. And this needs to be done using this Method syntax only. I was able to do it in query syntax. But the requirement is that behind the scene it should generate one query or as less query as possible. The query syntax using multiple "from" or multiple "join" executes multiple SELECT queries.

My second issue is that on the commented out line I need to filter on r4.VersionNo==r3.VersionNo. It's failing.

, ppp => ppp.r2.KeyColumn, t => t.KeyColumn && t.MyID==r1.MyID && t.Column2==r2.Column2, (ppp, t) => new { ppp, t })

Tried (didn't work):

, ppp => ppp.r2.KeyColumn, t => (t.KeyColumn, t.MyID, t.Column2), (ppp, t) => new { ppp, t })

Code:

var result = repo1.Join(repo2, r1 => r1.KeyColumn, r2 => r2.KeyColumn, (r1, r2) => new { r1, r2 })
                 .Join(repo3, ppp => ppp.r2.KeyColumn, t => t.KeyColumn, (ppp, t) => new { ppp, t })
                 .Join(repo4, pppt => pppt.ppp.r2.KeyColumn, r4 => r4.VersionNo, (pppt, r4) => new { pppt, r4 })
                 .Select(a => new MyObject
                    {
                        KeyColumn = a.ppp.r1.KeyColumn,
                        AnotherKeyColumn = a.ppp.r2.AnotherKeyColumn,
                 })?.ToList();

No, it doesn't work. I tried this line like in one of the examples on the above link. I get "An expression tree may not contain a tuple literal" ppp => (ppp.p.ColumnName, ppp.p.AnotherColumn), t => (t.ColumnName, t.AnotherColumn)), (ppp, t) => ppp) — coder, Mar 26 '21 at 00:47
It really doesn't make sense to use method syntax for joins. This can be done in query syntax easily without changing the compiled query. I'm curious to know what your query syntax looked like. That said, if you use navigation properties everything becomes simpler yet. Using `join` should be necessary at all. — Gert Arnold, Mar 26 '21 at 07:59
Awful, I even don't want to try to understand this mess. Use Query syntax ONLY for such queries. — Svyatoslav Danyliv, Mar 26 '21 at 13:17

score -1 · Accepted Answer · answered Mar 26 '21 at 19:30

It works now. It generates two queries, compared to many queries before, which is good.

Change:

r4 => new
{
    keycolumn1 = r4.keycolumn
    versioncolumn = r4.VersionNo
},
pppt => new 
{
    keycolumn1 = pppt.ppp.r2.KeyColumn
    versioncolumn = r4.VersionNo
},

Final Code:

var result = 
repo1.Join(repo2, r1 => r1.KeyColumn, r2 => r2.KeyColumn, (r1, r2) => new { r1, r2 })
    .Join(repo3, ppp => ppp.r2.KeyColumn, t => t.KeyColumn, (ppp, t) => new { ppp, t })
    .Join(repo4, 
        pppt => new 
        {
            keycolumn1 = pppt.ppp.r2.KeyColumn
            versioncolumn = r4.VersionNo
        },
        r4 => new
        {
            keycolumn1 = r4.keycolumn
            versioncolumn = r4.VersionNo
        },
        (pppt, r4) => new { pppt, r4 })
    .Select(a => new MyObject
    {
        KeyColumn = a.ppp.r1.KeyColumn,
        AnotherKeyColumn = a.ppp.r2.AnotherKeyColumn
    })?.ToList();

How to add multiple conditions in LINQ join using method syntax

1 Answers1