LinqToEntities produces incorrect SQL (Doubled Subquery)

Question

I have a LinqToEntities query that double produces a subquery when creating the SQL. This causes the result set to come back with 0-3 results, every time the query is run. The subquery on its own produces a single random result (as it should). What is going on here?

The LINQ query:

from jpj in JobProviderJobs
where jpj.JobID == 4725 
&& jpj.JobProviderID == (from jp2 in JobProviderJobs 
                        where jp2.JobID == 4725 
                        orderby Guid.NewGuid() 
                        select jp2.JobProviderID).FirstOrDefault()
select new 
{
    JobProviderID = jpj.JobProviderID
}

Produce this SQL:

SELECT 
[Filter2].[JobID] AS [JobID], 
[Filter2].[JobProviderID1] AS [JobProviderID]
FROM   (SELECT [Extent1].[JobID] AS [JobID], [Extent1].[JobProviderID] AS [JobProviderID1], [Limit1].[JobProviderID] AS [JobProviderID2]
    FROM  [dbo].[JobProviderJob] AS [Extent1]
    LEFT OUTER JOIN  (SELECT TOP (1) [Project1].[JobProviderID] AS [JobProviderID]
        FROM ( SELECT 
            NEWID() AS [C1], 
            [Extent2].[JobProviderID] AS [JobProviderID]
            FROM [dbo].[JobProviderJob] AS [Extent2]
            WHERE 4725 = [Extent2].[JobID]
        )  AS [Project1]
        ORDER BY [Project1].[C1] ASC ) AS [Limit1] ON 1 = 1
    WHERE 4725 = [Extent1].[JobID] ) AS [Filter2]
LEFT OUTER JOIN  (SELECT TOP (1) [Project2].[JobProviderID] AS [JobProviderID]
    FROM ( SELECT 
        NEWID() AS [C1], 
        [Extent3].[JobProviderID] AS [JobProviderID]
        FROM [dbo].[JobProviderJob] AS [Extent3]
        WHERE 4725 = [Extent3].[JobID]
    )  AS [Project2]
    ORDER BY [Project2].[C1] ASC ) AS [Limit2] ON 1 = 1
WHERE [Filter2].[JobProviderID1] = (CASE WHEN ([Filter2].[JobProviderID2] IS NULL) THEN 0 ELSE [Limit2].[JobProviderID] END)

EDIT:

So changing the subquery to this works, but I have no idea why

(from jp2 in JobProviderJobs 
    where jp2.JobID == 4725 
    orderby Guid.NewGuid() 
    select jp2).FirstOrDefault().JobProviderID

Answer 1

It's doing this due to the expected behavior of FirstOrDefault(). Calling FirstOrDefault() on an empty set of JobProviderJobs would produce a null value, but calling it on an empty set of ints would produce a 0. Recognizing this, LINQ to Entities tries to invoke a case statement at the end of the query to ensure that if there are no matching JobProviderJobs, the result of the select will be 0 instead of null.

In most cases, recreating the inner projection would cause no problems, but your use of NewGuid() obviously throws this logic off.

You found one solution. Another one would be to cast the result of the inner expression thusly:

from jpj in JobProviderJobs
where jpj.JobID == 4725 
&& jpj.JobProviderID == (from jp2 in JobProviderJobs 
                        where jp2.JobID == 4725 
                        orderby Guid.NewGuid() 
                        select (int?) jp2.JobProviderID).FirstOrDefault()
select new 
{
    JobProviderID = jpj.JobProviderID
}

A more correct implementation would reuse the initial SQL projection rather than creating two equivalent projections. It's likely that parser simply isn't complex enough to handle this properly, and the developers never saw any need to fix it because SQL Server should be able to optimize the identical projections away as long as they are deterministic.

You should probably log a bug report about this if one doesn't already exist.