Query: incorrect sql generated for query with owned entities, left join and predicate using inner qsre

[maumar]

query:

from l1 in 
    (from l1 in l1s
     join l2 in l2s on l1.Id equals l2.Level1_Optional_Id into grouping
     from l2 in grouping.DefaultIfEmpty()
     where l2.Name != "Foo"
     select l1).Distinct()
select l1.Id

query model after nav rewrite:

from Level1 l1 in 
    (from Level1 l1 in DbSet<Level1>
    join Level2 l2 in 
        from Level1 t in DbSet<Level1>
        join Level2 t.OneToOne_Required_PK in DbSet<Level2>
        on Property([t], "Id") equals Property([t.OneToOne_Required_PK], "Id") into t.OneToOne_Required_PK_group
        from Level2 t.OneToOne_Required_PK in 
            (from Level2 t.OneToOne_Required_PK_groupItem in [t.OneToOne_Required_PK_group]
            select [t.OneToOne_Required_PK_groupItem]).DefaultIfEmpty()
        where Property([t.OneToOne_Required_PK], "Id") != null
        select [t.OneToOne_Required_PK]
    on (Nullable<int>)[l1].Id equals [l2]?.Level1_Optional_Id into grouping
    from Level2 l2 in 
        (from Level2 <generated>_1 in [grouping]
        select [<generated>_1]).DefaultIfEmpty()
    where [l2]?.Name != "Foo"
    select [l1]).Distinct()
select (int)[l1]?.Id

generated sql:

SELECT [t1].[Id], [t1].[OneToOne_Required_PK_Date], [t1].[OneToOne_Required_PK_Level1_Optional_Id], [t1].[OneToOne_Required_PK_Level1_Required_Id], [t1].[OneToOne_Required_PK_Name], [t1].[OneToOne_Required_PK_OneToOne_Optional_PK_InverseId], [t1].[Id]
FROM (
	SELECT DISTINCT [t0].[Id], [t0].[OneToOne_Required_PK_Date], [t0].[OneToOne_Required_PK_Level1_Optional_Id], [t0].[OneToOne_Required_PK_Level1_Required_Id], [t0].[OneToOne_Required_PK_Name], [t0].[OneToOne_Required_PK_OneToOne_Optional_PK_InverseId]
	FROM [Level1] AS [l1]
	LEFT JOIN (
		SELECT [t].[Id], [t].[OneToOne_Required_PK_Date], [t].[OneToOne_Required_PK_Level1_Optional_Id], [t].[OneToOne_Required_PK_Level1_Required_Id], [t].[OneToOne_Required_PK_Name], [t].[OneToOne_Required_PK_OneToOne_Optional_PK_InverseId]
		FROM [Level1] AS [t]
		WHERE [t].[Id] IS NOT NULL
	) AS [t0] ON [l1].[Id] = [t0].[OneToOne_Required_PK_Level1_Optional_Id]
	WHERE ([t0].[OneToOne_Required_PK_Name] <> N'Foo') OR [t0].[OneToOne_Required_PK_Name] IS NULL
) AS [t1]

note that projected columns come from the joined entity (l2), instead of expected l1.

If the predicate is removed, or changed so that l1.Name is being used, everything works fine:

SELECT [t1].[Id]
            FROM (
                SELECT DISTINCT [l1].*
                FROM [Level1] AS [l1]
                LEFT JOIN (
                    SELECT [t].*
                    FROM [Level1] AS [t]
                    WHERE [t].[Id] IS NOT NULL
                ) AS [t0] ON [l1].[Id] = [t0].[OneToOne_Required_PK_Level1_Optional_Id]
                WHERE ([l1].[Name] <> N'Foo') OR [l1].[Name] IS NULL
            ) AS [t1]

[smitpatel]

Check materialization status for l1/l2. Probably l1 is not materialized (since we are picking up l1.id only & if l2 is materialized then we would copy over all projections while creating left join.

[maumar]

that's one issue for sure (l2 shouldn't be materialized), but on top of that, we should at least have l1.Id in the final projection - we do not. Instead, [t1].Id is present twice (pos 0 and pos 6), and the pos 6 one is used in the final client selector

[smitpatel]

Both t1.Id could have different structure. Basically when we are binding l1.Id, we could try to find column Id property inside table t1 and that gives us t1.Id (since internal projections are incorrect, the external one was bound incorrectly too.

[maumar]

problem surfaces because RequiresMaterializationExpressionVisitor is not able to find entity type for [l2] qsre in the l2.Name != 'Foo'. For non-owned case we get that entity directly based on qsre type, but for owned we need to go thru navigation. Problem is that there is no navigation because the expression is a manually created groupjoin

[smitpatel]

If l2 is owned type then how can we do DbSet<Level2>?

[maumar]

its just how expression printer displays stuff

[maumar]

fixed in 552f70a