Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Get max & min from Entity Framework, in one query and with best query possible

I'm aware of this question, but what I would like to do is obtain something close to this generated SQL:

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

When I try this:

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)
            };

The generated sql looks like this:

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)

I also tried this:

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 };

Which gives this:

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]

They both work, and the performance hit is probably negligible, so it's mostly aesthetic:
The two generated queries both hurt my eyes.

like image 388
Benjol Avatar asked Dec 09 '14 10:12

Benjol


People also ask

How do you get Max in C++?

The max() function in C++ accepts two values and returns the larger one. This function is available in <algorithm. h> . The max() function helps during coding contests when you want to find the maximum of two values in the logic.

What does Max () do in Python?

Definition and Usage. The max() function returns the item with the highest value, or the item with the highest value in an iterable. If the values are strings, an alphabetically comparison is done.

How does Max work in C++?

It compares the two numbers passed in its arguments and returns the larger of the two, and if both are equal, then it returns the first one. It can also compare the two numbers using a binary function, which is pre-defined by the user, and then passed as argument in std::max().


2 Answers

Here we are, 8 years later! But an answer nonetheless!

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

This results in:

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

Note that in "Group by 1" the 1 has nothing to do with the ID from the query!

like image 83
Bastiaan Linders Avatar answered Oct 18 '22 22:10

Bastiaan Linders


Try removing the let statements - the below produces expected results:

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

Resulting 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]
like image 27
Moho Avatar answered Oct 18 '22 22:10

Moho