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]
Rodney S. Foley :
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.\n\nvar q = from i in ProcessRoundIssueInstance\n where i.GroupID != null\n && i.GroupID != string.Empty\n group i by i.GroupID into g \n select new\n {\n Key = g.Key,\n Count = g.Select(x => x.UserID).Distinct().Count()\n };\n",
2010-10-24T23:34:32
Dave Swersky :
According to this post, your code looks correct:\n\nLINQ to SQL using GROUP BY and COUNT(DISTINCT)\n\nHave you tried inspecting the SQL that is generated?",
2010-08-26T20:40:26
James Curran :
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.",
2010-08-26T20:51:14
Basic :
Try a where clause to eliminate spurious Ids after the join...\n\nvar q = from i in ProcessRoundIssueInstance\n where i.GroupID != \"\"\n group i by i.GroupID into g\n select new\n {\n Key = g.Key,\n Count = g.Select(x => x.UserID).Distinct().Count()\n };\n",
2010-08-26T20:51:22