Why does the order of LET statements matter in this Entity Framework query?

Question

A query for a grid in an Entity Framework-backed .NET web application I'm working on was giving a 500 error (The cast to value type 'System.Int32' failed because the materialized value is null. Either the result type's generic parameter or the query must use a nullable type.) when the grid row object happened to have zero child items in a particular one-to-many relationship. The null was coming back on an unrelated integer property. Bafflingly, reversing the order of the two independent Let statements in the Linq expression made the error go away.

That is, if there is only one Widget (ID: 1, CreatedOn: some datetime), which has no Bars and one Foo (fValue: 96)

from w in Widgets.OrderBy(w => w.CreatedOn)
let foo = w.Foos.FirstOrDefault()
let bar = w.Bars.FirstOrDefault()
select new { w.WidgetID, foo.fValue }

or

from w in Widgets
let bar = w.Bars.FirstOrDefault()
let foo = w.Foos.FirstOrDefault()
orderby w.CreatedOn
select new { w.WidgetID, foo.fValue }

gives {WidgetID: 1, fValue: 96} as expected, but

from w in Widgets.OrderBy(w => w.CreatedOn)
let bar = w.Bars.FirstOrDefault()
let foo = w.Foos.FirstOrDefault()
select new { w.WidgetID, foo.fValue }

comes back with {WidgetID: 1, fValue: NULL} which of course crashes because Foo.fValue is an integer.

All three expressions generate slightly different SQL queries under Entity Framework, which I would expect - the failing expression contains the clause

... 
(SELECT TOP (1) 
    [Extent7].[fValue] AS [fValue]
    FROM   (SELECT TOP (1) [Extent6].[BarID] AS [BarID]
        FROM [dbo].[Bars] AS [Extent6]
        WHERE [Extent1].[WidgetID] = [Extent6].[bWidgetID] ) AS [Limit5]
    CROSS JOIN [dbo].[Foos] AS [Extent7]
    WHERE [Extent1].[WidgetID] = [Extent7].[fWidgetID]) AS [C1]
...

which I believe is the culprit (0 Bars crossed with 1 Foo = 0 results). So I understand the "how" of the error; what gets me is that I have no idea why the order of the LETs or whether I OrderBy with a Linq method call vs a Linq expression should make a difference.

Here's the reduced table schema / data if you want to experiment yourself:

create table Widgets (
    WidgetID int not null primary key,
    CreatedOn datetime not null
)    
insert Widgets values (1, '1995-02-03')

create table Foos (
    FooID int not null primary key,
    fWidgetID int not null references Widgets (WidgetID),
    fValue int not null
)
insert Foos values (7, 1, 96)

create table Bars (
    BarID int not null primary key,
    bWidgetID int not null references Widgets (WidgetID),
    bValue int not null
)

Can you explain why those 3 expressions aren't logically equivalent in Entity Framework?

Answer 1

I believe that this is a bug related to this know Entity Framework issue: https://entityframework.codeplex.com/workitem/1196. According to the issue, when using order by, let, and FirstOrDefault the query tree is compiled to a buggy SQL query.

Sadly, the issue is nearly two years old so this particular bug may not be a high priority for the EF team. Maybe it will be fixed in EF7!