`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.

我有一个带有 ZoneMin 列的 SQL 表 Plant.

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) FROM Plant

什么 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屋！