How to use distinct with group by in Linq to SQL

Question

I'm trying to convert the following sql to Linq 2 SQL:

select groupId, count(distinct(userId)) from processroundissueinstance 
group by groupId

Here is my code:

var q = from i in ProcessRoundIssueInstance
    group i by i.GroupID into g
    select new
    {
        Key = g.Key,
        Count = g.Select(x => x.UserID).Distinct().Count()
    };

When I run the code, I keep getting Invalid GroupID. Any ideas? Seems the distinct is screwing things up..

Here is the generated sql:

SELECT [t1].[GroupID] AS [Key], (
SELECT COUNT(*)
FROM (
    SELECT DISTINCT [t2].[UserID]
    FROM [ProcessRoundIssueInstance] AS [t2]
    WHERE (([t1].[GroupID] IS NULL) AND ([t2].[GroupID] IS NULL)) 
       OR (([t1].[GroupID] IS NOT NULL) 
            AND ([t2].[GroupID] IS NOT NULL) 
            AND ([t1].[GroupID] = [t2].[GroupID]))
    ) AS [t3]
) AS [Count]
FROM (
    SELECT [t0].[GroupID]
    FROM [ProcessRoundIssueInstance] AS [t0]
    GROUP BY [t0].[GroupID]
    ) AS [t1]

@Basiclife: What's FUBAR about that? In SQL NULL is not equal to anything, including NULL. If both were NULL, `t1.GroupID = t2.GroupId` would still be false. That's that only way to associate two NULL fields. — James Curran, Aug 26 '10 at 20:52

score 5 · Answer 1 · answered Oct 24 '10 at 23:34

I think Basiclife is close, but checking if the id is empty may not be the issue or enough, you should check to make sure it is not null before doing the group since you said it is a nullable field. Otherwise it looks right, and if you are having issues you may have bad data, or it is a bug or not fully implemented feature of Linq to SQL, and you may want to try Linq to Entity.

var q = from i in ProcessRoundIssueInstance
        where i.GroupID != null
        && i.GroupID != string.Empty
        group i by i.GroupID into g        
        select new
        {
            Key = g.Key,
            Count = g.Select(x => x.UserID).Distinct().Count()
        };

score 0 · Answer 2 · edited May 23 '17 at 12:07

0

According to this post, your code looks correct:

LINQ to SQL using GROUP BY and COUNT(DISTINCT)

Have you tried inspecting the SQL that is generated?

edited May 23 '17 at 12:07

Community

1
1

answered Aug 26 '10 at 20:40

Dave Swersky

34,502
9
78
118

Yes, please look at my original post to see the invalid sql generated. – Marco Aug 26 '10 at 20:44

score 0 · Answer 3 · answered Aug 26 '10 at 20:51

0

There appears to be a whole bunch of goop in the generated SQL to deal with the GroupID being NULL. If that a possiblity? IF not, try changing the definition to make it NOT NULL.

answered Aug 26 '10 at 20:51

James Curran

101,701
37
181
258

score 0 · Answer 4 · answered Aug 26 '10 at 20:51

0

Try a where clause to eliminate spurious Ids after the join...

var q = from i in ProcessRoundIssueInstance
    where i.GroupID != ""
    group i by i.GroupID into g
    select new
    {
        Key = g.Key,
        Count = g.Select(x => x.UserID).Distinct().Count()
    };

answered Aug 26 '10 at 20:51

Basic

26,321
24
115
201

Out of interest, is there any Id column nullable intentionally? Also, can you tell us the data type for ProcessRoundIssueInstance according to the debugger (not declaration). Thanks – Basic Aug 26 '10 at 21:24
Yes, the GroupID is nullable but I also tried on another non-nullable column. System.Data.Linq.Table – Marco Aug 26 '10 at 21:27

Marcel Valdez Orozco · Answer 5 · 2011-05-23T00:12:09.937

Are you certain of correct database integrity? Anyway maybe you should try this: I don't know how a group would be empty, but that seems to be your problem.

ProcessRoundIssueInstance.Where(i => i.GroupId != null)
    .GroupBy(i => i.GroupID)
    .Select(group => new 
                    { 
                      Key = group.Key,
                      Count = group.SingleOrDefault() == null ? 0 : 
                              group.SingleOrDefault().Select( item => item.UserID).Distinct().Count() 
                     });

How to use distinct with group by in Linq to SQL

5 Answers5

Linked