What's a good way to perform a Left join using LINQ?

Question

I'm trying to get this SQL Server query in a method in my .NET 7 application using LINQ to entities:

SELECT a.NumberRelation,
       a.NumberOfTransaction,
       a.YearOfTransaction,
       a.DateOfTransaction,
       a.LineNumber,
       a.JournalNumber,
       a.CodeDC,
       a.TransactAmountHomeCurrency,
       a.TransactAmountForeignCurrency,
       a.PaymentCirculationHomeCurrency,
       a.PaymentCirculationHomeCurrency AS PaymentCirculationForeignCurrency,
       a.PayedHomeCurrency,
       a.PayedForeignCurrency,
       a.AccountNumber,
       a.BalanceForeignCurrency,
       a.BalanceHomeCurrency,
       a.WayOfPayment,
       a.PaymentDocument,
       a.ExpiryDate,
       a.JournalPayedDocument,
       a.YearPayedDocument,
       a.NumberPayedDocument
FROM AccountingTransactionsDetail AS a
LEFT JOIN AccountingTransactionsDetail AS fact
    ON fact.JournalNumber = a.JournalPayedDocument
    AND fact.YearOfTransaction = a.YearPayedDocument
    AND fact.NumberOfTransaction = a.NumberPayedDocument
    AND fact.LineNumber = a.LineNbrPayedDocument
LEFT JOIN CompanyRelations AS c
    ON c.NumberRelation = a.NumberRelation
WHERE a.YearOfTransaction = 0
    AND a.NumberOfTransaction = 0
    AND a.PaymentDocument = @order
    AND a.JournalNumber = @journal
    AND a.TypeOfJournal != 21
ORDER BY a.LineNumber;

Unfortunately that doesn't seem too easy to do.

I've tried different approaches such as:

await (from a in _context.AccountingTransactionsDetail
                              from fact in _context.AccountingTransactionsDetail.Where(f => f.JournalNumber == a.JournalPayedDocument &&
                              f.YearOfTransaction == a.YearPayedDocument && f.NumberOfTransaction == a.NumberPayedDocument && f.LineNumber == a.LineNbrPayedDocument).DefaultIfEmpty()
                              from c in _context.CompanyRelations.LeftJoin(c => c.NumberRelation == a.NumberRelation)
                              where a.YearOfTransaction == 0 && a.NumberOfTransaction == 0 && a.PaymentDocument == order
                              && a.JournalNumber == journal && a.TypeOfJournal != 21
                              orderby a.LineNumber
                              select new InvoiceResponseModel
                              {
                                  NumberRelation = a.NumberRelation,
                                  NumberOfTransaction = a.NumberOfTransaction,
                                  YearOfTransaction = a.YearOfTransaction,
                                  DateOfTransaction = a.DateOfTransaction,
                                  LineNumber = a.LineNumber,
                                  JournalNumber = a.JournalNumber,
                                  CodeDC = a.CodeDC,
                                  TransactAmountHomeCurrency = a.TransactAmountHomeCurrency,
                                  TransactAmountForeignCurrency = a.TransactAmountForeignCurrency,
                                  PaymentCirculationHomeCurrency = a.PaymentCirculationHomeCurrency,
                                  PaymentCirculationForeignCurrency = a.PaymentCirculationHomeCurrency,
                                  PayedHomeCurrency = a.PayedHomeCurrency,
                                  PayedForeignCurrency = a.PayedForeignCurrency,
                                  AccountNumber = a.AccountNumber,
                                  BalanceForeignCurrency = a.BalanceForeignCurrency,
                                  BalanceHomeCurrency = a.BalanceHomeCurrency,
                                  WayOfPayment = a.WayOfPayment,
                                  PaymentDocument = a.PaymentDocument,
                                  ExpiryDate = a.ExpiryDate,
                                  JournalPayedDocument = a.JournalPayedDocument,
                                  YearPayedDocument = a.YearPayedDocument,
                                  NumberPayedDocument = a.NumberPayedDocument
                              }).ToListAsync();

But that gives me a CS7036 on the LeftJoin(). I also tried:

return await _context.AccountingTransactionsDetail
                    .GroupJoin<AccountingTransactionDetail, (int JournalPayedDocument, int YearPayedDocument, int NumberPayedDocument, int LineNbrPayedDocument), (int JournalNumber, int YearOfTransaction, int NumberOfTransaction, int LineNumber), (AccountingTransactionDetail a, IEnumerable<AccountingTransactionDetail> fact)>(
                        _context.AccountingTransactionsDetail,
                        a => new { a.JournalPayedDocument, a.YearPayedDocument, a.NumberPayedDocument, a.LineNbrPayedDocument },
                        fact => new { fact.JournalNumber, fact.YearOfTransaction, fact.NumberOfTransaction, fact.LineNumber },
                        (a, fact) => new { a, fact })
                    .SelectMany(temp0 => temp0.fact.DefaultIfEmpty(), (temp0, fact) => new { temp0.a, fact })
                    .GroupJoin(
                        _context.CompanyRelations,
                        temp1 => temp1.a.NumberRelation,
                        c => c.NumberRelation,
                        (temp1, c) => new { temp1.a, temp1.fact, c })
                    .SelectMany(temp2 => temp2.c.DefaultIfEmpty(), (temp2, c) => new { temp2.a, temp2.fact, c })
                    .Where(temp3 => temp3.a.YearOfTransaction == 0 && temp3.a.NumberOfTransaction == 0 && temp3.a.PaymentDocument == order && temp3.a.JournalNumber == journal && temp3.a.TypeOfJournal != 21)
                    .OrderBy(temp4 => temp4.a.LineNumber)
                    .Select(temp5 => new InvoiceResponseModel
                    {
                        NumberRelation = temp5.a.NumberRelation,
                        NumberOfTransaction = temp5.a.NumberOfTransaction,
                        YearOfTransaction = temp5.a.YearOfTransaction,
                        DateOfTransaction = temp5.a.DateOfTransaction,
                        LineNumber = temp5.a.LineNumber,
                        JournalNumber = temp5.a.JournalNumber,
                        CodeDC = temp5.a.CodeDC,
                        TransactAmountHomeCurrency = temp5.a.TransactAmountHomeCurrency,
                        TransactAmountForeignCurrency = temp5.a.TransactAmountForeignCurrency,
                        PaymentCirculationHomeCurrency = temp5.a.PaymentCirculationHomeCurrency,
                        PaymentCirculationForeignCurrency = temp5.a.PaymentCirculationHomeCurrency,
                        PayedHomeCurrency = temp5.a.PayedHomeCurrency,
                        PayedForeignCurrency = temp5.a.PayedForeignCurrency,
                        AccountNumber = temp5.a.AccountNumber,
                        BalanceForeignCurrency = temp5.a.BalanceForeignCurrency,
                        BalanceHomeCurrency = temp5.a.BalanceHomeCurrency,
                        WayOfPayment = temp5.a.WayOfPayment,
                        PaymentDocument = temp5.a.PaymentDocument,
                        ExpiryDate = temp5.a.ExpiryDate,
                        JournalPayedDocument = temp5.a.JournalPayedDocument,
                        YearPayedDocument = temp5.a.YearPayedDocument,
                        NumberPayedDocument = temp5.a.NumberPayedDocument
                    }).ToListAsync();

but then the GoupJoin()s are a mess with CS0029, CS1662 and CS1061

Answer 1

After reviewing your help, I just did the obvious and did 2 left joins one after the other like this:

return await (from a in _context.AccountingTransactionsDetail 
from fact in _context.AccountingTransactionsDetail.Where(f => f.JournalNumber == a.JournalPayedDocument &&
f.YearOfTransaction == a.YearPayedDocument && f.NumberOfTransaction == a.NumberPayedDocument && f.LineNumber == a.LineNbrPayedDocument).DefaultIfEmpty()
from c in _context.CompanyRelations.Where(c => c.NumberRelation == a.NumberRelation).DefaultIfEmpty()
where a.YearOfTransaction == 0 && a.NumberOfTransaction == 0 && a.PaymentDocument == order && a.JournalNumber == journal && a.TypeOfJournal != 21
orderby a.LineNumber
select new InvoiceResponseModel
{
     NumberRelation = a.NumberRelation,
     NumberOfTransaction = a.NumberOfTransaction,
     YearOfTransaction = a.YearOfTransaction,
     DateOfTransaction = a.DateOfTransaction,
     LineNumber = a.LineNumber,
     JournalNumber = a.JournalNumber,
     CodeDC = a.CodeDC,
     TransactAmountHomeCurrency = a.TransactAmountHomeCurrency,
     TransactAmountForeignCurrency = a.TransactAmountForeignCurrency,
     PaymentCirculationHomeCurrency = a.PaymentCirculationHomeCurrency,
     PaymentCirculationForeignCurrency = a.PaymentCirculationHomeCurrency,
     PayedHomeCurrency = a.PayedHomeCurrency,
     PayedForeignCurrency = a.PayedForeignCurrency,
     AccountNumber = a.AccountNumber,
     BalanceForeignCurrency = a.BalanceForeignCurrency,
     BalanceHomeCurrency = a.BalanceHomeCurrency,
     WayOfPayment = a.WayOfPayment,
     PaymentDocument = a.PaymentDocument,
     ExpiryDate = a.ExpiryDate,
     JournalPayedDocument = a.JournalPayedDocument,
     YearPayedDocument = a.YearPayedDocument,
     NumberPayedDocument = a.NumberPayedDocument
}).ToListAsync();

This should give me the same result als the SQL query above.