如何在.NET 7中用LINQ to Entities实现SQL左连接查询

原SQL查询 #

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;

原实现的错误分析 #

1. 第一种查询语法错误：错误调用LeftJoin方法，LINQ查询语法中左连接必须使用join...on...into配合from...in...DefaultIfEmpty()的标准结构，不能直接在from子句中调用LeftJoin。
2. 第二种方法语法错误：手动指定的泛型参数类型不匹配（值元组与匿名类型混用），导致编译器无法解析类型转换，且泛型参数顺序错误。

正确实现方案 #

方案1：LINQ查询语法（更易读）

await (from a in _context.AccountingTransactionsDetail
       // 左连接AccountingTransactionsDetail表
       join fact in _context.AccountingTransactionsDetail
           on new { a.JournalPayedDocument, a.YearPayedDocument, a.NumberPayedDocument, a.LineNbrPayedDocument }
           equals new { fact.JournalNumber, fact.YearOfTransaction, fact.NumberOfTransaction, fact.LineNumber }
           into factGroup
       from fact in factGroup.DefaultIfEmpty()
       // 左连接CompanyRelations表
       join c in _context.CompanyRelations
           on a.NumberRelation equals c.NumberRelation
           into cGroup
       from c in cGroup.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();

方案2：LINQ方法语法

return await _context.AccountingTransactionsDetail
    // 左连接AccountingTransactionsDetail表
    .GroupJoin(
        _context.AccountingTransactionsDetail,
        a => new { a.JournalPayedDocument, a.YearPayedDocument, a.NumberPayedDocument, a.LineNbrPayedDocument },
        fact => new { fact.JournalNumber, fact.YearOfTransaction, fact.NumberOfTransaction, fact.LineNumber },
        (a, factGroup) => new { a, factGroup })
    .SelectMany(temp => temp.factGroup.DefaultIfEmpty(), (temp, fact) => new { temp.a, fact })
    // 左连接CompanyRelations表
    .GroupJoin(
        _context.CompanyRelations,
        temp => temp.a.NumberRelation,
        c => c.NumberRelation,
        (temp, cGroup) => new { temp.a, temp.fact, cGroup })
    .SelectMany(temp => temp.cGroup.DefaultIfEmpty(), (temp, c) => new { temp.a, temp.fact, c })
    // 筛选条件
    .Where(temp => temp.a.YearOfTransaction == 0 
        && temp.a.NumberOfTransaction == 0 
        && temp.a.PaymentDocument == order 
        && temp.a.JournalNumber == journal 
        && temp.a.TypeOfJournal != 21)
    // 排序
    .OrderBy(temp => temp.a.LineNumber)
    // 映射到响应模型
    .Select(temp => new InvoiceResponseModel
    {
        NumberRelation = temp.a.NumberRelation,
        NumberOfTransaction = temp.a.NumberOfTransaction,
        YearOfTransaction = temp.a.YearOfTransaction,
        DateOfTransaction = temp.a.DateOfTransaction,
        LineNumber = temp.a.LineNumber,
        JournalNumber = temp.a.JournalNumber,
        CodeDC = temp.a.CodeDC,
        TransactAmountHomeCurrency = temp.a.TransactAmountHomeCurrency,
        TransactAmountForeignCurrency = temp.a.TransactAmountForeignCurrency,
        PaymentCirculationHomeCurrency = temp.a.PaymentCirculationHomeCurrency,
        PaymentCirculationForeignCurrency = temp.a.PaymentCirculationHomeCurrency,
        PayedHomeCurrency = temp.a.PayedHomeCurrency,
        PayedForeignCurrency = temp.a.PayedForeignCurrency,
        AccountNumber = temp.a.AccountNumber,
        BalanceForeignCurrency = temp.a.BalanceForeignCurrency,
        BalanceHomeCurrency = temp.a.BalanceHomeCurrency,
        WayOfPayment = temp.a.WayOfPayment,
        PaymentDocument = temp.a.PaymentDocument,
        ExpiryDate = temp.a.ExpiryDate,
        JournalPayedDocument = temp.a.JournalPayedDocument,
        YearPayedDocument = temp.a.YearPayedDocument,
        NumberPayedDocument = temp.a.NumberPayedDocument
    }).ToListAsync();

关键修正说明 #

• 查询语法中使用join...on...into创建分组，再通过from...in...DefaultIfEmpty()实现左连接，符合LINQ查询语法规范。
• 方法语法中移除了手动指定的错误泛型参数，让编译器自动推断类型；统一使用匿名类型作为连接键，确保类型匹配。

内容的提问来源于stack exchange，提问作者Pieter Dreissen