Linq Query with aggregate function

Question

Hello everyone, I am trying to figure out how to go about writing a linq query to perform an aggregate like the sql query below:

select d.ID, d.FIRST_NAME, d.LAST_NAME, count(s.id) as design_count
from tbldesigner d inner join
TBLDESIGN s on d.ID = s.DESIGNER_ID
where s.COMPLETED = 1 and d.ACTIVE = 1
group by d.ID, d.FIRST_NAME, d.LAST_NAME
Having COUNT(s.id) > 0

If this is even possible with a linq query could somebody please provide me with an example. Thanks in Advance, Billy

Answer 1

Here's how I'd do it. Please note that I'm accustomed to linqtosql and am unaware if there are differences for the query in linqtoentities.

var query =
  from d in myObjectContext.tbldesigner
  where d.ACTIVE == 1
  let manys =
    from s in d.tbldesign
    where s.COMPLETED == 1
    select s
  where manys.Count() > 0
  select new
  {
    d.ID, d.FIRST_NAME, d.LAST_NAME,
    DesignCount = manys.Count()
  };

Answer 2

A more direct translation of your original SQL query would look like this:

var q = 
    // Join tables TblDesign with TblDesigner and filter them
    from d in db.TblDesigner 
    join s in db.TblDesign on d.ID equals s.DesignerID
    where s.Completed && d.Active
    // Key and values used for grouping (note, you don't really need the
    // value here, because you only need Count of the values in a group, but
    // in case you needed anything from 's' or 'd' in 'select', you'd write this
    let value = new { s, d } 
    let key = new { d.ID, d.FirstName, d.LastName }
    group value by key into g 
    // Now, filter the created groups (return only non-empty) and select 
    // information for every group
    where g.Count() > 0
    select { ID = g.Key.ID, FirstName = g.Key.FirstName, 
             LastName = g.Key.LastName, Count = g.Count() };

The HAVING clause is translated to an ordinary where that is applied after grouping values using group ... by. The result of grouping is a collection of groups (another collections), so you can use where to filter groups. In the select clause, you can then return information from the key (used for grouping) and aggregate of values (using g.Count())

EDIT: As mmcteam points out (see comments), the where g.Count() > 0 clause is not necessary, because this is already guranteed by the join. I'll leave it there, because it shows how to translate HAVING clause in general, so it may be helpful in other cases.

Answer 3

Ignoring the s.id which is confusing me (see my comment on the question), this is a simple query which would generate a having clause. Of course, in this case it's a worthless example since the count will always be more than 0 in this case.

Anyways, if you are using SQL to Entities, you should use the entity mapping to access the foreign key relationships instead of manually doing a join or a subquery.

var results = from d in db.tbldesigner 
              where d.TBLDESIGN.COMPLETED && d.ACTIVE 
              group d by new {d.ID, d.FIRST_NAME, d.LAST_NAME} into g
              where g.Count() >= 0
              select new {
                 d.ID, d.FIRST_NAME, d.LAST_NAME,
                 Count = g.Count()
              };

NOTE: This is untested (and uncompiled) so there might be some issues, but this is where I would start.