英文:
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 => 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
答案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
and to give you a sample example of 2 tables join:
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)//or some where conditions if you need
.Select(s => new
{
PROPERTY1 = s.TABLE1,
PROPERTY2 = s.TABLE2
}).ToList();
答案2
得分: 1
我刚刚审查了您的帮助,我只是做了显而易见的事情,像这样连续进行了两次左连接:
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();
这应该给我与上面的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 => 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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论