LINQ INNER JOIN中的条件组合键 [英] LINQ Conditional Composite key in INNER JOIN
问题描述
假设我有一个Order和OrderDetails集合.如何用LINQ(查询或流利的语法)编写以下sql?
Let's say I have an Order and OrderDetails collections. How can I write following sql in LINQ (query or fluent syntax)?
选择前1个OD.ProductId
来自订单O
OD.OrderID = 1上的内部联接OrderDetail OD
和OD.OrderId = O.OrderId
和((OD.OrderDate = O.OrderDate)或(OD.OrderDate为空))
其中O.CustomerId = 2
通过OD.OrderDate desc排序
select top 1 OD.ProductId
from Order O
inner join OrderDetail OD on OD.OrderID = 1
and OD.OrderId = O.OrderId
and ((OD.OrderDate = O.OrderDate) or (OD.OrderDate is null))
where O.CustomerId = 2
order by OD.OrderDate desc
我知道我可以创建一个匿名类型,其中包含与联接匹配的所有列,但是如上所述,如何在 BOLD
I know that I can create an anonymous type containing all the columns to match for join however how can I write conditional logic for inner join as mentioned above in BOLD
推荐答案
您的查询将给出与以下结果相同的结果:
Your query will give the same results as:
select top 1 OD.ProductId
from Order O
inner join OrderDetail OD
on OD.OrderId = O.OrderId
where O.CustomerId = 2
and OD.OrderID=1
and (OD.OrderDate is null or OD.OrderDate=O.OrderDate)
order by OD.OrderDate desc
您应该能够轻松地将其转换为LINQ.
You should be able to convert that to LINQ much easier.
var results=db.OrderDetail
.Where(od=>od.Order.CustomerId==2)
.Where(od=>od.OrderId==1)
.Where(od=>od.OrderDate==null || od.OrderDate==od.Order.OrderDate)
.OrderBy(od=>od.OrderDate)
.Select(od=>od.ProductId)
.First();
您可以进一步简化为:
var results=db.OrderDetail
.Where(od=>od.Order.CustomerId==2 &&
(od.OrderId==1) &&
(od.OrderDate==null || od.OrderDate==od.Order.OrderDate))
.OrderBy(od=>od.OrderDate)
.Select(od=>od.ProductId)
.First();
这篇关于LINQ INNER JOIN中的条件组合键的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!