This rather simple SQL query is proving to be quite perplexing when attempting it from LINQ.
从LINQ尝试时,这个相当简单的SQL查询被证明是非常令人困惑的。
I have a SQL table Plant with column ZoneMin.
我有一个带有ZoneMin列的SQL表工厂。
I want to find the minimum and maximum of the values in the column.
The answer in T-SQL is quite simple:
我想找到列中值的最小值和最大值。 T-SQL的答案很简单:
SELECT MIN(ZoneMin), MAX(ZoneMin) FROM Plant
SELECT MIN(ZoneMin),MAX(ZoneMin)FROM Plant
What's a LINQ query that could get me to this (or some similar) SQL?
什么是LINQ查询,可以让我这个(或类似的)SQL?
I've made various attempts at .Aggregate() and .GroupBy() with no luck. I've also looked at several SO questions that seem similar.
我已经对.Aggregate()和.GroupBy()进行了各种尝试而没有运气。我也看了几个看似相似的问题。
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.
这可以通过应用于结果数组的方法简单地实现,但是当在T-SQL中如此简单时,我不需要从每个SQL行传输值。
1 个解决方案
#1
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:
要获得与原始查询相同的性能,您需要使用分组(通过常量来最小化影响,例如0),以便您可以在同一查询中两次引用同一组记录。使用表名会导致在每个引用上生成新查询。请尝试以下方法:
(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 plantsGROUP BY plants.GrpAnd after the optimizer is done with it, it spits out something equivalent to your query, at least according to SQL Server Management Studio.
在优化器完成之后,它会向您的查询发出类似的内容,至少根据SQL Server Management Studio。
#1
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:
要获得与原始查询相同的性能,您需要使用分组(通过常量来最小化影响,例如0),以便您可以在同一查询中两次引用同一组记录。使用表名会导致在每个引用上生成新查询。请尝试以下方法:
(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 plantsGROUP BY plants.GrpAnd after the optimizer is done with it, it spits out something equivalent to your query, at least according to SQL Server Management Studio.
在优化器完成之后,它会向您的查询发出类似的内容,至少根据SQL Server Management Studio。