Using Linq to SQL, how do I find min and max of a column in a table?

Question

I want to find the fastest way to get the min and max of a column in a table with a single Linq to SQL roundtrip. So I know this would work in two roundtrips:

int min = MyTable.Min(row => row.FavoriteNumber);
int max = MyTable.Max(row => row.FavoriteNumber);

I know I can use group but I don't have a group by clause, I want to aggregate over the whole table! And I can't use the .Min without grouping first. I did try this:

from row in MyTable 
group row by true into r 
select new { 
    min = r.Min(z => z.FavoriteNumber), 
    max = r.Max(z => z.FavoriteNumber) 
}

But that crazy group clause seems silly, and the SQL it makes is more complex than it needs to be.

So, is there any way to just get the correct SQL out?

EDIT: These guys failed too: Linq to SQL: how to aggregate without a group by? ... lame oversight by LINQ designers if there's really no answer.

EDIT 2: I looked at my own solution (with the nonsensical constant group by clause) in the SQL Server Management Studio execution plan analysis, and it looks to me like it is identical to the plan generated by:

SELECT MIN(FavoriteNumber), MAX(FavoriteNumber)
FROM MyTable

so unless someone can come up with a simpler-or-equally-as-good answer, I think I have to mark it as answered-by-myself. Thoughts?

Answer 1

As stated in the question, this method seems to actually generate optimal SQL code, so while it looks a bit squirrely in LINQ, it should be optimal performance-wise.

from row in MyTable   group row by true into r   select new {       min = r.Min(z => z.FavoriteNumber),       max = r.Max(z => z.FavoriteNumber)   }  

Answer 2

I could find only this one which produces somewhat clean sql still not really effective comparing to select min(val), max(val) from table:

var r =   (from min in items.OrderBy(i => i.Value)    from max in items.OrderByDescending(i => i.Value)    select new {min, max}).First(); 

the sql is

SELECT TOP (1)     [t0].[Value],     [t1].[Value] AS [Value2] FROM     [TestTable] AS [t0],     [TestTable] AS [t1] ORDER BY     [t0].[Value],     [t1].[Value] DESC 

still there is another option to use single connection for both min and max queries (see Multiple Active Result Sets (MARS))

or stored procedure..

Answer 3

I'm not sure how to translate it into C# yet (I'm working on it)

This is the Haskell version

minAndMax :: Ord a => [a] -> (a,a)
minAndMax [x]    = (x,x)
minAndMax (x:xs) = (min a x, max b x)
                   where (a,b) = minAndMax xs

The C# version should involve Aggregate some how (I think).