Convert SQL to Linq using group by

Question

I am beginner in linq, I want to convert this SQL query:

select UserId 
from Table 
where sID in (123, 124) 
group by UserId 
having count(distinct sID) = 2

I tried like this

var abc = obj_map.dbset
                 .where(x => sID.Contains(x.sID.ToString()))
                 .Select(m => UserId)
                 .Distinct()
                 .ToList();

How I can solve this?

hi, perhaps might be of interest https://stackoverflow.com/questions/7325278/group-by-in-linq — jspcal, Sep 19 '22 at 04:22

score 1 · Answer 1 · edited Sep 19 '22 at 09:35

1

You probably want something more like this:

int[] sIDs = new[] { 123, 124 };

IEnumerable<IGrouping<string, X>> abc =
    obj_map
        .dbset
        .Where(x => sIDs.Contains(x.sID))
        .GroupBy(x => x.UserId)
        .Where(gs => gs.Count() == 2);

edited Sep 19 '22 at 09:35

Svyatoslav Danyliv

21,911
3
16
32

answered Sep 19 '22 at 04:35

Enigmativity

113,464
11
89
172

Probably better to use `var` and and provide explicit projection of `UserId` in `Select`. – Svyatoslav Danyliv Sep 19 '22 at 10:50

Convert SQL to Linq using group by

1 Answers1