Sql to Linq difficulty - group by, having

Question

I have the following query in SQL which 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 am having a difficulty how to write the last line in my sql query into linq. The following is my work so far:

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 may main problem is that many convert a having sql statement into a where linq statement, but in my case i do not think it is possible to write another where after the group by linq statement!

Update:

The situation: I have a number of children, each of which has many different profiles, (some may be the same). A user will select a number of children, from which I would like to derive their common profiles. That is, if profile X is found for EVERY child, than I will get it, if profile Y is found for every child except one, than it would be invalid!

I'm now starting to think that I have to use a NESTED query! — test, May 01 '12 at 13:33

score 10 · Accepted Answer · answered May 01 '12 at 13:33

10

Sounds like you want 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

answered May 01 '12 at 13:33

Jon Skeet

1,421,763
867
9,128
9,194

thank you my friend, I thought I couldn't write another where :) – test May 01 '12 at 13:34
A small problem I'm having is that 'pcr' does not exist in the current context! – test May 01 '12 at 13:35
1

@test: See my update - I think I've got it now. The `select pcr` confused me, because you only actually want the profile ID, according to the original SQL... – Jon Skeet May 01 '12 at 13:38
yes, sorry for the ambiguity and thank you very much for your patience. Just posted an update about my current situation too. Thanks again :) – test May 01 '12 at 13:39
can I write g.Distinct(), it seems to return the whole object instead of simply an int. What do you think about that please? – test May 01 '12 at 13:45
@test: Well yes, `g` is a sequence of ProfileChildRelationships. It's not clear what you're expecting it to give you - or indeed where yuo're trying to use `g.Distinct()`. – Jon Skeet May 01 '12 at 13:48
i remained with your answer, now I get a list of id's of profiles, and now I also appended a foreach loop, looping in 'query' which calls another method getting the whole profile object for every id :) – test May 01 '12 at 13:58
@test: You shouldn't have to do that... this is why it's useful if you give more context than just "this is the SQL I'm trying to represent in LINQ" - it's not really clear what the aim is. – Jon Skeet May 01 '12 at 14:41

Sql to Linq difficulty - group by, having

1 Answers1