Strange problem converting a T-SQL query to Linq + Entity Framework.

[Herman]

So I have been updating an old management application here, and migrating queries to the Entity Framework with Linq, and after a couple dozens without a problem, this one is giving me a headache:

Original SQL query:

                              SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
                              IF OBJECT_ID('tempdb..#history') IS NOT NULL DROP TABLE #history; 
 
                              SELECT * INTO #history 
                              FROM usrTestsHistory h 
                              WHERE h.ExBuildId = 8406
                                AND (h.Lang = 'EN' OR h.Lang IS NULL); 
 
                              SELECT t.Inactive,
                                     h.TestComplete,
                                     t.SetupTest,
                                     c.Success,
                                     c.TestComplete,
                                     t.TestCode AS TestName,
                                     e.TestEdition AS Edition,
                                     ISNULL(c.Lang, 'EN') AS Lang,
                                     CASE h.ExecutionCount WHEN NULL THEN t.[Timeout] ELSE (h.ExecutionCount * 500) + t.[Timeout] END As QueuePosition
                              FROM usrTestRunEditions tre 
                              INNER JOIN usrTestSuiteName t ON tre.ExID = t.ID 
                              INNER JOIN usrTestsEditions e ON tre.TestEdition = e.ID 
                              LEFT JOIN #history h ON tre.ID = h.ExTestRunEditionsID 
                              LEFT JOIN usrTestLog c ON h.ID = c.exTestHistoryId 
                              WHERE (t.Inactive = 0 OR t.Inactive IS NULL) 
                                AND (h.TestComplete = 0 OR h.TestComplete is null)
                                AND (t.SetupTest = 0 OR t.SetupTest IS NULL) 
                                AND (c.Success = 0 OR c.Success IS NULL) 
                                AND (c.TestComplete = 0 OR c.TestComplete IS NULL) 
                              ORDER BY QueuePosition;

And the rewrite in VB.Net Linq to Entity:

        Using EnterpriseContext As New EnterpriseEntities
            Dim usrTestsHistory As ObjectQuery(Of usrTestsHistory) = EnterpriseContext.usrTestsHistory
            Dim usrTestsEditions As ObjectQuery(Of usrTestsEditions) = EnterpriseContext.usrTestsEditions
            Dim usrTestLog As ObjectQuery(Of usrTestLog) = EnterpriseContext.usrTestLog
            Dim usrTestSuiteName As ObjectQuery(Of usrTestSuiteName) = EnterpriseContext.usrTestSuiteName
            Dim usrTestRunEditions As ObjectQuery(Of usrTestRunEditions) = EnterpriseContext.usrTestRunEditions

            Dim QueryTestsHistory = From h In usrTestsHistory
                                    Where h.ExBuildId = BuildId _
                                      And (h.Lang = "EN" Or h.Lang Is Nothing)
                                    Select h

            Dim Query = From tre In usrTestRunEditions
                        Join t In usrTestSuiteName On tre.ExID Equals t.ID
                        Join e In usrTestsEditions On tre.TestEdition Equals e.ID
                        Group Join b In QueryTestsHistory On tre.ID Equals b.ExTestRunEditionsID Into a = Group
                        From h In a.DefaultIfEmpty
                        Group Join c In usrTestLog On h.ID Equals c.exTestHistoryId Into d = Group
                        From l In d.DefaultIfEmpty
                        Where (t.Inactive = False Or t.Inactive Is Nothing) _
                          And (h.RunningNow = False Or h.RunningNow Is Nothing) _
                          And (t.SetupTest = False Or t.SetupTest Is Nothing) _
                          And (l.Success = False Or l.Success Is Nothing) _
                          And (l.TestComplete = "0" Or l.TestComplete Is Nothing)
                        Order By CInt(If(h.ExecutionCount Is Nothing, t.Timeout, (h.ExecutionCount * 500) + t.Timeout)) Ascending
                        Select New With {.TestName = t.TestCode,
                                         .Edition = e.TestEdition,
                                         .Lang = If(l.Lang Is Nothing, "EN", l.Lang),
                                         .QueuePosition = CInt(If(h.ExecutionCount Is Nothing, t.Timeout, (h.ExecutionCount * 500) + t.Timeout))}

            GetTestsStack = Query
        End Using

The problem is that the original query correctly returns 117 rows, while the Linq query returns 1200 rows. I am guessing I messed up one of those Group Join somehow. Can anyone see what the problem is?

Thanks!

 

[Herman]

No one has any idea?