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();

Answer 1

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();