How to use aggregate functions in linq with joins?

Question

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 

I tried to linq as below.

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.

Answer 1

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

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

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.