如何在带有联接的linq中使用聚合函数?

本文介绍了如何在带有联接的linq中使用聚合函数?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在编写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.