Query: [Nav Prop Translation] EF7 generating incorrect sql with optional relationships

[mrahhal]

Consider the following models:

    public class News
    {
        public int Id { get; set; }
        public string Title { get; set; }

        public Branch Branch { get; set; }
        public int? BranchId { get; set; }
    }

    public class NewsDto
    {
        public int Id { get; set; }
        public string Title { get; set; }
        public BranchDto Branch { get; set; }
    }

    public class Branch
    {
        public int Id { get; set; }
        public string Name { get; set; }

        public List<News> News { get; set; } = new List<News>();
    }

    public class BranchDto
    {
        public int Id { get; set; }
        public string Name { get; set; }
    }

Here we have an optional relationship between News and Branch (1-to-many).

We're issuing the following query:

    context.News
        .Select(dto => new NewsDto()
        {
            Id = dto.Id,
            Title = dto.Title,
            Branch = dto.Branch == null ? null : new BranchDto()
            {
                Id = dto.Branch.Id,
                Name = dto.Branch.Name
            }
        }).ToList();

EF6

The generated query is:

SELECT 
[Extent1].[Id] AS [Id], 
[Extent1].[Title] AS [Title], 
CASE WHEN ([Extent2].[Id] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1], 
[Extent1].[BranchId] AS [BranchId], 
[Extent2].[Name] AS [Name]
FROM  [dbo].[News] AS [Extent1]
LEFT OUTER JOIN [dbo].[Branches] AS [Extent2] ON [Extent1].[BranchId] = [Extent2].[Id]

This is how it should be: a left outer join after a CASE WHEN to handle null references to the Branches table.

EF7 (RC1)

The same query is generated as:

SELECT
[dto].[Id],
[dto].[Title],
[dto].[BranchId] IS NULL,
[dto].[BranchId],
[dto.Branch].[Name]
FROM [News] AS [dto]
INNER JOIN [Branch] AS [dto.Branch] ON [dto].[BranchId] = [dto.Branch].[Id]

I don't really understand why this is happening. The relationship is clearly optional so inner joins are just plain wrong. Other than that, the line [dto].[BranchId] IS NULL is giving a System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'IS' so I guess this isn't even valid sql.

This is only happening when the relationship is optional.

[mrahhal]

So I believe this is related to #3186

[smitpatel]

[dto].[BranchId] IS NULL will be fixed by #3703

[mrahhal]

@rowanmiller #3703 is done and the LOJ issue is being tracked in #3186. So I believe we should close this?

[rowanmiller]

@maumar anything you want to track on this issue... or is it good to be closed now (feel free to close if you are happy it's all covered).

[maumar]

I will keep it open for now to make sure that this specific case works (complex projection with navigations like that)

[rowanmiller]

👍

[mrahhal]

Just to add to this, I noticed similar problems occur even in the InMemory provider. Things like

context.News
    .Select(dto => new NewsDto()
    {
        Id = dto.Id,
        Title = dto.Title,
        Branch = dto.Branch == null ? null : new BranchDto()
        {
            Id = dto.Branch.Id,
            Name = dto.Branch.Name
        }
    }).ToList();

do not work as expected (return null).

So I guess a part of this is rooted in the common code and not something specific to the sql provider.

[maumar]

@mrahhal That is correct, navigation translation is done in the common part of the query pipeline - currently we always translate navigations into a LINQ join, which in memory have semantics of INNER JOIN and in relational get translated to INNER JOIN directly. The fix we are currently working on will translate optional navigations into SelectMany-GroupJoin-DefaultIfEmpty construct which behaves like LOJ

[maumar]

Fixed in c4e4447
Fix works fine for this particular scenario, however some more complex scenarios could still be broken or not efficient. E.g. Everything after optional navigation would be performed on the client.