LinqToEntities creates invalid SQL (double subquery)

Question

I have a LinqToEntities query that double creates a subquery when creating SQL. This causes the result set to return with results 0-3 each time the query is run. A subquery itself produces a single random result (as it should). What's going on here?

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 } 

Create 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

This does this due to the expected behavior of FirstOrDefault() . Calling FirstOrDefault() on an empty JobProviderJobs set will result in a null value, but calling it on an empty int set will result in 0 . Recognizing this, LINQ to Entities tries to call the case statement at the end of the query to ensure that if there are no matching JobProviderJobs, the result of the selection will be 0 instead of null.

In most cases, recreating the inner projection will not cause problems, but using NewGuid() explicitly excludes this logic.

You have found one solution. Another would be to make the result of an internal expression this way:

 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 will repeat the use of the original SQL projection instead of creating two equivalent forecasts. The parser is probably just not complex enough to handle this properly, and developers have never seen the need to fix it because SQL Server should be able to optimize identical forecasts, provided that they are deterministic.

You should probably register a bug report if it does not already exist.