LINQ thinks I need an extra INNER JOIN, but why?

Posted by Saurabh Kumar on Stack Overflow See other posts from Stack Overflow or by Saurabh Kumar
Published on 2010-05-15T11:41:22Z Indexed on 2010/05/15 11:44 UTC
Read the original article Hit count: 264

Filed under:
|
|

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

© Stack Overflow or respective owner

Related posts about LINQ

Related posts about sql