11

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.

bdukes
  • 152,002
  • 23
  • 148
  • 175
Peter
  • 1,488
  • 2
  • 17
  • 23

1 Answers1

14

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.

Allon Guralnek
  • 15,813
  • 6
  • 60
  • 93