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

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

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:

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 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:

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 =&gt; f.JournalNumber == a.JournalPayedDocument &amp;&amp;
                              f.YearOfTransaction == a.YearPayedDocument &amp;&amp; f.NumberOfTransaction == a.NumberPayedDocument &amp;&amp; f.LineNumber == a.LineNbrPayedDocument).DefaultIfEmpty()
                              from c in _context.CompanyRelations.LeftJoin(c =&gt; c.NumberRelation == a.NumberRelation)
                              where a.YearOfTransaction == 0 &amp;&amp; a.NumberOfTransaction == 0 &amp;&amp; a.PaymentDocument == order
                              &amp;&amp; a.JournalNumber == journal &amp;&amp; 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&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;(
                        _context.AccountingTransactionsDetail,
                        a =&gt; new { a.JournalPayedDocument, a.YearPayedDocument, a.NumberPayedDocument, a.LineNbrPayedDocument },
                        fact =&gt; new { fact.JournalNumber, fact.YearOfTransaction, fact.NumberOfTransaction, fact.LineNumber },
                        (a, fact) =&gt; new { a, fact })
                    .SelectMany(temp0 =&gt; temp0.fact.DefaultIfEmpty(), (temp0, fact) =&gt; new { temp0.a, fact })
                    .GroupJoin(
                        _context.CompanyRelations,
                        temp1 =&gt; temp1.a.NumberRelation,
                        c =&gt; c.NumberRelation,
                        (temp1, c) =&gt; new { temp1.a, temp1.fact, c })
                    .SelectMany(temp2 =&gt; temp2.c.DefaultIfEmpty(), (temp2, c) =&gt; new { temp2.a, temp2.fact, c })
                    .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)
                    .OrderBy(temp4 =&gt; temp4.a.LineNumber)
                    .Select(temp5 =&gt; 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

答案1

得分: 1

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

以下是一个示例,演示如何联接两个表格:

context.TABLE1
    .Join(context.TABLE2,
        e => e.ID,
        m => m.ID,
        (e, m) => new { TABLE1 = e, TABLE2 = m }
    )
    .Where(w => w.TABLE1.RunWhen <= DateTime.Now)//或者根据需要添加其他条件
    .Select(s => new
    {
        PROPERTY1 = s.TABLE1,
        PROPERTY2 = s.TABLE2
    }).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:

context.TABLE1
	.Join(context.TABLE2,
			e =&gt; e.ID,
			m =&gt; m.ID,
			(e, m) =&gt; new { TABLE1 = e, TABLE2 = m }
		 )
	.Where(w =&gt; w.TABLE1.RunWhen &lt;= DateTime.Now)//or some where conditions if you need
	.Select(s =&gt; new
	{
		PROPERTY1 = s.TABLE1,
		PROPERTY2 = s.TABLE2
	}).ToList();
	

答案2

得分: 1

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

return await (from a in _context.AccountingTransactionsDetail 
from fact in _context.AccountingTransactionsDetail.Where(f =&gt; f.JournalNumber == a.JournalPayedDocument &amp;&amp;
f.YearOfTransaction == a.YearPayedDocument &amp;&amp; f.NumberOfTransaction == a.NumberPayedDocument &amp;&amp; f.LineNumber == a.LineNbrPayedDocument).DefaultIfEmpty()
from c in _context.CompanyRelations.Where(c =&gt; c.NumberRelation == a.NumberRelation).DefaultIfEmpty()
where a.YearOfTransaction == 0 &amp;&amp; a.NumberOfTransaction == 0 &amp;&amp; a.PaymentDocument == order &amp;&amp; a.JournalNumber == journal &amp;&amp; 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();

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

英文:

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 =&gt; f.JournalNumber == a.JournalPayedDocument &amp;&amp;
f.YearOfTransaction == a.YearPayedDocument &amp;&amp; f.NumberOfTransaction == a.NumberPayedDocument &amp;&amp; f.LineNumber == a.LineNbrPayedDocument).DefaultIfEmpty()
from c in _context.CompanyRelations.Where(c =&gt; c.NumberRelation == a.NumberRelation).DefaultIfEmpty()
where a.YearOfTransaction == 0 &amp;&amp; a.NumberOfTransaction == 0 &amp;&amp; a.PaymentDocument == order &amp;&amp; a.JournalNumber == journal &amp;&amp; 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.

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

发表评论

匿名网友

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

确定