`SELECT MIN（ZoneMin），MAX（ZoneMin）FROM Plant`作为的LINQ to SQL [英] `SELECT MIN(ZoneMin), MAX(ZoneMin) FROM Plant` as LINQ to SQL

问题描述

这相当简单的SQL查询被证明是从LINQ尝试它的时候是相当令人费解。

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

我有一个SQL表工厂与列 ZoneMin 。

I have a SQL table Plant with column ZoneMin.

我想找到列中的值的最小值和最大值。
在T-SQL的答案很简单：

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）从植物

什么是LINQ查询，可以让我到这个（或类似）的SQL？

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

我做了各种尝试.Aggregate（）和.GroupBy（），没有运气。我也看了几个SO问题，似乎类似。

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

这可以简单地应用到结果数组方式实现，但我不应该需要从每一个SQL行运的值时，它是如此简单的T-SQL。

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.

推荐答案

要达到同样的性能作为原始查询，则需要使用分组（由一个常数，以尽量减少影响，如 0 ），这样就可以在相同的查询指代相同的记录集的两倍。使用表名会导致一个新的查询要对每个参考生产。请尝试以下操作：

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()

这会产生以下查询：

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

和之后的优化器使用完时，吐出来的东西等同于您的查询，根据SQL Server Management Studio中最少。

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

这篇关于`SELECT MIN（ZoneMin），MAX（ZoneMin）FROM Plant`作为的LINQ to SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！