CODEWITHSUNDEEP
.netentity-frameworklinq
George Mauer
George Mauer

Reputation: 122042

Null-safe navigation in Entity Framework Linq query

A customer has zero or one languages associated. I would like to do the following

db.Customer.Select(x => new {
     x.Id, x.Name, 
     Language = new { x.Language?.Id, x.Language?.Name }
})

But this doesn't even compile as linq expressions don't seem to know what to do with the null safe get operator (?.). How do I do the equivalent? I'd like the query generated to be a LEFT OUTER JOIN with Language and for the Language key to either be null, or an object with a null Id.

Upvotes: 0

Views: 831

Answers (1)

Ivan Stoev
Ivan Stoev

Reputation: 205529

Simply use

db.Customer.Select(x => new {
     x.Id, x.Name, 
     Language = new { (int?)x.Language.Id, x.Language.Name }
})

which should generate SQL like this (of course table and column names could be different)

SELECT 
    [Extent1].[Id] AS [Id], 
    [Extent1].[Name] AS [Name], 
    [Extent1].[Language_Id] AS [Language_Id], 
    [Extent2].[Name] AS [Name1]
    FROM  [dbo].[Customers] AS [Extent1]
    LEFT OUTER JOIN [dbo].[Languages] AS [Extent2] ON [Extent1].[Language_Id] = [Extent2].[Id]

Here is another example of a similar problem, take a look at the generated SQL.

Update: However, we need to include T? cast when projecting value type properties, otherwise the query cannot be materialized (ToList() fails with InvalidOperationException: "Additional information: 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."

Update2: Another option is to use this construct 

db.Customer.Select(x => new {
     x.Id, x.Name, 
     Language = x.Language != null ? new { x.Language.Id, x.Language.Name } : null
})

The SQL looks like this

SELECT 
    [Extent1].[Id] AS [Id], 
    [Extent1].[Name] AS [Name], 
    CASE WHEN ([Extent2].[ID] IS NOT NULL) THEN 1 END AS [C1],
    [Extent1].[Language_Id] AS [Language_Id], 
    [Extent2].[Name] AS [Name1]
    FROM  [dbo].[Customers] AS [Extent1]
    LEFT OUTER JOIN [dbo].[Languages] AS [Extent2] ON [Extent1].[Language_Id] = [Extent2].[Id]

and when materialized, will produce a null Language member (in constrast with the previous approach which produces instance with all members set to null)

Upvotes: 3

Related Questions