代码之家 › 专栏 › 技术社区 › Justin Grant

强制LINQ to Entities+SQL Server 2014有条件地运行子查询

sql-server-2014 linq-to-entities linq sql-server c#

Justin Grant · 技术社区 · 9 年前

我想要的是类似于伪SQL的SQL:

SELECT CASE WHEN condition THEN (subquery SQL) ELSE NULL END

CASE WHEN condition THEN sub.results ELSE NULL END
.... (more SQL here)
OUTER APPLY (
    subquery SQL
) sub

下面是C#中子查询的简化版本:

let lastSale = storeReportSettings.ShowRunningTotal
                    ? salesTable
                        .Where(arg =>
                            arg.StoreId == Stores.StoreId &&
                            arg.SaleDate <= endDate)
                        .OrderByDescending(arg => arg.SaleDate)
                        .Select(arg => arg.RunningTotal)
                        .FirstOrDefault()
                    : (int?)null

在上面的例子中, reportSettings.ShowRunningTotal

因此,目标是避免运行该子查询,但需要它的行除外。

SELECT
... (lots of SQL here) ...

    CASE WHEN ([Filter1].[ShowRunningTotal] = 1) THEN [Limit1].[RunningTotal] END AS [C1], 

... (lots more SQL here) ...

    OUTER APPLY  (SELECT TOP (1) [Project1].[RunningTotal] AS [RunningTotal]
        FROM ( SELECT 
            [Extent13].[RunningTotal] AS [RunningTotal], 
            [Extent13].[SaleDate] AS [SaleDate]
            FROM [dbo].[Sales] AS [Extent13]
            WHERE ([Extent13].[StoreID] = [Filter1].[StoreId1]) 
                AND ([Extent13].[SaleDate] <= @p__linq__1) 
        )  AS [Project1]
        ORDER BY [Project1].[SaleDate] DESC ) AS [Limit1]

我想要的是这样的SQL:

OUTER APPLY  (
     SELECT CASE WHEN ([Filter1].[ShowRunningTotal] = 0) THEN NULL ELSE
     (
         SELECT TOP (1) [Project1].[RunningTotal] AS [RunningTotal]
            FROM ( SELECT 
                [Extent13].[RunningTotal] AS [RunningTotal], 
                [Extent13].[SaleDate] AS [SaleDate]
                FROM [dbo].[Sales] AS [Extent13]
                WHERE ([Extent13].[StoreID] = [Filter1].[StoreId1])
                    AND ([Extent13].[SaleDate] <= @p__linq__1) 
            )  AS [Project1]
            ORDER BY [Project1].[SaleDate] DESC 
    ) END AS [RunningTotal]
) AS [Limit1]

我如何更改我的LINQ查询以发出如上所述的SQL,或者任何其他在条件为false时总是避免运行子查询的SQL?

显然,我可以将其分为两个LINQ查询——一个用于需要运行总计的行,另一个用于其余行——并合并结果。但这涉及到很多我宁愿避免的重构。

Where() 相反但是,尽管这种方法在其他情况下可能有用,但在这里没有帮助,因为SQL Server仍然在该子查询所依赖的覆盖索引上运行低效的索引查找。(这是 ON (SaleDate, StoreId) INCLUDE (RunningTotal) predicate pushdown 不足以避免索引搜索。不幸的是,我无法添加更好的覆盖索引——由于各种原因,底层表的索引不容易修改。

我可以将子查询从let(又名OUTER APPLY)转换为left-OUTER连接,但这将涉及相当复杂的重构,我担心这可能会导致不太可预测的查询计划,在某些情况下可能会产生更差的性能。

1 回复 | 直到 9 年前

David Browne - Microsoft 9 年前

只需在子查询中嵌入条件,使其在ShowRunningTotal=false的情况下短路。如

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity;
using System.Data.SqlClient;
using System.Linq;
using System.Threading;

namespace ConsoleApp6
{


    [Table("Customers")]
    public class Customer
    {
        public int CustomerID { get; set; }

        public string Name { get; set; }

        public bool ShowRunningTotal { get; set; }

        public ICollection<SalesOrders> Orders { get; } = new HashSet<SalesOrders>();
    }

    public class SalesOrders
    {
        public int Id { get; set; }
        public float Amount { get; set; }

        public DateTime SaleDate { get; set; }

        public int CustomerId { get; set; }
        virtual public Customer Customer { get; set; }
    }
    class Db : DbContext
    {

        public DbSet<Customer> Customers { get; set; }

        public DbSet<SalesOrders> SalesOrders { get; set; }
    }
    class Program
    {
        static void Main()
        {

            Database.SetInitializer(new DropCreateDatabaseAlways<Db>());

            using (var db = new Db())
            {
                var q = from c in db.Customers
                        select new
                        {
                            c.CustomerID,
                            LastSale = c.Orders
                                        .Where(o => c.ShowRunningTotal)
                                        .OrderByDescending(o => o.SaleDate)
                                        .FirstOrDefault()
                        };

                Console.WriteLine(q.ToString());
            }

            Console.ReadKey();
        }


    }
}

SELECT
    [Extent1].[CustomerID] AS [CustomerID],
    [Limit1].[Id] AS [Id],
    [Limit1].[Amount] AS [Amount],
    [Limit1].[SaleDate] AS [SaleDate],
    [Limit1].[CustomerId] AS [CustomerID1]
    FROM  [dbo].[Customers] AS [Extent1]
    OUTER APPLY  (SELECT TOP (1) [Project1].[Id] AS [Id], [Project1].[Amount] AS [Amount], [Project1].[SaleDate] AS [SaleDate], [Project1].[CustomerId] AS [CustomerId]
        FROM ( SELECT
            [Extent2].[Id] AS [Id],
            [Extent2].[Amount] AS [Amount],
            [Extent2].[SaleDate] AS [SaleDate],
            [Extent2].[CustomerId] AS [CustomerId]
            FROM [dbo].[SalesOrders] AS [Extent2]
            WHERE ([Extent1].[CustomerID] = [Extent2].[CustomerId]) AND ([Extent1].[ShowRunningTotal] = 1)
        )  AS [Project1]
        ORDER BY [Project1].[SaleDate] DESC ) AS [Limit1]