David David - 3 months ago 25
C# Question

Linq grouping multiple where condition

I have a query that I wrote in SQL, which works, and I am trying to move this to the appropriate LINQ statement, and can't seem to get it quite right.

The SQL query:

select sku
from Table
group by sku
having count(sku) > 1 and count(distinct(unit)) > 1


and what I have so far with LINQ

var dupCount = (from val in dt.AsEnumerable()
group val by new {sku = val[4]} into grp
where grp.Count() > 1 && grp.unit.Distinct().Count() > 1
select grp).ToList();


I cant seem to figure out how to tell the where within the group to only pull the records with a distinct "unitreserve".

Answer

I cant seem to figure out how to tell the where within the group to only pull the records with a distinct "unitreserve".

Select the "unitreverse" field, apply Distinct and then Count it:

where grp.Count() > 1 && 
      grp.Select(dr => dr["unitreserve"]).Distinct().Count() > 1