如何在带有联接的linq中使用聚合函数? [英] How to use aggregate functions in linq with joins?

问题描述

我正在编写linq查询，以获取来自两个表中列的最大计数.我写了sql查询，它在下面.

Hi I am writing linq query to get max count with columns from the two tables. I write sql query and it is below.

select MAX(p.dispalyOrder) from NCT_Process p INNER JOIN NCT_Process_Settings s ON 
p.projectId =s.projectId AND p.level=s.level 

我试图如下所示.

dbObject = (from c in entityObject.NCT_Process_Settings
            join process in entityObject.NCT_Process on c.projectId equals process.projectId
            join level in entityObject.NCT_Process on c.level equals level.level
            select new settingsobject
            {
                MAX(p.dispalyOrder) to some propert of settingsobject
            }).Tolist();

我不确定如何从过程表中获得最大显示顺序.任何帮助，将不胜感激.谢谢.

I am not sure how to get max display order from the process table. Any help would be appreciated. Thank you.

推荐答案

您只需选择值，然后在查询中调用Max.另外，要加入多个列，还必须使用要匹配的列创建匿名类.

You just select the value and then call Max on the query. Also to join on multiple columns you have to create anonymous classes with the columns you want to match.

max = (from c in entityObject.NCT_Process_Settings
       join p in entityObject.NCT_Process 
       on new { c.projectId, c.level } equals new { p.projectId, p.level }
       select p.dispalyOrder).Max();

如果您还希望合并其他列，则可以对一个恒定值进行分组.

If you have other columns you also want to aggregate then you can do a group by on a constant value.

result = (from c in entityObject.NCT_Process_Settings
          join p in entityObject.NCT_Process 
          on new { c.projectId, c.level } equals new { p.projectId, p.level }
          group new{c,p} on 1 into grp
          select new {
              MaxDisplayOrder = grp.Max(x => x.p.dispalyOrder),
              AvgOfSomething = grp.Avgerage(x => x.c.Something),
              MinOfASum = grp.Min(x => x.p.SomeNumber + x.c.SomeOtherNumber)
          }).Single();

请注意使用Single，因为在常量上使用group by仅会导致一行.

Note the use of Single because an group by on a constant will only result in one row.

或者，如果您只想获取按另一列分组的最大显示顺序，请执行此操作

Or if you just want to get the max display order grouped by another column then do this

result = (from c in entityObject.NCT_Process_Settings
          join p in entityObject.NCT_Process 
          on new { c.projectId, c.level } equals new { p.projectId, p.level }
          group p.displayOrder on c.Id into grp
          select new {
              MaxDisplayOrder = grp.Max(),
              Id = grp.Key
          }).ToList();

请注意，Key是放置在on之后的任何内容，grp是on之前的值的集合.在这种情况下，可能会有多个结果，因此您可以使用ToList来运行查询.

Note that the Key is whatever you put after the on and that grp is a collection of the values before the on. And in this case there can be multiple results so you'd use ToList to run the query.

这篇关于如何在带有联接的linq中使用聚合函数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！