LINQ thinks I need an extra INNER JOIN, but why?
- by Saurabh Kumar
I have a LINQ query, which for some reason is generating an extra/duplicatre INNER JOIN. This is causing the query to not return the expected output. If I manually comment that extra JOIN from the generated SQL, then I get seemingly correct output.
Can you detect what I might have done i nthis LINQ to have cuased this extra JOIN?
Thanks.
Here is my approx LINQ
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
};
and here is the generated SQL (the duplicate join is [t7])
Declare @p1 VarChar(10)='Home'
Declare @p2 VarChar(10)='111'
Declare @p3 VarChar(10)='Office'
Declare @p4 VarChar(10)='222'
Declare @p5 int=2
SELECT [t9].[PersonID] AS [pid]
FROM (
SELECT [t3].[PersonID], (
SELECT COUNT(*)
FROM (
SELECT DISTINCT [t7].[PhoneValue]
FROM [dbo].[Person] AS [t4]
INNER JOIN [dbo].[PersonPhoneNumber] AS [t5] ON [t5].[PersonID] = [t4].[PersonID]
INNER JOIN [dbo].[CodeMaster] AS [t6] ON [t6].[Code] = [t5].[PhoneType]
INNER JOIN [dbo].[PersonPhoneNumber] AS [t7] ON [t7].[PersonID] = [t4].[PersonID]
WHERE ([t3].[PersonID] = [t4].[PersonID]) AND ([t6].[Enumeration] = @p0) AND ((([t6].[CodeDescription] = @p1) AND ([t5].[PhoneValue] = @p2)) OR (([t6].[CodeDescription] = @p3) AND ([t5].[PhoneValue] = @p4)))
) AS [t8]
) AS [value]
FROM (
SELECT [t0].[PersonID]
FROM [dbo].[Person] AS [t0]
INNER JOIN [dbo].[PersonPhoneNumber] AS [t1] ON [t1].[PersonID] = [t0].[PersonID]
INNER JOIN [dbo].[CodeMaster] AS [t2] ON [t2].[Code] = [t1].[PhoneType]
WHERE ([t2].[Enumeration] = @p0) AND ((([t2].[CodeDescription] = @p1) AND ([t1].[PhoneValue] = @p2)) OR (([t2].[CodeDescription] = @p3) AND ([t1].[PhoneValue] = @p4)))
GROUP BY [t0].[PersonID]
) AS [t3]
) AS [t9]
WHERE [t9].[value] = @p5
