Получите максимум и минимум из Entity Framework за один запрос и с наилучшим возможным запросом

Question

Мне известен этот вопрос, но я хотел бы получить что-то близкое к этому сгенерированному SQL:

select MAX(Column), MIN(Column) from Table WHERE Id = 1

Когда я пытаюсь это:

var query = from d in db.Table
            where d.Id == 1
            select new
            {
                min = db.Table.Max(s => s.Column),
                max = db.Table.Min(s => s.Column)
            };

Сгенерированный sql выглядит так:

SELECT 
    [Extent1].[Id] AS [Id], 
    [GroupBy1].[A1] AS [C1], 
    [GroupBy2].[A1] AS [C2]
    FROM   [dbo].[Table] AS [Extent1]
    CROSS JOIN  (SELECT 
        MAX([Extent2].[Column]) AS [A1]
        FROM [dbo].[Table] AS [Extent2] ) AS [GroupBy1]
    CROSS JOIN  (SELECT 
        MIN([Extent3].[Column]) AS [A1]
        FROM [dbo].[Table] AS [Extent3] ) AS [GroupBy2]
    WHERE ([Extent1].[Id] = 1) AND (1 IS NOT NULL)

Я также попробовал это:

var query = from d in db.Table
           where d.Id == 1
           group d by d.Id into grp
           let min = grp.Min(s => s.Column)
           let max = grp.Max(s => s.Column)
           select new { min, max };

Что дает это:

SELECT 
    [Project2].[Id] AS [Id], 
    [Project2].[C1] AS [C1], 
    [Project2].[C2] AS [C2]
    FROM ( SELECT 
        [Project1].[C1] AS [C1], 
        [Project1].[Id] AS [Id], 
        (SELECT 
            MAX([Extent2].[Column]) AS [A1]
            FROM [dbo].[Table] AS [Extent2]
            WHERE ([Extent2].[Id] = 1) AND (1 IS NOT NULL) AND ([Project1].[Id] = [Extent2].[Id])) AS [C2]
        FROM ( SELECT 
            [GroupBy1].[A1] AS [C1], 
            [GroupBy1].[K1] AS [Id]
            FROM ( SELECT 
                [Extent1].[Id] AS [K1], 
                MIN([Extent1].[Column]) AS [A1]
                FROM [dbo].[Table] AS [Extent1]
                WHERE ([Extent1].[Id] = 16) AND (16 IS NOT NULL)
                GROUP BY [Extent1].[Id]
            )  AS [GroupBy1]
        )  AS [Project1]
    )  AS [Project2]

Они оба работают, и снижение производительности, вероятно, незначительно, так что это в основном эстетично:
Два сгенерированных запроса ранили мне глаза.

Answer 1

Попробуйте удалить let Заявления - ниже дает ожидаемые результаты:

var q = from d in db.Table
        where d.Id == 1
        group d by d.Id into g
        select new
        {
            Id = g.Key, // shown for illustrative purposes
            ColumnMin = g.Min( gi => gi.Column ),
            ColumnMax = g.Max( gi => gi.Column )
        };

var result = q.SingleOrDefault();

Результирующий SQL:

SELECT 
    [GroupBy1].[K1] AS [Id],
    [GroupBy1].[A1] AS [C1], 
    [GroupBy1].[A2] AS [C2]
    FROM ( SELECT 
        [Extent1].[Id] AS [K1], 
        MIN([Extent1].[Column]) AS [A1], 
        MAX([Extent1].[Column]) AS [A2]
        FROM [dbo].[Table] AS [Extent1]
        WHERE 1 = [Extent1].[Id]
        GROUP BY [Extent1].[Id]
    )  AS [GroupBy1]

Answer 2

Вот и мы, 8 лет спустя! Но все же ответ!

      db.Table
  .Where(d => d.Id == 1)
  .GroupBy(_ => 1,
    (_, records) => new
                    {
                        Max = records.Max(r => r.Column),
                        Min = records.Min(r => r.Column)
                     });

Это приводит к:

      SELECT MAX([o].[Column]) AS [Max], MIN([o].[Column]) AS [Min]
FROM [Table] AS [o]
WHERE [o].[Id] = 1
GO

Обратите внимание, что в «Группировать по 1 » 1 не имеет ничего общего с идентификатором из запроса!

Answer 3

var query = from d in db.Table
            where d.Id == 1
            select
            {     
                d.Max(t =>t.yourColName),  
                d.Min(t =>t.yourColName)  
            };