Sql to Linq - a group having

Question

I have the following query in SQL that I would like to convert to LINQ:

select profile_id from t where child_id in (1, 2 ,3, ...) //this will be a list of integers CHILDREN group by profile_id having count(distinct child_id) = 3 

I find it hard to write the last line in my sql query in linq. Below is my work:

 public IQueryable<ProfileChildRelationship> GetPCRelByCids(List<int> children) { var query = from pcr in this._entities.ProfileChildRelationships where children.Contains(pcr.pcChildIDF) group pcr by pcr.pcProfileIDF into g ??? having ...? select pcr; return query; } 

I think that the main problem is that many convert the SQL query into a linq statement, but in my case, I don’t think it is possible to write another, where the linq statement is after the group!

Update:

Situation: I have several children, each of which has many different profiles (some may be the same). The user will select the number of children from whom I would like to receive common profiles. That is, if profile X is found for EVERY child, then I will get it if profile Y is found for each child, except for one that would be invalid!

Answer 1

Looks like you want to make a where clause here ...

 var query = from pcr in this._entities.ProfileChildRelationships where children.Contains(pcr.pcChildIDF) group pcr by pcr.pcProfileIDF into g where g.Select(x => x.ChildId).Distinct().Count() == 3 select g.Key; // This is the profile ID