How to use distinct with group by in Linq to SQL
26,422
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()
};
Author by
Marco
Updated on July 09, 2022Comments
-
Marco almost 2 years
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]
-
Marco over 13 yearsYes, please look at my original post to see the invalid sql generated.
-
Basic over 13 yearsOut 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
-
Marco over 13 yearsYes, the GroupID is nullable but I also tried on another non-nullable column. System.Data.Linq.Table<ProcessRoundIssueInstance>