Hi, I am trying to convert a SQL query to LINQ. Somehow my count(distinct(x)) logic does not seem to be working correctly. The original SQL is quite efficient(or so i think), but the generated SQL is not even returning the correct result.
I am trying to fix this LINQ to do what the original SQL is doing, AND in an efficient way as the original query is doing. Help here would be really apreciated as I am stuck here :(
SQL which is working and I need to make a comparable LINQ of:
SELECT [t1].[PersonID] AS [personid]
FROM [dbo].[Code] AS [t0]
INNER JOIN [dbo].[phonenumbers] AS [t1] ON [t1].[PhoneCode] = [t0].[Code]
INNER JOIN [dbo].[person] ON [t1].[PersonID]= [dbo].[Person].PersonID
WHERE ([t0].[codetype] = 'phone') AND (
([t0].[CodeDescription] = 'Home') AND ([t1].[PhoneNum] = '111')
OR
([t0].[CodeDescription] = 'Work') AND ([t1].[PhoneNum] = '222') )
GROUP BY [t1].[PersonID] HAVING COUNT(DISTINCT([t1].[PhoneNum]))=2
The LINQ which I made is approximately as below:
var ids = context.Code.Where(predicate);
var rs = from r in ids
group r by new { r.phonenumbers.person.PersonID} into g
let matchcount=g.Select(p => p.phonenumbers.PhoneNum).Distinct().Count()
where matchcount ==2
select new
{
personid = g.Key
};
Unfortunately, the above LINQ is NOT generating the correct result, and is actually internally getting generated to the SQL shown below. By the way, this generated query is also reading ALL the rows(about 19592040) around 2 times due to the COUNTS :( Wich is a big performance issue too. Please help/point me to the right direction.
Declare @p0 VarChar(10)='phone'
Declare @p1 VarChar(10)='Home'
Declare @p2 VarChar(10)='111'
Declare @p3 VarChar(10)='Work'
Declare @p4 VarChar(10)='222'
Declare @p5 VarChar(10)='2'
SELECT [t9].[PersonID], (
SELECT COUNT(*)
FROM (
SELECT DISTINCT [t13].[PhoneNum]
FROM [dbo].[Code] AS [t10]
INNER JOIN [dbo].[phonenumbers] AS [t11] ON [t11].[PhoneType] = [t10].[Code]
INNER JOIN [dbo].[Person] AS [t12] ON [t12].[PersonID] = [t11].[PersonID]
INNER JOIN [dbo].[phonenumbers] AS [t13] ON [t13].[PhoneType] = [t10].[Code]
WHERE ([t9].[PersonID] = [t12].[PersonID]) AND ([t10].[codetype] = @p0) AND ((([t10].[codetype] = @p1) AND ([t11].[PhoneNum] = @p2)) OR (([t10].[codetype] = @p3) AND ([t11].[PhoneNum] = @p4)))
) AS [t14]
) AS [cnt]
FROM (
SELECT [t3].[PersonID], (
SELECT COUNT(*)
FROM (
SELECT DISTINCT [t7].[PhoneNum]
FROM [dbo].[Code] AS [t4]
INNER JOIN [dbo].[phonenumbers] AS [t5] ON [t5].[PhoneType] = [t4].[Code]
INNER JOIN [dbo].[Person] AS [t6] ON [t6].[PersonID] = [t5].[PersonID]
INNER JOIN [dbo].[phonenumbers] AS [t7] ON [t7].[PhoneType] = [t4].[Code]
WHERE ([t3].[PersonID] = [t6].[PersonID]) AND ([t4].[codetype] = @p0) AND ((([t4].[codetype] = @p1) AND ([t5].[PhoneNum] = @p2)) OR (([t4].[codetype] = @p3) AND ([t5].[PhoneNum] = @p4)))
) AS [t8]
) AS [value]
FROM (
SELECT [t2].[PersonID]
FROM [dbo].[Code] AS [t0]
INNER JOIN [dbo].[phonenumbers] AS [t1] ON [t1].[PhoneType] = [t0].[Code]
INNER JOIN [dbo].[Person] AS [t2] ON [t2].[PersonID] = [t1].[PersonID]
WHERE ([t0].[codetype] = @p0) AND ((([t0].[codetype] = @p1) AND ([t1].[PhoneNum] = @p2)) OR (([t0].[codetype] = @p3) AND ([t1].[PhoneNum] = @p4)))
GROUP BY [t2].[PersonID]
) AS [t3]
) AS [t9]
WHERE [t9].[value] = @p5
Thanks!