3.0 generates invalid SQL

[remiskaune]

After upgrade to 3.0 generates wrong sql (EF 2.2 was good)

var q = db.Vaistas
                .Where(e => e.GrupeId != 5)
                .Where(e => e.EspbiKey != null && e.EspbiUpdatedAt != null)
                .Where(e => e.BendrinisPavadinimas.VaistoRaktinioZodzioVieta.Any(rzv => rzv.RaktinisZodis.Pavadinimas.StartsWith(term)) ||
                    e.FirminisPavadinimas.VaistoRaktinioZodzioVieta.Any(rzv => rzv.RaktinisZodis.Pavadinimas.StartsWith(term)))
.Select(e => new
                    {
                        e.BendrinisPavadinimasId,
                        BendrinisPavadinimas = e.BendrinisPavadinimas.Pavadinimas,
                        e.FormaId,
                        Forma = e.Forma.Pavadinimas
                    })
                    .Distinct()
                    .ToListAsync();

EF 3.0 generates SQL:

SELECT DISTINCT [v].[BendrinisPavadinimasId], [v0].[Pavadinimas] AS [BendrinisPavadinimas], [v].[FormaId], [v2].[Pavadinimas] AS [Forma]
FROM [Vaistas] AS [v]
LEFT JOIN [VaistoBendrinisPavadinimas] AS [v0] ON [v].[BendrinisPavadinimasId] = [v0].[Id]
LEFT JOIN [VaistoFirminisPavadinimas] AS [v1] ON [v].[FirminisPavadinimasId] = [v1].[Id]
LEFT JOIN [VaistoForma] AS [v2] ON [v].[FormaId] = [v2].[Id]
WHERE (([v].[GrupeId] <> 5) AND ([v].[EspbiKey] IS NOT NULL AND [v].[EspbiUpdatedAt] IS NOT NULL)) AND (EXISTS (
    SELECT 1
    FROM [VaistoRaktinioZodzioVieta] AS [v3]
    INNER JOIN [VaistoRaktinisZodis] AS [v4] ON [v3].[RaktinisZodisId] = [v4].[Id]
    WHERE ((([v0].[Id] = [v3].[BendrinisPavadinimasId]) 
-- second AND wasn't in EF 2.2 
AND ([v0].[Id] IS NOT NULL AND [v3].[BendrinisPavadinimasId] IS NOT NULL)) 
		OR ([v0].[Id] IS NULL AND [v3].[BendrinisPavadinimasId] IS NULL)) 
	AND 
-- second expression bigger then EF 2.2 with additional checking
	(((@__get_Item_0 = N'') AND @__get_Item_0 IS NOT NULL) OR (@__get_Item_0 IS NOT NULL AND (([v4].[Pavadinimas] LIKE [v4].[Pavadinimas] + N'%') AND (((LEFT([v4].[Pavadinimas], LEN(@__get_Item_0)) = @__get_Item_0) AND (LEFT([v4].[Pavadinimas], LEN(@__get_Item_0)) IS NOT NULL AND @__get_Item_0 IS NOT NULL)) OR (LEFT([v4].[Pavadinimas], LEN(@__get_Item_0)) IS NULL AND @__get_Item_0 IS NULL)))))) OR EXISTS (
    SELECT 1
    FROM [VaistoRaktinioZodzioVieta] AS [v5]
    INNER JOIN [VaistoRaktinisZodis] AS [v6] ON [v5].[RaktinisZodisId] = [v6].[Id]
    WHERE ((([v1].[Id] = [v5].[FirminisPavadinimasId]) AND ([v1].[Id] IS NOT NULL AND [v5].[FirminisPavadinimasId] IS NOT NULL)) OR ([v1].[Id] IS NULL AND [v5].[FirminisPavadinimasId] IS NULL)) AND (((@__get_Item_0 = N'') AND @__get_Item_0 IS NOT NULL) OR (@__get_Item_0 IS NOT NULL AND (([v6].[Pavadinimas] LIKE [v6].[Pavadinimas] + N'%') AND (((LEFT([v6].[Pavadinimas], LEN(@__get_Item_0)) = @__get_Item_0) AND (LEFT([v6].[Pavadinimas], LEN(@__get_Item_0)) IS NOT NULL AND @__get_Item_0 IS NOT NULL)) OR (LEFT([v6].[Pavadinimas], LEN(@__get_Item_0)) IS NULL AND @__get_Item_0 IS NULL)))))))

EF 2.2 generates

SELECT DISTINCT [e].[BendrinisPavadinimasId], [e.BendrinisPavadinimas].[Pavadinimas] AS [BendrinisPavadinimas], [e].[FormaId], [e.Forma].[Pavadinimas] AS [Forma]
FROM [Vaistas] AS [e]
LEFT JOIN [VaistoForma] AS [e.Forma] ON [e].[FormaId] = [e.Forma].[Id]
LEFT JOIN [VaistoFirminisPavadinimas] AS [e.FirminisPavadinimas] ON [e].[FirminisPavadinimasId] = [e.FirminisPavadinimas].[Id]
LEFT JOIN [VaistoBendrinisPavadinimas] AS [e.BendrinisPavadinimas] ON [e].[BendrinisPavadinimasId] = [e.BendrinisPavadinimas].[Id]
WHERE (([e].[GrupeId] <> 5) AND ([e].[EspbiKey] IS NOT NULL AND [e].[EspbiUpdatedAt] IS NOT NULL)) AND (EXISTS (
    SELECT 1
    FROM [VaistoRaktinioZodzioVieta] AS [rzv]
    INNER JOIN [VaistoRaktinisZodis] AS [rzv.RaktinisZodis] ON [rzv].[RaktinisZodisId] = [rzv.RaktinisZodis].[Id]
    WHERE (([rzv.RaktinisZodis].[Pavadinimas] LIKE @__get_Item_0 + N'%' AND (LEFT([rzv.RaktinisZodis].[Pavadinimas], LEN(@__get_Item_0)) = @__get_Item_0)) 
		OR (@__get_Item_0 = N'')) 
		AND ([e.BendrinisPavadinimas].[Id] = [rzv].[BendrinisPavadinimasId])) OR EXISTS (
    SELECT 1
    FROM [VaistoRaktinioZodzioVieta] AS [rzv0]
    INNER JOIN [VaistoRaktinisZodis] AS [rzv.RaktinisZodis0] ON [rzv0].[RaktinisZodisId] = [rzv.RaktinisZodis0].[Id]
    WHERE (([rzv.RaktinisZodis0].[Pavadinimas] LIKE @__get_Item_0 + N'%' AND (LEFT([rzv.RaktinisZodis0].[Pavadinimas], LEN(@__get_Item_0)) = @__get_Item_0)) OR (@__get_Item_0 = N'')) AND ([e.FirminisPavadinimas].[Id] = [rzv0].[FirminisPavadinimasId])))

EF 3.0 generates bigger SQL. One of them additional

AND ([v0].[Id] IS NOT NULL AND [v3].[BendrinisPavadinimasId] IS NOT NULL)) 
		OR ([v0].[Id] IS NULL AND [v3].[BendrinisPavadinimasId] IS NULL)

and return wrong data

Further technical details

EF Core version: 3.0
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET Core 3.0
Operating system: Windows 10
IDE: Visual Studio 2019 16.3

[smitpatel]

Duplicate of #17531