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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With