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

Question

I know this question, but what I would like to do is get 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 }; 

What 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] 

Both of them work, and the impact on performance is probably negligible, so it is mostly aesthetic:
Two generated queries hurt me.

Answer 1

Try deleting let statements - the expected results are shown below:

 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 result:

 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

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