我如何改进这个Linq查询(一种旋转)

本文关键字：一种 旋转 查询 何改进 Linq | 更新日期: 2023-09-27 18:17:36

我有这个实体:

public  class Delivery
    {
        public int Id { get; set; }
        public int ProductId { get; set; }
        public int CustomerId { get; set; }
        public int Quantity { get; set; }
        public DateTime DeliveryDate { get; set; }
        public virtual Product Product { get; set; }
        public virtual Customer Customer { get; set; }
    }

我想按周显示交货，所以我写这个查询:

    public override IEnumerable GetModelData(ApplicationDbContext context)
            {
                return context.Deliveries.GroupBy(x => x.Product).Select(x => new
                {
                    Id=x.Key.Id,
                    Product = x.Key.Name,
                    Wk1 =(int?) x.Where(a => SqlFunctions.DatePart("wk", a.DeliveryDate) == 1).Sum(a => a.Quantity),
...
...
...
                    Wk46 = (int?)x.Where(a => SqlFunctions.DatePart("wk", a.DeliveryDate) == 46).Sum(a => a.Quantity),
                    Wk47 = (int?)x.Where(a => SqlFunctions.DatePart("wk", a.DeliveryDate) == 47).Sum(a => a.Quantity),
                    Wk48 = (int?)x.Where(a => SqlFunctions.DatePart("wk", a.DeliveryDate) == 48).Sum(a => a.Quantity),
                    Wk49 = (int?)x.Where(a => SqlFunctions.DatePart("wk", a.DeliveryDate) == 49).Sum(a => a.Quantity),
                    Wk50 = (int?)x.Where(a => SqlFunctions.DatePart("wk", a.DeliveryDate) == 50).Sum(a => a.Quantity),
                    Wk51 = (int?)x.Where(a => SqlFunctions.DatePart("wk", a.DeliveryDate) == 51).Sum(a => a.Quantity),
                    Wk52 = (int?)x.Where(a => SqlFunctions.DatePart("wk", a.DeliveryDate) == 52).Sum(a => a.Quantity),
                }).ToList();
            }

是否有可能用更小的查询获得预期的对象?

我在这个时刻大约有100个样本行在交付数据库表，我有填充，这种方式来获得数据不是最好的一个。

查询正在工作，我只想知道你是否有一个更好的方法来编写这种查询。

我如何改进这个Linq查询(一种旋转)

我认为使LINQ查询更短的唯一方法是以编程方式生成选择器。

但是，肯定有一种方法可以使生成的SQL查询更短(并且更高效)。代替不能很好地转换的Where(condition).Sum(expr)结构，使用条件和，即Sum(condition ? expr : null)，它产生更好的SQL:

Wk1 = x.Sum(a => SqlFunctions.DatePart("wk", a.DeliveryDate) == 1 ? a.Quantity : (int?)null),
Wk2 = x.Sum(a => SqlFunctions.DatePart("wk", a.DeliveryDate) == 2 ? a.Quantity : (int?)null),
...

我将在SQL查询中进行分组，但随后在内存中进行旋转。

public static IEnumerable GetModelData(ApplicationDbContext context)
{
    return context.Deliveries
        .GroupBy(x => new { x.Product.Id, x.Product.Name, Week = SqlFunctions.DatePart("wk", x.DeliveryDate) } )
        .Select(x => new
        {
            Id = x.Key.Id,
            Product = x.Key.Name,
            Week = x.Key.Week,
            Quantity = x.Sum(a => a.Quantity),
        })
        .AsEnumerable()
        .GroupBy(x => new { x.Id, x.Product })
        .Select(x => new
        {
            Id = x.Key.Id,
            Product = x.Key.Product,
            Wk1 = x.Sum(a => a.Week == 1 ? a.Quantity : 0),
            Wk2 = x.Sum(a => a.Week == 2 ? a.Quantity : 0),
            Wk51 = x.Sum(a => a.Week == 52 ? a.Quantity : 0),
            Wk52 = x.Sum(a => a.Week == 53 ? a.Quantity : 0),
        })
        .ToList();
}

. asenumerable()上面的所有内容都作为一条SQL语句对数据库执行，下面的所有内容都在内存中执行。

下面是执行的SQL的跟踪。

SELECT
    [GroupBy1].[K1] AS [ProductId],
    [GroupBy1].[K2] AS [Name],
    [GroupBy1].[K3] AS [C1],
    [GroupBy1].[A1] AS [C2]
    FROM ( SELECT
        [Join1].[K1] AS [K1],
        [Join1].[K2] AS [K2],
        [Join1].[K3] AS [K3],
        SUM([Join1].[A1]) AS [A1]
        FROM ( SELECT
            [Extent1].[ProductId] AS [K1],
            [Extent2].[Name] AS [K2],
            DATEPART(wk, [Extent1].[DeliveryDate]) AS [K3],
            [Extent1].[Quantity] AS [A1]
            FROM  [dbo].[Deliveries] AS [Extent1]
            INNER JOIN [dbo].[Products] AS [Extent2] ON [Extent1].[ProductId] = [Extent2].[Id]
        )  AS [Join1]
        GROUP BY [K1], [K2], [K3]
    )  AS [GroupBy1]

如果你删除。asenumerable()，它将在服务器上全部运行。下面是SQL跟踪。

SELECT
    [GroupBy2].[K1] AS [ProductId],
    [GroupBy2].[K2] AS [Name],
    [GroupBy2].[A1] AS [C1],
    [GroupBy2].[A2] AS [C2],
    [GroupBy2].[A3] AS [C3],
    [GroupBy2].[A4] AS [C4]
    FROM ( SELECT
        [GroupBy1].[K1] AS [K1],
        [GroupBy1].[K2] AS [K2],
        SUM([GroupBy1].[A1]) AS [A1],
        SUM([GroupBy1].[A2]) AS [A2],
        SUM([GroupBy1].[A3]) AS [A3],
        SUM([GroupBy1].[A4]) AS [A4]
        FROM ( SELECT
            [GroupBy1].[K1] AS [K1],
            [GroupBy1].[K2] AS [K2],
            CASE WHEN (1 = [GroupBy1].[K3]) THEN [GroupBy1].[A1] ELSE 0 END AS [A1],
            CASE WHEN (2 = [GroupBy1].[K3]) THEN [GroupBy1].[A1] ELSE 0 END AS [A2],
            CASE WHEN (52 = [GroupBy1].[K3]) THEN [GroupBy1].[A1] ELSE 0 END AS [A3],
            CASE WHEN (53 = [GroupBy1].[K3]) THEN [GroupBy1].[A1] ELSE 0 END AS [A4]
            FROM ( SELECT
                [Join1].[K1] AS [K1],
                [Join1].[K2] AS [K2],
                [Join1].[K3] AS [K3],
                SUM([Join1].[A1]) AS [A1]
                FROM ( SELECT
                    [Extent1].[ProductId] AS [K1],
                    [Extent2].[Name] AS [K2],
                    DATEPART(wk, [Extent1].[DeliveryDate]) AS [K3],
                    [Extent1].[Quantity] AS [A1]
                    FROM  [dbo].[Deliveries] AS [Extent1]
                    INNER JOIN [dbo].[Products] AS [Extent2] ON [Extent1].[ProductId] = [Extent2].[Id]
                )  AS [Join1]
                GROUP BY [K1], [K2], [K3]
            )  AS [GroupBy1]
        )  AS [GroupBy1]
        GROUP BY [K1], [K2]
    )  AS [GroupBy2]
相关文章：