gpt4 book ai didi

c# - 具有多个子查询的 Linq 查询

转载 作者:太空狗 更新时间:2023-10-30 00:37:48 25 4
gpt4 key购买 nike

我正在将 Oracle Sql 查询转换为 Linq,但不确定如何继续。这是 Sql 查询:

SELECT *
FROM CustomerShip,
(SELECT DISTINCT b.ShipSeq AS shipSeq
FROM Orders a,
CustomerShip b
WHERE a.OrderId IN (SELECT OrderId
FROM Orders
WHERE CustomerId = @CustomerId
AND OrderType <> 'A')
AND b.CustomerId = @CustomerId
AND b.ShipSeq = a.CustShip
AND OrderStatus <> 'C'
GROUP BY b.ShipSeq) i
WHERE CustomerId = @CustomerId
AND (Address NOT LIKE '%RETAIL%STORE%')
AND ShipSeq = i.ShipSeq(+)
ORDER BY ShipTo DESC, OrderDate DESC;

在转换为 linq 时,我试图将其分解为三个单独的查询。
var query1 = from c in CustomerShip
where c.CustomerId == customerId
&& !c.Address.Contains("RETAIL")
&& !c.Address.Contains("STORE")
orderby c.ShipTo descending, c.OrderDate descending
select c;

var query2 = from o in Orders
where o.CustomerId == customerId
&& !o.OrderType.Equals("A")
select o.OrderId;

var query3 = (from o in Orders
from c in CustomerShip
where c.CustomerId == customerId
&& c.ShipSeq == o.CustShip
&& !o.OrderStatus.Equals("A")
select c.ShipSeq).Distinct();

现在我试图将它们全部组合成一个查询,但不确定如何去做。这是我要去的方向:
var query = from c in CustomerShip

let subquery = from o in Orders
where o.CustomerId == customerId
&& !o.OrderType.Equals("A")
select o.OrderId

from or in model.Orders
where subquery.Contains(or.OrderId)
&& c.CustomerId == customerId
&& c.ShipSeq == or.CustShip
&& !or.OrderStatus.Equals("A")
group c by c.ShipSeq
into i
select c.ShipSeq

where c.CustomerId == customerId
&& !c.Address.Contains("RETAIL")
&& !c.Address.Contains("STORE")
orderby c.ShipTo descending, c.OrderDate descending
select c, i;

更新

我有一个类型有效的查询,但它需要将近两分钟来执行(与 Oracle 查询的 0.02s 相比)并且结果的顺序不正确。有人看到我缺少什么吗?
var innerQuery = from x in model.Orders
where x.CustomerId == customerId
&& !x.OrderType.Equals("A")
select x.OrderId;

var result = from c in model.CustomerShip
join subQuery in
(
(from o in model.Orders
from c in model.CustomerShip
where c.CustomerId == customerId
&& innerQuery.Contains(o.OrderId)
&& !o.FLAG_ORD_STATUS.Equals("C")
&& c.ShipSeq == o.CustShip
select c.ShipSeq).Distinct()

) on c.ShipSeq equals subQuery into temp
from x in temp.DefaultIfEmpty()
where c.CustomerId == customerId
&& !c.Address.Contains("RETAIL")
&& !c.Address.Contains("STORE")
orderby c.ShipTo descending, c.OrderDate descending
select c;

最佳答案

请记住,您只是在此处构建查询。在您执行 ToList() 之前,不会执行任何操作或 .FirstOrDefault()管他呢。 SO,您可以在其他查​​询中使用这些查询,并且在执行时会创建一个大 SQL 语句。

var query2 = from o in Orders
where o.CustomerId == customerId
&& !o.OrderType.Equals("A")
select o.OrderId;

var query3 = (from o in Orders
join c in CustomerShip on o.CustShip equals c.ShipSeq
where c.CustomerId == customerId
&& !o.OrderStatus.Equals("A")
&& query2.Contains(o.OrderId)
select c.ShipSeq).Distinct();

var query1 = from c in CustomerShip
from i in query3
where c.CustomerId == customerId
&& !c.Address.Contains("RETAIL")
&& !c.Address.Contains("STORE")
&& c.ShipSeq == i.ShipSeq
orderby c.ShipTo descending, c.OrderDate descending
select c;

但是,我很确定您可以将 query2 和 query3 减少到仅:
var query3 = (from o in Orders
join c in CustomerShip on o.CustShip equals c.ShipSeq
where c.CustomerId == customerId
&& !o.OrderStatus.Equals("A")
&& !o.OrderType.Equals("A")
select c.ShipSeq).Distinct();

关于c# - 具有多个子查询的 Linq 查询,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/44437022/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com