logo标签列表

使用多个子查询的Linq查询

c#linq
5

我正在尝试将 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.02秒），而且结果的顺序不正确。有人看到我错过了什么吗？

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;
1
阅读有关 Linq 的 Join 能力的内容... 您可以将其重写为看起来非常类似于您的 SQL，不要将其拆分。 - JWP
我正在努力理解这个问题，但是我对这一行感到困惑：AND ShipSeq = i.ShipSeq(+)。这是Oracle特有的吗？ - PixelPaul
@PixelPaul (+) 是 Oracle 表示外连接的旧方式。这里的意思是“所有客户记录，加上与 ShipSeq 匹配的内部查询记录。详情请见：https://dev59.com/NWw15IYBdhLWcg3whMI1”。 - James Curran
1
这是一个回答，所以我只会发表评论：不行。Linq的范围是查询.NET对象，而不是查询数据库。每个人都有自己的方法：将查询放入DB的视图中，然后使用EF或任何ORM与其交互。 Linq永远不会产生最佳查询，它的执行计划经常会被破坏，DBE将不得不重新编译查询，忘记任何优化等等。千万不要使用Linq来查询DB。（顺便说一句，你还很幸运，我经常不得不修复由使用Linq对DB造成的损害，而我见过的最糟糕的情况是8分钟对0.4秒）。 - motoDrizzt
同意 @motoDrizzt 的观点，您需要创建一个存储过程，然后使用 EF 执行它。实际上，存储过程已经被“编译”、优化并准备好执行。这可能看起来不太美观，但这是实现您所尝试做的事情的更好方式。 - csblo
4个回答
8

记住，你只是在构建一个查询。在执行ToList()或.FirstOrDefault()等操作之前，什么也不会被执行。因此，你可以在其他查询中使用这些查询，并在执行时创建一个大的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;

然而，我非常确定您可以将查询2和查询3缩减为：

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();
1

将query2和query3合并到此处作为内部查询

var Innerquery = (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();

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

或者您可以尝试使用 Linqer http://www.sqltolinq.com。

1
尝试这样做。我仅为了消除错误而建模一些类。如果按ShipSeq分组，则不需要distinct。只需从组中取第一个项目即可获得相同的结果。
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;

namespace ConsoleApplication60
{
    class Program
    {
        static void Main(string[] args)
        {
            int customerID = 1234;
            List<Order> CustomTypeA = Order.orders
                .Where(x => (x.CustomerId == customerID) && (x.OrderType == "A") && (x.OrderStatus == "C")).ToList();

            var results = (from CustA in CustomTypeA 
                          join CustShip in Ship.CustomerShip on CustA.CustomerId equals CustShip.CustomerId 
                          select new { CustA = CustA, CustShip = CustShip})
                          .Where(x => (!RetailStore(x.CustShip.Address)) && (x.CustA.CustShip == x.CustShip.ShipSeq))
                          .OrderByDescending(x => x.CustShip.OrderDate)
                          .GroupBy(x => x.CustShip.ShipSeq)
                          .Select(x => x.FirstOrDefault())
                          .Select(x => new {
                              CustomerID = x.CustShip.CustomerId,
                              Address = x.CustShip.Address,
                              OrderDate = x.CustShip.OrderDate
                          }).ToList();

        }
        static Boolean RetailStore(string address)
        {
            string pattern = "RETAIL.*STORE";
            return Regex.IsMatch(address, pattern);
        }
    }
    public class Order
    {
        public static List<Order> orders = new List<Order>();

        public int CustomerId { get; set; }
        public string OrderType { get; set; }
        public string CustShip { get; set; }
        public string OrderStatus { get; set; } 
    }
    public class Ship
    {
        public static List<Ship> CustomerShip = new List<Ship>();

        public int CustomerId { get; set; }
        public string ShipSeq { get; set; }
        public string Address { get; set; }
        public DateTime OrderDate { get; set; }
     }
}
0

你的EF查询缓慢可能有很多原因 - 我建议使用分析器。

可能的原因是EF创建了一个低效的查询（通常数据库应该创建自己的优化，但我在EF和Oracle方面有过不好的经历），或者根据它加载的结果数量，将其映射到实际对象非常昂贵。

总的来说，虽然在.NET世界中似乎不是一种流行的观点，但我建议在有复杂查询时要么创建视图，要么使用dbcontext.Database.SqlQuery<CustomerShip>(sql)，特别是在使用Oracle时，至少从我以前的经验来看是这样的（已经过去一段时间了，所以我可能是错的）。

网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接