如何在 MVC 中使用 LINQ Lambda 连接三个表并将其放入 ViewModel ASP.Net?

Question

我在尝试连接三个表数据并将其放入视图模型中时遇到问题。

这是我的代码：

var idSearchJoin = payoutdb.payout_transaction    // your starting point - table in the "from" statement
.Join(payoutdb.payout_remittance, // the source table of the inner join
transaction => transaction.transid, // Select the primary key (the first part of the "on" clause in an sql "join" statement)
remit => remit.transid,   // Select the foreign key (the second part of the "on" clause)
(transaction, remit) => new TransactionReport() // selection
{
transid = transaction.transid,
senderRefId = transaction.senderRefId,
senderName = transaction.senderName,
senderAddr = transaction.senderAddr,
batchid = transaction.batchid,
requestDate = transaction.requestDate,
benefName = transaction.benefName,
benefacctno = transaction.benefacctno,
amount = remit.amount,
purpose = remit.purpose,
status = transaction.status
})
.Join(payoutdb.payout_bank,
remit => remit.receivingbank,
bank => bank.bankid,
(remit,bank) => new TransactionReport()
{
receivingbank = bank.name
})
.Where(transactremit => transactremit.senderRefId == searchTxt).ToList();

但是此代码生成并错误An exception of type 'System.NotSupportedException' occurred in EntityFramework.SqlServer.dll but was not handled in user code附加信息The specified type member 'receivingbank' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported给我一个问题的提示，但我仍然无法解决它。仅当我删除这部分代码时，此代码才会运行：

.Join(payoutdb.payout_bank,
remit => remit.receivingbank,
bank => bank.bankid,
(remit,bank) => new TransactionReport()
{
receivingbank = bank.name
})

但是我需要这部分代码，以便我获得从payout_transaction到payout_bank表上的bankid的等效receivingbank。这是我的 sql 代码：

SELECT a.transid,c.name
FROM payout_transaction a
INNER JOIN 
payout_remittance b
ON a.transid = b.transid
INNER JOIN
payout_bank c
ON b.receivingbank = c.bankid
WHERE senderRefId =  searchtxt;

但是我不知道如何在 LINQ 上执行此操作 Lambda.Do 您对如何解决此问题有任何建议或任何解决方法？

Answer 1

假设您在模型中正确定义了关系，则可以在 EF 方法语法中选择联接的结果集，如下所示，而无需使用任何 Join 语句：

var idSearchJoin = DBContext.payout_transactionDBSet                        
.Include(i => i.payout_remittanceNavigationProp)                                     
.Include(p => p.payout_bankNavigationProp)                                 
.Where(transactremit => transactremit.senderRefId == searchTxt)              
.Select(s => new TransactionReport() 
{
transid = s.transid,
senderRefId = s.senderRefId,
senderName = s.senderName,
senderAddr = s.senderAddr,
batchid = s.batchid,
requestDate = s.requestDate,
benefName = s.benefName,
benefacctno = s.benefacctno,
amount = s.payout_remittanceNavigationProp.amount,
purpose = s.payout_remittanceNavigationProp.purpose,
status = s.status,
receivingbank = s.payout_bankNavigationProp.name
}).ToList();