C# LINQ Multiple GroupJoin using Lambda Syntax

Question

I have 3 tables that I am trying to combine them together using left joins. In my application, all LINQ expressions are in query-chain format (lambda expressions).

I am trying to figure out how to accomplish this query using LINQ;

This is my SQL query;

select i.INVOICE_NUMBER, ic.CustomerName, o.BusinessUnit
from Invoices_Daily as i
left join intercompany as ic on i.customer_number = ic.customernumber
left join ordertypes as o on i.LINE_ORDERTYPE = o.OrderType

I have tried combining the GroupJoin as follows;

var commissions = data
    .GroupJoin(genericNameList,
        d => d.CUSTOMER_NUMBER,
        g => g.CustomerId,
        (d, g) => new { d, generic = g.FirstOrDefault() })
    .GroupJoin(intercompanies,
        dd => dd.d.CUSTOMER_NAME,
        i => i.CustomerId,
        (d, i) => new { data = d.d, intercompanies = i.FirstOrDefault() })
    .Select(_ => new MainGridViewModel
    {
        INVOICE_DATE = _.d.INVOICE_DATE,
        EndCustomer = _.generic == null ? _.d.CUSTOMER_NAME : _.generic.EndCustomer ?? _.d.CUSTOMER_NAME,
        LINE_ORDERTYPE = _.i.OrderType
    })
    .ToList();

However I am having a syntax error that is written below;

Error CS0411 The type arguments for method 'Enumerable.GroupJoin(IEnumerable, IEnumerable, Func, Func, Func, TResult>)' cannot be inferred from the usage. Try specifying the type arguments explicitly.

Seems like the keys (CUSTOMER_NAME and CustomerId) have different types. Also note that (d, i) => new { data = d.d, intercompanies = i.FirstOrDefault() }) would be translated to something like outer apply (select top 1 ...) instead of left join. — tukaef
@2kay CUSTOMER_NAME and CustomerId are both string type. When I use GroupJoin with the FirstOrDefault it always brings me the same results as I do left join on SQL side. I never encountered any issues yet. Its my first time tying to combine multiple tables... — 0014
@0014 which line is it happening at the first GroupJoin or the Second? — johnny 5
@johnny5 The problem happens on the second GroupJoin. In fact the intellisense does not give any results when I type i => i. on the second GroupJoin where I am expecting the IDE to bring all the columns for intercompanies. — 0014

johnny 5 johnny 5 · Accepted Answer · 2018-07-31T16:04:26

I wrote a test sample as per request showing the proper syntax, this should all be working.

public class Data
{
    public string CUSTOMER_NUMBER { get; set; }
    public string CUSTOMER_NAME { get; set; }
}

public class NameList
{
    public string CustomerId { get; set; }

}

public class InterCompanies
{
    public string CustomerId { get; set; }
}

public class Test
{
    public void TMP()
    {
        var data = new List<Data>();
        var genericNameList = new List<NameList>();
        var intercompanies = new List<InterCompanies>();

        var commissions = data
            .GroupJoin(genericNameList,
                d => d.CUSTOMER_NUMBER,
                g => g.CustomerId,
                (d, g) => new { d, generic = g.FirstOrDefault() })
            .GroupJoin(intercompanies,
                dd => dd.d.CUSTOMER_NAME,
                i => i.CustomerId,
                (d, i) => new { data = d.d, intercompanies = i.FirstOrDefault() })
            .ToList();
    }
}

My Original Thought was that you CUSTOMER_NUMBER, and CUSTOMER_NAME is wrong because why do you have 2 fields in the same class with the same value. But since you assured me they're correct, Maybe you're swapping the order of the fields expected on the data in the join, It's hard to tell without seeing your data models.

Perhaps your second join should look like so:

.GroupJoin(intercompanies,
            dd => dd.d.CUSTOMER_NUMBER,
            i => i.CUSTOMER_NAME,

C# LINQ Multiple GroupJoin using Lambda Syntax

1 Answers