Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

`SELECT MIN(ZoneMin), MAX(ZoneMin) FROM Plant` as LINQ to SQL

This rather simple SQL query is proving to be quite perplexing when attempting it from LINQ.

I have a SQL table Plant with column ZoneMin.

I want to find the minimum and maximum of the values in the column.
The answer in T-SQL is quite simple:

SELECT MIN(ZoneMin), MAX(ZoneMin) FROM Plant

What's a LINQ query that could get me to this (or some similar) SQL?

I've made various attempts at .Aggregate() and .GroupBy() with no luck. I've also looked at several SO questions that seem similar.

This could be simply achieved with methods applied to a resulting array, but I shouldn't need to transport a value from every SQL row when it's so simple in T-SQL.

like image 351
Peter Avatar asked Jan 12 '11 18:01

Peter


1 Answers

To achieve the same performance as your original query, you'll need to use grouping (by a constant to minimize impact, e.g. 0), so that you can refer to the same set of records twice in the same query. Using the table name causes a new query to be produced on each reference. Try the following:

(from plant in db.Plants
 group plant by 0 into plants
 select new { Min = plants.Min(p => p.ZoneMin), Max = plants.Max(p => p.ZoneMin) }
).Single()

This produces the following query:

SELECT MIN(plants.ZoneMin), MAX(plants.ZoneMin)
FROM (SELECT 0 AS Grp, ZoneMin FROM Plants) AS plants
GROUP BY plants.Grp

And after the optimizer is done with it, it spits out something equivalent to your query, at least according to SQL Server Management Studio.

like image 158
Allon Guralnek Avatar answered Nov 07 '22 03:11

Allon Guralnek