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

huangapple
go评论90阅读模式
英文:

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

问题

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

  1. SELECT a.NumberRelation,
  2.        a.NumberOfTransaction,
  3.        a.YearOfTransaction,
  4.        a.DateOfTransaction,
  5.        a.LineNumber,
  6.        a.JournalNumber,
  7.        a.CodeDC,
  8.        a.TransactAmountHomeCurrency,
  9.        a.TransactAmountForeignCurrency,
  10.        a.PaymentCirculationHomeCurrency,
  11.        a.PaymentCirculationHomeCurrency AS PaymentCirculationForeignCurrency,
  12.        a.PayedHomeCurrency,
  13.        a.PayedForeignCurrency,
  14.        a.AccountNumber,
  15.        a.BalanceForeignCurrency,
  16.        a.BalanceHomeCurrency,
  17.        a.WayOfPayment,
  18.        a.PaymentDocument,
  19.        a.ExpiryDate,
  20.        a.JournalPayedDocument,
  21.        a.YearPayedDocument,
  22.        a.NumberPayedDocument
  23. FROM AccountingTransactionsDetail AS a
  24. LEFT JOIN AccountingTransactionsDetail AS fact
  25.     ON fact.JournalNumber = a.JournalPayedDocument
  26.     AND fact.YearOfTransaction = a.YearPayedDocument
  27.     AND fact.NumberOfTransaction = a.NumberPayedDocument
  28.     AND fact.LineNumber = a.LineNbrPayedDocument
  29. LEFT JOIN CompanyRelations AS c
  30.     ON c.NumberRelation = a.NumberRelation
  31. WHERE a.YearOfTransaction = 0
  32.     AND a.NumberOfTransaction = 0
  33.     AND a.PaymentDocument = @order
  34.     AND a.JournalNumber = @journal
  35.     AND a.TypeOfJournal != 21
  36. ORDER BY a.LineNumber;

Unfortunately that doesn't seem too easy to do.

I've tried different approaches such as:

  1. await (from a in _context.AccountingTransactionsDetail
  2.        from fact in _context.AccountingTransactionsDetail.Where(f => f.JournalNumber == a.JournalPayedDocument &&
  3.                                                               f.YearOfTransaction == a.YearPayedDocument &&
  4.                                                               f.NumberOfTransaction == a.NumberPayedDocument &&
  5.                                                               f.LineNumber == a.LineNbrPayedDocument).DefaultIfEmpty()
  6.        from c in _context.CompanyRelations.LeftJoin(c => c.NumberRelation == a.NumberRelation)
  7.        where a.YearOfTransaction == 0 && a.NumberOfTransaction == 0 && a.PaymentDocument == order
  8.           && a.JournalNumber == journal && a.TypeOfJournal != 21
  9.        orderby a.LineNumber
  10.        select new InvoiceResponseModel
  11.        {
  12.            NumberRelation = a.NumberRelation,
  13.            NumberOfTransaction = a.NumberOfTransaction,
  14.            YearOfTransaction = a.YearOfTransaction,
  15.            DateOfTransaction = a.DateOfTransaction,
  16.            LineNumber = a.LineNumber,
  17.            JournalNumber = a.JournalNumber,
  18.            CodeDC = a.CodeDC,
  19.            TransactAmountHomeCurrency = a.TransactAmountHomeCurrency,
  20.            TransactAmountForeignCurrency = a.TransactAmountForeignCurrency,
  21.            PaymentCirculationHomeCurrency = a.PaymentCirculationHomeCurrency,
  22.            PaymentCirculationForeignCurrency = a.PaymentCirculationHomeCurrency,
  23.            PayedHomeCurrency = a.PayedHomeCurrency,
  24.            PayedForeignCurrency = a.PayedForeignCurrency,
  25.            AccountNumber = a.AccountNumber,
  26.            BalanceForeignCurrency = a.BalanceForeignCurrency,
  27.            BalanceHomeCurrency = a.BalanceHomeCurrency,
  28.            WayOfPayment = a.WayOfPayment,
  29.            PaymentDocument = a.PaymentDocument,
  30.            ExpiryDate = a.ExpiryDate,
  31.            JournalPayedDocument = a.JournalPayedDocument,
  32.            YearPayedDocument = a.YearPayedDocument,
  33.            NumberPayedDocument = a.NumberPayedDocument
  34.        }).ToListAsync();

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

  1. return await _context.AccountingTransactionsDetail
  2.                     .GroupJoin<AccountingTransactionDetail, (int JournalPayedDocument, int YearPayedDocument, int NumberPayedDocument, int LineNbrPayedDocument), (int JournalNumber, int YearOfTransaction, int NumberOfTransaction, int LineNumber), (AccountingTransactionDetail a, IEnumerable<AccountingTransactionDetail> fact)>(
  3.                         _context.AccountingTransactionsDetail,
  4.                         a => new { a.JournalPayedDocument, a.YearPayedDocument, a.NumberPayedDocument, a.LineNbrPayedDocument },
  5.                         fact => new { fact.JournalNumber, fact.YearOfTransaction, fact.NumberOfTransaction, fact.LineNumber },
  6.                         (a, fact) => new { a, fact })
  7.                     .SelectMany(temp0 => temp0.fact.DefaultIfEmpty(), (temp0, fact) => new { temp0.a, fact })
  8.                     .GroupJoin(
  9.                         _context.CompanyRelations,
  10.                         temp1 => temp1.a.NumberRelation,
  11.                         c => c.NumberRelation,
  12.                         (temp1, c) => new { temp1.a, temp1.fact, c })
  13.                     .SelectMany(temp2 => temp2.c.DefaultIfEmpty(), (temp2, c) => new { temp2.a, temp2.fact, c })
  14.                     .Where(temp3 => temp3.a.YearOfTransaction == 0 && temp3.a.NumberOfTransaction == 0 && temp3.a.PaymentDocument == order && temp3.a.JournalNumber == journal && temp3.a.TypeOfJournal != 21)
  15.                     .OrderBy(temp4 => temp4.a.LineNumber)
  16.                     .Select(temp5 => new InvoiceResponseModel
  17.                     {
  18.                         NumberRelation = temp5.a.NumberRelation,
  19.                         NumberOfTransaction = temp5.a.NumberOfTransaction,
  20.                         YearOfTransaction = temp5.a.YearOfTransaction,
  21.                         DateOfTransaction = temp5.a.DateOfTransaction,
  22.                         LineNumber = temp5.a.LineNumber,
  23.                         JournalNumber = temp5.a.JournalNumber,
  24.                         CodeDC = temp5.a.CodeDC,
  25.                         TransactAmountHomeCurrency = temp5.a.TransactAmountHomeCurrency,
  26.                         TransactAmountForeignCurrency = temp5.a.TransactAmountForeignCurrency,
  27.                         PaymentCirculationHomeCurrency = temp5.a.PaymentCirculationHomeCurrency,
  28.                         PaymentCirculationForeignCurrency = temp5.a.PaymentCirculationHomeCurrency,
  29.                         PayedHomeCurrency = temp5.a.PayedHomeCurrency,
  30.                         PayedForeignCurrency = temp5.a.PayedForeignCurrency,
  31.                         AccountNumber = temp5.a.AccountNumber,
  32.                         BalanceForeignCurrency = temp5.a.BalanceForeignCurrency,
  33.                         BalanceHomeCurrency = temp5.a.BalanceHomeCurrency,
  34.                         WayOfPayment = temp5.a.WayOfPayment,
  35.                         PaymentDocument = temp5.a.PaymentDocument,
  36.                         ExpiryDate = temp5.a.ExpiryDate,
  37.                         JournalPayedDocument = temp5.a.JournalPayedDocument,
  38.                         YearPayedDocument = temp5.a.YearPayedDocument,
  39.                         NumberPayedDocument = temp5.a.NumberPayedDocument
  40.                     }).ToListAsync();

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

英文:

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

  1. SELECT a.NumberRelation,
  2.        a.NumberOfTransaction,
  3.        a.YearOfTransaction,
  4.        a.DateOfTransaction,
  5.        a.LineNumber,
  6.        a.JournalNumber,
  7.        a.CodeDC,
  8.        a.TransactAmountHomeCurrency,
  9.        a.TransactAmountForeignCurrency,
  10.        a.PaymentCirculationHomeCurrency,
  11.        a.PaymentCirculationHomeCurrency AS PaymentCirculationForeignCurrency,
  12.        a.PayedHomeCurrency,
  13.        a.PayedForeignCurrency,
  14.        a.AccountNumber,
  15.        a.BalanceForeignCurrency,
  16.        a.BalanceHomeCurrency,
  17.        a.WayOfPayment,
  18.        a.PaymentDocument,
  19.        a.ExpiryDate,
  20.        a.JournalPayedDocument,
  21.        a.YearPayedDocument,
  22.        a.NumberPayedDocument
  23. FROM AccountingTransactionsDetail AS a
  24. LEFT JOIN AccountingTransactionsDetail AS fact
  25.     ON fact.JournalNumber = a.JournalPayedDocument
  26.     AND fact.YearOfTransaction = a.YearPayedDocument
  27.     AND fact.NumberOfTransaction = a.NumberPayedDocument
  28.     AND fact.LineNumber = a.LineNbrPayedDocument
  29. LEFT JOIN CompanyRelations AS c
  30.     ON c.NumberRelation = a.NumberRelation
  31. WHERE a.YearOfTransaction = 0
  32.     AND a.NumberOfTransaction = 0
  33.     AND a.PaymentDocument = @order
  34.     AND a.JournalNumber = @journal
  35.     AND a.TypeOfJournal != 21
  36. ORDER BY a.LineNumber;

Unfortunately that doesn't seem too easy to do.

I've tried different approaches such as:

  1. await (from a in _context.AccountingTransactionsDetail
  2.                               from fact in _context.AccountingTransactionsDetail.Where(f =&gt; f.JournalNumber == a.JournalPayedDocument &amp;&amp;
  3.                               f.YearOfTransaction == a.YearPayedDocument &amp;&amp; f.NumberOfTransaction == a.NumberPayedDocument &amp;&amp; f.LineNumber == a.LineNbrPayedDocument).DefaultIfEmpty()
  4.                               from c in _context.CompanyRelations.LeftJoin(c =&gt; c.NumberRelation == a.NumberRelation)
  5.                               where a.YearOfTransaction == 0 &amp;&amp; a.NumberOfTransaction == 0 &amp;&amp; a.PaymentDocument == order
  6.                               &amp;&amp; a.JournalNumber == journal &amp;&amp; a.TypeOfJournal != 21
  7.                               orderby a.LineNumber
  8.                               select new InvoiceResponseModel
  9.                               {
  10.                                   NumberRelation = a.NumberRelation,
  11.                                   NumberOfTransaction = a.NumberOfTransaction,
  12.                                   YearOfTransaction = a.YearOfTransaction,
  13.                                   DateOfTransaction = a.DateOfTransaction,
  14.                                   LineNumber = a.LineNumber,
  15.                                   JournalNumber = a.JournalNumber,
  16.                                   CodeDC = a.CodeDC,
  17.                                   TransactAmountHomeCurrency = a.TransactAmountHomeCurrency,
  18.                                   TransactAmountForeignCurrency = a.TransactAmountForeignCurrency,
  19.                                   PaymentCirculationHomeCurrency = a.PaymentCirculationHomeCurrency,
  20.                                   PaymentCirculationForeignCurrency = a.PaymentCirculationHomeCurrency,
  21.                                   PayedHomeCurrency = a.PayedHomeCurrency,
  22.                                   PayedForeignCurrency = a.PayedForeignCurrency,
  23.                                   AccountNumber = a.AccountNumber,
  24.                                   BalanceForeignCurrency = a.BalanceForeignCurrency,
  25.                                   BalanceHomeCurrency = a.BalanceHomeCurrency,
  26.                                   WayOfPayment = a.WayOfPayment,
  27.                                   PaymentDocument = a.PaymentDocument,
  28.                                   ExpiryDate = a.ExpiryDate,
  29.                                   JournalPayedDocument = a.JournalPayedDocument,
  30.                                   YearPayedDocument = a.YearPayedDocument,
  31.                                   NumberPayedDocument = a.NumberPayedDocument
  32.                               }).ToListAsync();

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

  1. return await _context.AccountingTransactionsDetail
  2.                     .GroupJoin&lt;AccountingTransactionDetail, (int JournalPayedDocument, int YearPayedDocument, int NumberPayedDocument, int LineNbrPayedDocument), (int JournalNumber, int YearOfTransaction, int NumberOfTransaction, int LineNumber), (AccountingTransactionDetail a, IEnumerable&lt;AccountingTransactionDetail&gt; fact)&gt;(
  3.                         _context.AccountingTransactionsDetail,
  4.                         a =&gt; new { a.JournalPayedDocument, a.YearPayedDocument, a.NumberPayedDocument, a.LineNbrPayedDocument },
  5.                         fact =&gt; new { fact.JournalNumber, fact.YearOfTransaction, fact.NumberOfTransaction, fact.LineNumber },
  6.                         (a, fact) =&gt; new { a, fact })
  7.                     .SelectMany(temp0 =&gt; temp0.fact.DefaultIfEmpty(), (temp0, fact) =&gt; new { temp0.a, fact })
  8.                     .GroupJoin(
  9.                         _context.CompanyRelations,
  10.                         temp1 =&gt; temp1.a.NumberRelation,
  11.                         c =&gt; c.NumberRelation,
  12.                         (temp1, c) =&gt; new { temp1.a, temp1.fact, c })
  13.                     .SelectMany(temp2 =&gt; temp2.c.DefaultIfEmpty(), (temp2, c) =&gt; new { temp2.a, temp2.fact, c })
  14.                     .Where(temp3 =&gt; temp3.a.YearOfTransaction == 0 &amp;&amp; temp3.a.NumberOfTransaction == 0 &amp;&amp; temp3.a.PaymentDocument == order &amp;&amp; temp3.a.JournalNumber == journal &amp;&amp; temp3.a.TypeOfJournal != 21)
  15.                     .OrderBy(temp4 =&gt; temp4.a.LineNumber)
  16.                     .Select(temp5 =&gt; new InvoiceResponseModel
  17.                     {
  18.                         NumberRelation = temp5.a.NumberRelation,
  19.                         NumberOfTransaction = temp5.a.NumberOfTransaction,
  20.                         YearOfTransaction = temp5.a.YearOfTransaction,
  21.                         DateOfTransaction = temp5.a.DateOfTransaction,
  22.                         LineNumber = temp5.a.LineNumber,
  23.                         JournalNumber = temp5.a.JournalNumber,
  24.                         CodeDC = temp5.a.CodeDC,
  25.                         TransactAmountHomeCurrency = temp5.a.TransactAmountHomeCurrency,
  26.                         TransactAmountForeignCurrency = temp5.a.TransactAmountForeignCurrency,
  27.                         PaymentCirculationHomeCurrency = temp5.a.PaymentCirculationHomeCurrency,
  28.                         PaymentCirculationForeignCurrency = temp5.a.PaymentCirculationHomeCurrency,
  29.                         PayedHomeCurrency = temp5.a.PayedHomeCurrency,
  30.                         PayedForeignCurrency = temp5.a.PayedForeignCurrency,
  31.                         AccountNumber = temp5.a.AccountNumber,
  32.                         BalanceForeignCurrency = temp5.a.BalanceForeignCurrency,
  33.                         BalanceHomeCurrency = temp5.a.BalanceHomeCurrency,
  34.                         WayOfPayment = temp5.a.WayOfPayment,
  35.                         PaymentDocument = temp5.a.PaymentDocument,
  36.                         ExpiryDate = temp5.a.ExpiryDate,
  37.                         JournalPayedDocument = temp5.a.JournalPayedDocument,
  38.                         YearPayedDocument = temp5.a.YearPayedDocument,
  39.                         NumberPayedDocument = temp5.a.NumberPayedDocument
  40.                     }).ToListAsync();

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

答案1

得分: 1

在LINQ中使用左连接的想法是有效的，因为复制整个结构会浪费时间。另外，为什么要使用分组呢？只需进行一次连接，然后在查询中添加另一个连接。

以下是一个示例，演示如何联接两个表格：

  1. context.TABLE1
  2.     .Join(context.TABLE2,
  3.         e => e.ID,
  4.         m => m.ID,
  5.         (e, m) => new { TABLE1 = e, TABLE2 = m }
  6.     )
  7.     .Where(w => w.TABLE1.RunWhen <= DateTime.Now)//或者根据需要添加其他条件
  8.     .Select(s => new
  9.     {
  10.         PROPERTY1 = s.TABLE1,
  11.         PROPERTY2 = s.TABLE2
  12.     }).ToList();

希望这可以帮助你。

英文:

since it would be a wait of time to copy the full structure. there the idea how to use left join in LINQ
also, why you using Grouping ? just make one join , than add another one to query
What's a good way to perform a Left join using LINQ?

and to give you a sample example of 2 tables join:

  1. context.TABLE1
  2. 	.Join(context.TABLE2,
  3. 			e =&gt; e.ID,
  4. 			m =&gt; m.ID,
  5. 			(e, m) =&gt; new { TABLE1 = e, TABLE2 = m }
  6. 		 )
  7. 	.Where(w =&gt; w.TABLE1.RunWhen &lt;= DateTime.Now)//or some where conditions if you need
  8. 	.Select(s =&gt; new
  9. 	{
  10. 		PROPERTY1 = s.TABLE1,
  11. 		PROPERTY2 = s.TABLE2
  12. 	}).ToList();
  13.

答案2

得分: 1

我刚刚审查了您的帮助，我只是做了显而易见的事情，像这样连续进行了两次左连接：

  1. return await (from a in _context.AccountingTransactionsDetail 
  2. from fact in _context.AccountingTransactionsDetail.Where(f =&gt; f.JournalNumber == a.JournalPayedDocument &amp;&amp;
  3. f.YearOfTransaction == a.YearPayedDocument &amp;&amp; f.NumberOfTransaction == a.NumberPayedDocument &amp;&amp; f.LineNumber == a.LineNbrPayedDocument).DefaultIfEmpty()
  4. from c in _context.CompanyRelations.Where(c =&gt; c.NumberRelation == a.NumberRelation).DefaultIfEmpty()
  5. where a.YearOfTransaction == 0 &amp;&amp; a.NumberOfTransaction == 0 &amp;&amp; a.PaymentDocument == order &amp;&amp; a.JournalNumber == journal &amp;&amp; a.TypeOfJournal != 21
  6. orderby a.LineNumber
  7. select new InvoiceResponseModel
  8. {
  9.      NumberRelation = a.NumberRelation,
  10.      NumberOfTransaction = a.NumberOfTransaction,
  11.      YearOfTransaction = a.YearOfTransaction,
  12.      DateOfTransaction = a.DateOfTransaction,
  13.      LineNumber = a.LineNumber,
  14.      JournalNumber = a.JournalNumber,
  15.      CodeDC = a.CodeDC,
  16.      TransactAmountHomeCurrency = a.TransactAmountHomeCurrency,
  17.      TransactAmountForeignCurrency = a.TransactAmountForeignCurrency,
  18.      PaymentCirculationHomeCurrency = a.PaymentCirculationHomeCurrency,
  19.      PaymentCirculationForeignCurrency = a.PaymentCirculationHomeCurrency,
  20.      PayedHomeCurrency = a.PayedHomeCurrency,
  21.      PayedForeignCurrency = a.PayedForeignCurrency,
  22.      AccountNumber = a.AccountNumber,
  23.      BalanceForeignCurrency = a.BalanceForeignCurrency,
  24.      BalanceHomeCurrency = a.BalanceHomeCurrency,
  25.      WayOfPayment = a.WayOfPayment,
  26.      PaymentDocument = a.PaymentDocument,
  27.      ExpiryDate = a.ExpiryDate,
  28.      JournalPayedDocument = a.JournalPayedDocument,
  29.      YearPayedDocument = a.YearPayedDocument,
  30.      NumberPayedDocument = a.NumberPayedDocument
  31. }).ToListAsync();

这应该给我与上面的SQL查询相同的结果。

英文:

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

  1. return await (from a in _context.AccountingTransactionsDetail 
  2. from fact in _context.AccountingTransactionsDetail.Where(f =&gt; f.JournalNumber == a.JournalPayedDocument &amp;&amp;
  3. f.YearOfTransaction == a.YearPayedDocument &amp;&amp; f.NumberOfTransaction == a.NumberPayedDocument &amp;&amp; f.LineNumber == a.LineNbrPayedDocument).DefaultIfEmpty()
  4. from c in _context.CompanyRelations.Where(c =&gt; c.NumberRelation == a.NumberRelation).DefaultIfEmpty()
  5. where a.YearOfTransaction == 0 &amp;&amp; a.NumberOfTransaction == 0 &amp;&amp; a.PaymentDocument == order &amp;&amp; a.JournalNumber == journal &amp;&amp; a.TypeOfJournal != 21
  6. orderby a.LineNumber
  7. select new InvoiceResponseModel
  8. {
  9.      NumberRelation = a.NumberRelation,
  10.      NumberOfTransaction = a.NumberOfTransaction,
  11.      YearOfTransaction = a.YearOfTransaction,
  12.      DateOfTransaction = a.DateOfTransaction,
  13.      LineNumber = a.LineNumber,
  14.      JournalNumber = a.JournalNumber,
  15.      CodeDC = a.CodeDC,
  16.      TransactAmountHomeCurrency = a.TransactAmountHomeCurrency,
  17.      TransactAmountForeignCurrency = a.TransactAmountForeignCurrency,
  18.      PaymentCirculationHomeCurrency = a.PaymentCirculationHomeCurrency,
  19.      PaymentCirculationForeignCurrency = a.PaymentCirculationHomeCurrency,
  20.      PayedHomeCurrency = a.PayedHomeCurrency,
  21.      PayedForeignCurrency = a.PayedForeignCurrency,
  22.      AccountNumber = a.AccountNumber,
  23.      BalanceForeignCurrency = a.BalanceForeignCurrency,
  24.      BalanceHomeCurrency = a.BalanceHomeCurrency,
  25.      WayOfPayment = a.WayOfPayment,
  26.      PaymentDocument = a.PaymentDocument,
  27.      ExpiryDate = a.ExpiryDate,
  28.      JournalPayedDocument = a.JournalPayedDocument,
  29.      YearPayedDocument = a.YearPayedDocument,
  30.      NumberPayedDocument = a.NumberPayedDocument
  31. }).ToListAsync();

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

huangapple
  • 本文由 发表于 2023年6月29日 23:13:08
  • 转载请务必保留本文链接：https://go.coder-hub.com/76582388.html
  • .net-7.0
  • entity-framework-core
  • left-join
  • linq
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定