单个Linq-to-Entities查询中的多个SQL聚合函数 [英] Multiple SQL aggregate functions in a single Linq-to-Entities query

问题描述

在SQL中，您可以在单个数据库查询中表示多个聚合，如下所示：

  SELECT MIN（px），MAX px），MIN（py），MAX（py）
 FROM Places p JOIN Logs l on p.Id = l.PlaceId 
 WHERE l.OwnerId = @OwnerId 
  

可以使用Linq-to-Entities做一个同等的事情吗？我发现一个类似的问题建议Linq-to SQL不可能，但是我想认为我不需要使用四次往返DB的方式来执行。

解决方案

假设您有以下SQL语句：

  select sum（A），max（B） ，来自TBL组的平均（C）由D 
  

在C＃中尝试这个：

  from t in table 
 group t by D 
 into g 
 select new {
s = g。 Sum（x => xA），
m = g.Max（x => xB），
a = g.Average（x => xC）
} 
  

- 或在VB中： -

在$ TB 
组t中，从$ t 
 $ g 
选择s = g.Sum（function（x）xA），
m = g.Max（function（x）xB），
a = g.Average（function（x） xC）
  

显而易见的是，VB中的是：

pre> 将TBL中的聚合t转换为s = Sum（tA），m = Max（tB），a =平均（tC）
虽然它会给出相同的结果，但是它会产生更高的成本，因为它发出多个SQL select语句，每个聚合函数一个，即它将运行在多次通过第一个语法给出了一个单一（相当复杂但有效的）SQL语句，它对数据库执行单次传递。

PS。如果您没有按组合键（即，您需要一行作为结果覆盖整个数据集），请使用常量，如：

 从t在TBL 
组t按键= 0 
进入g =组
选择s = g.Sum（function（x）xA） ，
m = g.Max（function（x）xB），
a = g.Average（function（x）xC）
  

In SQL you can express multiple aggregates in a single database query like this:

SELECT MIN(p.x), MAX(p.x), MIN(p.y), MAX(p.y)
FROM   Places p JOIN Logs l on p.Id = l.PlaceId
WHERE  l.OwnerId = @OwnerId

Is it possible to do an equivalent thing using Linq-to-Entities? I found a similar question that suggests it's not possible for Linq-to-SQL but I would like to think I won't have to do the above using four round trips to the DB.

解决方案

Suppose you have the following SQL statement:

  select sum(A), max(B), avg(C) from TBL group by D

Try this in C#:

  from t in table
  group t by D
  into g
  select new {
     s = g.Sum(x => x.A),
     m = g.Max(x => x.B),
     a = g.Average(x => x.C)
  }

-- or in VB: --

  from t in TBL
  group t by key = D
  into g = group
  select s = g.Sum(function(x) x.A),
       m = g.Max(function(x) x.B),
       a = g.Average(function(x) x.C)

The obvious, which in VB would be:

  aggregate t in TBL into s = Sum(t.A), m = Max(t.B), a = Average(t.C)

though it will give the same results, it has a higher cost as it issues multiple SQL select statements, one for each aggregate function, i.e. it will run in multiple passes. The first syntax, gives a single (fairly complex, but efficient) SQL statement which does a single pass against the database.

PS. If you don't have a key by which to group by (i.e. you need a single row as the result, covering the whole data set), use a constant as in:

  from t in TBL
  group t by key = 0
  into g = group
  select s = g.Sum(function(x) x.A),
       m = g.Max(function(x) x.B),
       a = g.Average(function(x) x.C)

这篇关于单个Linq-to-Entities查询中的多个SQL聚合函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！