LINQtoSQL 在 1 对 1 关系上左联接,基于方法
本文关键字:于方法 方法 关系 LINQtoSQL | 更新日期: 2023-09-27 18:35:32
假设我在 1 到 1 或 0 关系上有 2 个表,如下所示:
Invoice: ID, Date, OrderID
Order: ID, Date, Notes (string)
就LINQtoSQL而言,我有INNER JOIN为:
customer
.invoices
.OrderByDescending(i => i.Date)
.Join(context.Orders,
invoice => invoice.OrderID,
order => order.OrderID,
(invoice, order) => new
{
invoice = invoice,
OrderNotes = order.Notes
})
.ToList()
内部连接中排除的所有值都是WHERE Invoice.OrderID NOT IN (SELECT OrderID FROM Order)
,我没有检查会发生什么WHERE Invoice.OrderID IS NULL
所需的查询是:
SELECT i.*, o.Notes AS OrderNotes
FROM
Invoice AS i
LEFT OUTER JOIN
Order AS o
ON i.OrderID = o.OrderID
我知道 FK 应该切换,但我没有权力改变数据库的结构。
我不知道如何产生左外连接效果。 环顾四周,我只找到了 1 到多个左连接的解决方案。
感谢所有建议,我已经弄清楚了如何在基于方法的语法中做到这一点。
customer
.invoices
.OrderByDescending(i => i.Date)
.GroupJoin(context.Orders,
invoice => invoice.OrderID,
order => order.OrderID,
(invoice, orders) => new
{
invoice = invoice,
OrderNotes = orders.SingleOrDefault()==null?"":orders.SingleOrDefault().Notes
})
.ToList()
关键是组加入,即使它看起来像 1 对多关系 JOIN,它的工作方式与 SQL 一样
msdn 的优秀示例
http://msdn.microsoft.com/en-us/library/bb397895.aspx
应该是这样的
from i in invoice
join o in order on i.OrderID = o.OrderID into result
from r in result.DefaultIfEmpty()
select new {i.Id, i.someProperty , Notes = (r== null ? String.Empty : r.Notes) };
试试这个:
var query = from invoice in context.Invoices
join order in context.Orders on new { invoice.OrderId } equals new { order.Id } into groupedJoin
from grOrder in groupedJoin.DefaultIfEmpty()
select new { Invoice = invoice, Order = grOrder };