search

dotnet / efcore

EF Core is a modern object-database mapper for .NET. It supports LINQ queries, change tracking, updates, and schema migrations.
https://docs.microsoft.com/ef/
MIT License
13.67k stars 3.16k forks source link

Subquery with scalar single results are not lifted when projection has unmapped property #11835

Closed berets76 closed 4 years ago

berets76 commented 6 years ago

I've a [Guest] entity with two navigation properties, [Country] and [City], each of which has a [CountryName]/[CityName] navigation property (each Country or City has different Names based on language).

public class Country
{
    [Key,Column(Order = 0)]
    public Guid TenantID { get; set; }
    [Key, Column(Order = 1)]
    public Guid ID { get; set; }

    public virtual List<CountryName> Names { get; set; }
}
public class CountryName
{
    [Key, Column(Order = 0)]
    public Guid TenantID { get; set; }
    [Key,Column(Order = 1)]
    public Guid CountryID { get; set; }
    [Key, Column(Order = 2)]
    [StringLength(3)]
    public string LanguageID { get; set; }
    [StringLength(255)]
    public string Name { get; set; }

    public virtual Country Country { get; set; }
}

public class City
{
    [Key, Column(Order = 0)]
    public Guid TenantID { get; set; }
    [Key, Column(Order = 1)]
    public Guid CountryID { get; set; }
    [Key, Column(Order = 2)]
    public Guid ID { get; set; }

    public virtual List<CityName> Names { get; set; }
}
public class CityName
{
    [Key, Column(Order = 0)]
    public Guid TenantID { get; set; }
    [Key, Column(Order = 1)]
    public Guid CountryID { get; set; }
    [Key,Column(Order = 2)]
    public Guid CityID { get; set; }
    [Key, Column(Order = 3)]
    [StringLength(3)]
    public string LanguageID { get; set; }
    [StringLength(255)]
    public string Name { get; set; }

    public virtual City City { get; set; }
}

public class Guest
{
    [Key, Column(Order = 0)]
    public Guid TenantID { get; set; }
    [Key, Column(Order = 1)]
    public Guid ID { get; set; }
    public Guid? BirthCountryID { get; set; }
    public virtual Country BirthCountry { get; set; }
    public Guid? BirthCityID { get; set; }
    public virtual City BirthCity { get; set; }
}

[CountryName] and [CityName] have a query filter set in context OnModelCreating.

// Country
modelBuilder.Entity<Country>().HasKey(c => new { c.TenantID, c.ID });
modelBuilder.Entity<Country>().Property(p => p.ID).ValueGeneratedOnAdd().HasDefaultValueSql("newsequentialid()");
// CountryName
modelBuilder.Entity<CountryName>().HasKey(c => new { c.TenantID, c.CountryID, c.LanguageID });
modelBuilder.Entity<CountryName>().HasOne(ho => ho.Country).WithMany(wm => wm.Names).HasForeignKey(fk => new { fk.TenantID, fk.CountryID }).OnDelete(DeleteBehavior.Restrict);
modelBuilder.Entity<CountryName>().HasQueryFilter(qf => qf.LanguageID == LanguageID);

// City
modelBuilder.Entity<City>().HasKey(c => new { c.TenantID, c.CountryID, c.ID });
modelBuilder.Entity<City>().Property(p => p.ID).ValueGeneratedOnAdd().HasDefaultValueSql("newsequentialid()");
// CityName
modelBuilder.Entity<CityName>().HasKey(c => new { c.TenantID, c.CountryID, c.CityID, c.LanguageID });
modelBuilder.Entity<CityName>().HasOne(ho => ho.City).WithMany(wm => wm.Names).HasForeignKey(fk => new { fk.TenantID, fk.CountryID, fk.CityID }).OnDelete(DeleteBehavior.Restrict);
modelBuilder.Entity<CityName>().HasQueryFilter(qf => qf.LanguageID == LanguageID);

// Guest
modelBuilder.Entity<Guest>().HasKey(c => new { c.TenantID, c.ID });
modelBuilder.Entity<Guest>().Property(p => p.ID).ValueGeneratedOnAdd().HasDefaultValueSql("newsequentialid()");
modelBuilder.Entity<Guest>().HasOne(ho => ho.BirthCountry).WithMany().HasForeignKey(fk => new { fk.TenantID, fk.BirthCountryID }).OnDelete(DeleteBehavior.Restrict);
modelBuilder.Entity<Guest>().HasOne(ho => ho.BirthCity).WithMany().HasForeignKey(fk => new { fk.TenantID, fk.BirthCountryID, fk.BirthCityID }).OnDelete(DeleteBehavior.Restrict);

MY QUERY

var data = _context.Guests
        .Where(w => w.TenantID == TenantID && w.Cancelled == null  && 
        (w.VAT.Contains(Filter) || w.First.Contains(Filter) || w.Last.Contains(Filter) || w.BirthDate.ToString().Contains(Filter) ||
        w.ResidenceAddress.Contains(Filter) || w.BirthCountry.Names.Any(a => a.Name.Contains(Filter)) || 
        w.BirthCity.Names.Any(a => a.Name.Contains(Filter))))
        .OrderBy(o => o.ID)
        .Skip(Start).Take(Count)
        .Select(s => new
        {
            ID = s.ID,
            Name = s.FullName,
            VAT = s.VAT,
            BirthDate = s.BirthDate.ToString("yyyy/MM/dd"),
            ResidenceAddress = s.ResidenceAddress,
            BirthCountryName = s.BirthCountry.Names[0].Name,
            BirthCityName = s.BirthCity.Names[0].Name,
            Reservations = s.ReservationsNumber,
            Bookable = bookable,
            IsHospitalized = s.IsHospitalized,
            IsPresent = s.IsPresent
        })
        .AsNoTracking()
        .ToList();

GENERATED SQL

/* MAIN */
SELECT [w].[TenantID], [w].[ID], [w].[AddUserID], [w].[Added], [w].[BirthCityID], [w].[BirthCountryID], [w].[BirthDate], [w].[BirthState], [w].[CancelUserID], [w].[Cancelled], [w].[DeathDate], [w].[First], [w].[IsHospitalized], [w].[IsPresent], [w].[Last], [w].[ReservationsNumber] AS [Reservations], [w].[ResidenceAddress], [w].[ResidenceAddressNumber], [w].[ResidenceAddressState], [w].[ResidenceAddressZip], [w].[ResidenceCityID], [w].[ResidenceCountryID], [w].[SexID], [w].[UpdateUserID], [w].[Updated], [w].[VAT], [w.BirthCountry].[TenantID], [w.BirthCountry].[ID], [w.BirthCity].[TenantID], [w.BirthCity].[CountryID], [w.BirthCity].[ID]
FROM [Guests] AS [w]
LEFT JOIN [Cities] AS [w.BirthCity] ON (([w].[TenantID] = [w.BirthCity].[TenantID]) AND ([w].[BirthCountryID] = [w.BirthCity].[CountryID])) AND ([w].[BirthCityID] = [w.BirthCity].[ID])
LEFT JOIN [Countries] AS [w.BirthCountry] ON ([w].[TenantID] = [w.BirthCountry].[TenantID]) AND ([w].[BirthCountryID] = [w.BirthCountry].[ID])
WHERE (([w].[TenantID] = @__TenantID_0) AND [w].[Cancelled] IS NULL) AND ((((((((CHARINDEX(@__Filter_1, [w].[VAT]) > 0) OR (@__Filter_1 = N'')) OR ((CHARINDEX(@__Filter_2, [w].[First]) > 0) OR (@__Filter_2 = N''))) OR ((CHARINDEX(@__Filter_3, [w].[Last]) > 0) OR (@__Filter_3 = N''))) OR ((CHARINDEX(@__Filter_4, CONVERT(VARCHAR(100), [w].[BirthDate])) > 0) OR (@__Filter_4 = N''))) OR ((CHARINDEX(@__Filter_5, [w].[ResidenceAddress]) > 0) OR (@__Filter_5 = N''))) OR EXISTS (
    SELECT 1
    FROM [CountryNames] AS [qf]
    WHERE (([qf].[LanguageID] = @__ef_filter__LanguageID_0) AND ((CHARINDEX(@__Filter_6, [qf].[Name]) > 0) OR (@__Filter_6 = N''))) AND (([w.BirthCountry].[TenantID] = [qf].[TenantID]) AND ([w.BirthCountry].[ID] = [qf].[CountryID])))) OR EXISTS (
    SELECT 1
    FROM [CityNames] AS [qf0]
    WHERE (([qf0].[LanguageID] = @__ef_filter__LanguageID_1) AND ((CHARINDEX(@__Filter_7, [qf0].[Name]) > 0) OR (@__Filter_7 = N''))) AND ((([w.BirthCity].[TenantID] = [qf0].[TenantID]) AND ([w.BirthCity].[CountryID] = [qf0].[CountryID])) AND ([w.BirthCity].[ID] = [qf0].[CityID]))))
ORDER BY [w].[ID], [w].[TenantID], [w.BirthCountry].[TenantID], [w.BirthCountry].[ID], [w.BirthCity].[TenantID], [w.BirthCity].[CountryID], [w.BirthCity].[ID]
OFFSET @__p_8 ROWS FETCH NEXT @__p_9 ROWS ONLY

/* FOR >>>  [BirthCountryName = s.BirthCountry.Names[0].Name] */
SELECT [w.BirthCountry.Names].[TenantID], [w.BirthCountry.Names].[CountryID], [w.BirthCountry.Names].[LanguageID], [w.BirthCountry.Names].[Name], [t].[ID], [t].[TenantID], [t].[TenantID0], [t].[ID0]
FROM [CountryNames] AS [w.BirthCountry.Names]
INNER JOIN (
    SELECT [w0].[ID], [w0].[TenantID], [w.BirthCountry0].[TenantID] AS [TenantID0], [w.BirthCountry0].[ID] AS [ID0]
    FROM [Guests] AS [w0]
    LEFT JOIN [Cities] AS [w.BirthCity0] ON (([w0].[TenantID] = [w.BirthCity0].[TenantID]) AND ([w0].[BirthCountryID] = [w.BirthCity0].[CountryID])) AND ([w0].[BirthCityID] = [w.BirthCity0].[ID])
    LEFT JOIN [Countries] AS [w.BirthCountry0] ON ([w0].[TenantID] = [w.BirthCountry0].[TenantID]) AND ([w0].[BirthCountryID] = [w.BirthCountry0].[ID])
    WHERE (([w0].[TenantID] = @__TenantID_0) AND [w0].[Cancelled] IS NULL) AND ((((((((CHARINDEX(@__Filter_1, [w0].[VAT]) > 0) OR (@__Filter_1 = N'')) OR ((CHARINDEX(@__Filter_2, [w0].[First]) > 0) OR (@__Filter_2 = N''))) OR ((CHARINDEX(@__Filter_3, [w0].[Last]) > 0) OR (@__Filter_3 = N''))) OR ((CHARINDEX(@__Filter_4, CONVERT(VARCHAR(100), [w0].[BirthDate])) > 0) OR (@__Filter_4 = N''))) OR ((CHARINDEX(@__Filter_5, [w0].[ResidenceAddress]) > 0) OR (@__Filter_5 = N''))) OR EXISTS (
        SELECT 1
        FROM [CountryNames] AS [qf1]
        WHERE (([qf1].[LanguageID] = @__ef_filter__LanguageID_0) AND ((CHARINDEX(@__Filter_6, [qf1].[Name]) > 0) OR (@__Filter_6 = N''))) AND (([w.BirthCountry0].[TenantID] = [qf1].[TenantID]) AND ([w.BirthCountry0].[ID] = [qf1].[CountryID])))) OR EXISTS (
        SELECT 1
        FROM [CityNames] AS [qf2]
        WHERE (([qf2].[LanguageID] = @__ef_filter__LanguageID_1) AND ((CHARINDEX(@__Filter_7, [qf2].[Name]) > 0) OR (@__Filter_7 = N''))) AND ((([w.BirthCity0].[TenantID] = [qf2].[TenantID]) AND ([w.BirthCity0].[CountryID] = [qf2].[CountryID])) AND ([w.BirthCity0].[ID] = [qf2].[CityID]))))
    ORDER BY [w0].[ID], [w0].[TenantID], [w.BirthCountry0].[TenantID], [w.BirthCountry0].[ID]
    OFFSET @__p_8 ROWS FETCH NEXT @__p_9 ROWS ONLY
) AS [t] ON ([w.BirthCountry.Names].[TenantID] = [t].[TenantID0]) AND ([w.BirthCountry.Names].[CountryID] = [t].[ID0])
WHERE [w.BirthCountry.Names].[LanguageID] = @__ef_filter__LanguageID_2
ORDER BY [t].[ID], [t].[TenantID], [t].[TenantID0], [t].[ID0]

/* FOR >>> [BirthCityName = s.BirthCity.Names[0].Name]*/
SELECT [w.BirthCity.Names].[TenantID], [w.BirthCity.Names].[CountryID], [w.BirthCity.Names].[CityID], [w.BirthCity.Names].[LanguageID], [w.BirthCity.Names].[Name], [t0].[ID], [t0].[TenantID], [t0].[TenantID0], [t0].[CountryID], [t0].[ID0]
FROM [CityNames] AS [w.BirthCity.Names]
INNER JOIN (
    SELECT [w1].[ID], [w1].[TenantID], [w.BirthCity1].[TenantID] AS [TenantID0], [w.BirthCity1].[CountryID], [w.BirthCity1].[ID] AS [ID0]
    FROM [Guests] AS [w1]
    LEFT JOIN [Cities] AS [w.BirthCity1] ON (([w1].[TenantID] = [w.BirthCity1].[TenantID]) AND ([w1].[BirthCountryID] = [w.BirthCity1].[CountryID])) AND ([w1].[BirthCityID] = [w.BirthCity1].[ID])
    LEFT JOIN [Countries] AS [w.BirthCountry1] ON ([w1].[TenantID] = [w.BirthCountry1].[TenantID]) AND ([w1].[BirthCountryID] = [w.BirthCountry1].[ID])
    WHERE (([w1].[TenantID] = @__TenantID_0) AND [w1].[Cancelled] IS NULL) AND ((((((((CHARINDEX(@__Filter_1, [w1].[VAT]) > 0) OR (@__Filter_1 = N'')) OR ((CHARINDEX(@__Filter_2, [w1].[First]) > 0) OR (@__Filter_2 = N''))) OR ((CHARINDEX(@__Filter_3, [w1].[Last]) > 0) OR (@__Filter_3 = N''))) OR ((CHARINDEX(@__Filter_4, CONVERT(VARCHAR(100), [w1].[BirthDate])) > 0) OR (@__Filter_4 = N''))) OR ((CHARINDEX(@__Filter_5, [w1].[ResidenceAddress]) > 0) OR (@__Filter_5 = N''))) OR EXISTS (
        SELECT 1
        FROM [CountryNames] AS [qf3]
        WHERE (([qf3].[LanguageID] = @__ef_filter__LanguageID_0) AND ((CHARINDEX(@__Filter_6, [qf3].[Name]) > 0) OR (@__Filter_6 = N''))) AND (([w.BirthCountry1].[TenantID] = [qf3].[TenantID]) AND ([w.BirthCountry1].[ID] = [qf3].[CountryID])))) OR EXISTS (
        SELECT 1
        FROM [CityNames] AS [qf4]
        WHERE (([qf4].[LanguageID] = @__ef_filter__LanguageID_1) AND ((CHARINDEX(@__Filter_7, [qf4].[Name]) > 0) OR (@__Filter_7 = N''))) AND ((([w.BirthCity1].[TenantID] = [qf4].[TenantID]) AND ([w.BirthCity1].[CountryID] = [qf4].[CountryID])) AND ([w.BirthCity1].[ID] = [qf4].[CityID]))))
    ORDER BY [w1].[ID], [w1].[TenantID], [w.BirthCity1].[TenantID], [w.BirthCity1].[CountryID], [w.BirthCity1].[ID]
    OFFSET @__p_8 ROWS FETCH NEXT @__p_9 ROWS ONLY
) AS [t0] ON (([w.BirthCity.Names].[TenantID] = [t0].[TenantID0]) AND ([w.BirthCity.Names].[CountryID] = [t0].[CountryID])) AND ([w.BirthCity.Names].[CityID] = [t0].[ID0])
WHERE [w.BirthCity.Names].[LanguageID] = @__ef_filter__LanguageID_3
ORDER BY [t0].[ID], [t0].[TenantID], [t0].[TenantID0], [t0].[CountryID], [t0].[ID0]

QUESTION

Actually 3 query are generated, 3 resultset are returned from SQL Server (Azure) and then client side merged.

Maybe this query could be translated to a single SQL query ? Or at least select only [Name] field from nested navigation properties ? (specially on mobile devices any byte saved is a great achievement)

I tried BirthCityName = s.BirthCity.Names.SingleOrDefault().Name or BirthCityName = s.BirthCity.Names.FirstOrDefault().Name instead of BirthCityName = s.BirthCity.Names[0].Name but both aren't translated and are resolved client side, generating [Count] (Take parameter) * 2 (Country and City) more SQL queries, two for each guest retrieved (taking 25 records EF Core generate 51 SQL queries)

Further technical details

EF Core version: 2.1.0-preview2-final Database Provider: Microsoft.EntityFrameworkCore.SqlServer Operating system: Windows 10 64 bit IDE: Visual Studio 2017 15.7.0 Preview 5

smitpatel commented 6 years ago

Array index wouldn't translate. SingleOrDefault would be client side because it needs to throw if there are more than one element. FirstOrDefault is expected to translate to server and lift the query.

berets76 commented 6 years ago

It doesn't translate, I change from array index to FirstOrDefault only BirthCityName and this is the result

Microsoft.EntityFrameworkCore.Query:Warning: Query: '(from CityName <generated>_1 in [w].BirthCity.Names select [<generated>_1].Name).FirstOrDefault()' uses First/FirstOrDefault/Last/LastOrDefault operation without OrderBy and filter which may lead to unpredictable results.
Microsoft.EntityFrameworkCore.Database.Command:Information: Executed DbCommand (131ms) [Parameters=[@__TenantID_0='?' (DbType = Guid), @__Filter_1='?' (Size = 4000), @__Filter_2='?' (Size = 4000), @__Filter_3='?' (Size = 4000), @__Filter_4='?' (Size = 4000), @__Filter_5='?' (Size = 4000), @__ef_filter__LanguageID_0='?' (Size = 3), @__Filter_6='?' (Size = 4000), @__ef_filter__LanguageID_1='?' (Size = 3), @__Filter_7='?' (Size = 4000), @__Filter_8='?' (Size = 4000), @__p_9='?' (DbType = Int32), @__p_10='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
SELECT [w].[TenantID], [w].[ID], [w].[AddUserID], [w].[Added], [w].[BirthCityID], [w].[BirthCountryID], [w].[BirthDate], [w].[BirthState], [w].[CancelUserID], [w].[Cancelled], [w].[DeathDate], [w].[FacilityCode], [w].[First], [w].[IsHospitalized], [w].[IsPresent], [w].[Last], [w].[ReservationsNumber] AS [Reservations], [w].[ResidenceAddress], [w].[ResidenceAddressNumber], [w].[ResidenceAddressState], [w].[ResidenceAddressZip], [w].[ResidenceCityID], [w].[ResidenceCountryID], [w].[SexID], [w].[UpdateUserID], [w].[Updated], [w].[VAT], [w.BirthCountry].[TenantID], [w.BirthCountry].[ID], [w.BirthCity].[TenantID], [w.BirthCity].[CountryID], [w.BirthCity].[ID]
FROM [Guests] AS [w]
LEFT JOIN [Cities] AS [w.BirthCity] ON (([w].[TenantID] = [w.BirthCity].[TenantID]) AND ([w].[BirthCountryID] = [w.BirthCity].[CountryID])) AND ([w].[BirthCityID] = [w.BirthCity].[ID])
LEFT JOIN [Countries] AS [w.BirthCountry] ON ([w].[TenantID] = [w.BirthCountry].[TenantID]) AND ([w].[BirthCountryID] = [w.BirthCountry].[ID])
WHERE (([w].[TenantID] = @__TenantID_0) AND [w].[Cancelled] IS NULL) AND (((((((((CHARINDEX(@__Filter_1, [w].[VAT]) > 0) OR (@__Filter_1 = N'')) OR ((CHARINDEX(@__Filter_2, [w].[First]) > 0) OR (@__Filter_2 = N''))) OR ((CHARINDEX(@__Filter_3, [w].[Last]) > 0) OR (@__Filter_3 = N''))) OR ((CHARINDEX(@__Filter_4, CONVERT(VARCHAR(100), [w].[BirthDate])) > 0) OR (@__Filter_4 = N''))) OR ((CHARINDEX(@__Filter_5, [w].[ResidenceAddress]) > 0) OR (@__Filter_5 = N''))) OR EXISTS (
    SELECT 1
    FROM [CountryNames] AS [qf]
    WHERE (([qf].[LanguageID] = @__ef_filter__LanguageID_0) AND ((CHARINDEX(@__Filter_6, [qf].[Name]) > 0) OR (@__Filter_6 = N''))) AND (([w.BirthCountry].[TenantID] = [qf].[TenantID]) AND ([w.BirthCountry].[ID] = [qf].[CountryID])))) OR EXISTS (
    SELECT 1
    FROM [CityNames] AS [qf0]
    WHERE (([qf0].[LanguageID] = @__ef_filter__LanguageID_1) AND ((CHARINDEX(@__Filter_7, [qf0].[Name]) > 0) OR (@__Filter_7 = N''))) AND ((([w.BirthCity].[TenantID] = [qf0].[TenantID]) AND ([w.BirthCity].[CountryID] = [qf0].[CountryID])) AND ([w.BirthCity].[ID] = [qf0].[CityID])))) OR ((CHARINDEX(@__Filter_8, [w].[FacilityCode]) > 0) OR (@__Filter_8 = N'')))
ORDER BY [w].[ID], [w].[TenantID], [w.BirthCountry].[TenantID], [w.BirthCountry].[ID]
OFFSET @__p_9 ROWS FETCH NEXT @__p_10 ROWS ONLY
Microsoft.EntityFrameworkCore.Database.Command:Information: Executed DbCommand (92ms) [Parameters=[@__TenantID_0='?' (DbType = Guid), @__Filter_1='?' (Size = 4000), @__Filter_2='?' (Size = 4000), @__Filter_3='?' (Size = 4000), @__Filter_4='?' (Size = 4000), @__Filter_5='?' (Size = 4000), @__ef_filter__LanguageID_0='?' (Size = 3), @__Filter_6='?' (Size = 4000), @__ef_filter__LanguageID_1='?' (Size = 3), @__Filter_7='?' (Size = 4000), @__Filter_8='?' (Size = 4000), @__p_9='?' (DbType = Int32), @__p_10='?' (DbType = Int32), @__ef_filter__LanguageID_2='?' (Size = 3)], CommandType='Text', CommandTimeout='30']
SELECT [w.BirthCountry.Names].[TenantID], [w.BirthCountry.Names].[CountryID], [w.BirthCountry.Names].[LanguageID], [w.BirthCountry.Names].[Name], [t].[ID], [t].[TenantID], [t].[TenantID0], [t].[ID0]
FROM [CountryNames] AS [w.BirthCountry.Names]
INNER JOIN (
    SELECT [w0].[ID], [w0].[TenantID], [w.BirthCountry0].[TenantID] AS [TenantID0], [w.BirthCountry0].[ID] AS [ID0]
    FROM [Guests] AS [w0]
    LEFT JOIN [Cities] AS [w.BirthCity0] ON (([w0].[TenantID] = [w.BirthCity0].[TenantID]) AND ([w0].[BirthCountryID] = [w.BirthCity0].[CountryID])) AND ([w0].[BirthCityID] = [w.BirthCity0].[ID])
    LEFT JOIN [Countries] AS [w.BirthCountry0] ON ([w0].[TenantID] = [w.BirthCountry0].[TenantID]) AND ([w0].[BirthCountryID] = [w.BirthCountry0].[ID])
    WHERE (([w0].[TenantID] = @__TenantID_0) AND [w0].[Cancelled] IS NULL) AND (((((((((CHARINDEX(@__Filter_1, [w0].[VAT]) > 0) OR (@__Filter_1 = N'')) OR ((CHARINDEX(@__Filter_2, [w0].[First]) > 0) OR (@__Filter_2 = N''))) OR ((CHARINDEX(@__Filter_3, [w0].[Last]) > 0) OR (@__Filter_3 = N''))) OR ((CHARINDEX(@__Filter_4, CONVERT(VARCHAR(100), [w0].[BirthDate])) > 0) OR (@__Filter_4 = N''))) OR ((CHARINDEX(@__Filter_5, [w0].[ResidenceAddress]) > 0) OR (@__Filter_5 = N''))) OR EXISTS (
        SELECT 1
        FROM [CountryNames] AS [qf1]
        WHERE (([qf1].[LanguageID] = @__ef_filter__LanguageID_0) AND ((CHARINDEX(@__Filter_6, [qf1].[Name]) > 0) OR (@__Filter_6 = N''))) AND (([w.BirthCountry0].[TenantID] = [qf1].[TenantID]) AND ([w.BirthCountry0].[ID] = [qf1].[CountryID])))) OR EXISTS (
        SELECT 1
        FROM [CityNames] AS [qf2]
        WHERE (([qf2].[LanguageID] = @__ef_filter__LanguageID_1) AND ((CHARINDEX(@__Filter_7, [qf2].[Name]) > 0) OR (@__Filter_7 = N''))) AND ((([w.BirthCity0].[TenantID] = [qf2].[TenantID]) AND ([w.BirthCity0].[CountryID] = [qf2].[CountryID])) AND ([w.BirthCity0].[ID] = [qf2].[CityID])))) OR ((CHARINDEX(@__Filter_8, [w0].[FacilityCode]) > 0) OR (@__Filter_8 = N'')))
    ORDER BY [w0].[ID], [w0].[TenantID], [w.BirthCountry0].[TenantID], [w.BirthCountry0].[ID]
    OFFSET @__p_9 ROWS FETCH NEXT @__p_10 ROWS ONLY
) AS [t] ON ([w.BirthCountry.Names].[TenantID] = [t].[TenantID0]) AND ([w.BirthCountry.Names].[CountryID] = [t].[ID0])
WHERE [w.BirthCountry.Names].[LanguageID] = @__ef_filter__LanguageID_2
ORDER BY [t].[ID], [t].[TenantID], [t].[TenantID0], [t].[ID0]
Microsoft.EntityFrameworkCore.Database.Command:Information: Executed DbCommand (79ms) [Parameters=[@__ef_filter__LanguageID_3='?' (Size = 3), @_outer_TenantID='?' (DbType = Guid), @_outer_CountryID='?' (DbType = Guid), @_outer_ID='?' (DbType = Guid)], CommandType='Text', CommandTimeout='30']
SELECT TOP(1) [qf4].[Name]
FROM [CityNames] AS [qf4]
WHERE ([qf4].[LanguageID] = @__ef_filter__LanguageID_3) AND (((@_outer_TenantID = [qf4].[TenantID]) AND (@_outer_CountryID = [qf4].[CountryID])) AND (@_outer_ID = [qf4].[CityID]))
Microsoft.EntityFrameworkCore.Database.Command:Information: Executed DbCommand (67ms) [Parameters=[@__ef_filter__LanguageID_3='?' (Size = 3), @_outer_TenantID='?' (DbType = Guid), @_outer_CountryID='?' (DbType = Guid), @_outer_ID='?' (DbType = Guid)], CommandType='Text', CommandTimeout='30']
SELECT TOP(1) [qf4].[Name]
FROM [CityNames] AS [qf4]
WHERE ([qf4].[LanguageID] = @__ef_filter__LanguageID_3) AND (((@_outer_TenantID = [qf4].[TenantID]) AND (@_outer_CountryID = [qf4].[CountryID])) AND (@_outer_ID = [qf4].[CityID]))
Microsoft.EntityFrameworkCore.Database.Command:Information: Executed DbCommand (66ms) [Parameters=[@__ef_filter__LanguageID_3='?' (Size = 3), @_outer_TenantID='?' (DbType = Guid), @_outer_CountryID='?' (DbType = Guid), @_outer_ID='?' (DbType = Guid)], CommandType='Text', CommandTimeout='30']
SELECT TOP(1) [qf4].[Name]
FROM [CityNames] AS [qf4]
WHERE ([qf4].[LanguageID] = @__ef_filter__LanguageID_3) AND (((@_outer_TenantID = [qf4].[TenantID]) AND (@_outer_CountryID = [qf4].[CountryID])) AND (@_outer_ID = [qf4].[CityID]))
Microsoft.EntityFrameworkCore.Database.Command:Information: Executed DbCommand (80ms) [Parameters=[@__ef_filter__LanguageID_3='?' (Size = 3), @_outer_TenantID='?' (DbType = Guid), @_outer_CountryID='?' (DbType = Guid), @_outer_ID='?' (DbType = Guid)], CommandType='Text', CommandTimeout='30']
SELECT TOP(1) [qf4].[Name]
FROM [CityNames] AS [qf4]
WHERE ([qf4].[LanguageID] = @__ef_filter__LanguageID_3) AND (((@_outer_TenantID = [qf4].[TenantID]) AND (@_outer_CountryID = [qf4].[CountryID])) AND (@_outer_ID = [qf4].[CityID]))
Microsoft.EntityFrameworkCore.Database.Command:Information: Executed DbCommand (69ms) [Parameters=[@__ef_filter__LanguageID_3='?' (Size = 3), @_outer_TenantID='?' (DbType = Guid), @_outer_CountryID='?' (DbType = Guid), @_outer_ID='?' (DbType = Guid)], CommandType='Text', CommandTimeout='30']
SELECT TOP(1) [qf4].[Name]
FROM [CityNames] AS [qf4]
WHERE ([qf4].[LanguageID] = @__ef_filter__LanguageID_3) AND (((@_outer_TenantID = [qf4].[TenantID]) AND (@_outer_CountryID = [qf4].[CountryID])) AND (@_outer_ID = [qf4].[CityID]))
Microsoft.EntityFrameworkCore.Database.Command:Information: Executed DbCommand (68ms) [Parameters=[@__ef_filter__LanguageID_3='?' (Size = 3), @_outer_TenantID='?' (DbType = Guid), @_outer_CountryID='?' (DbType = Guid), @_outer_ID='?' (DbType = Guid)], CommandType='Text', CommandTimeout='30']
SELECT TOP(1) [qf4].[Name]
FROM [CityNames] AS [qf4]
WHERE ([qf4].[LanguageID] = @__ef_filter__LanguageID_3) AND (((@_outer_TenantID = [qf4].[TenantID]) AND (@_outer_CountryID = [qf4].[CountryID])) AND (@_outer_ID = [qf4].[CityID]))
Microsoft.EntityFrameworkCore.Database.Command:Information: Executed DbCommand (66ms) [Parameters=[@__ef_filter__LanguageID_3='?' (Size = 3), @_outer_TenantID='?' (DbType = Guid), @_outer_CountryID='?' (DbType = Guid), @_outer_ID='?' (DbType = Guid)], CommandType='Text', CommandTimeout='30']
SELECT TOP(1) [qf4].[Name]
FROM [CityNames] AS [qf4]
WHERE ([qf4].[LanguageID] = @__ef_filter__LanguageID_3) AND (((@_outer_TenantID = [qf4].[TenantID]) AND (@_outer_CountryID = [qf4].[CountryID])) AND (@_outer_ID = [qf4].[CityID]))
Microsoft.EntityFrameworkCore.Database.Command:Information: Executed DbCommand (69ms) [Parameters=[@__ef_filter__LanguageID_3='?' (Size = 3), @_outer_TenantID='?' (DbType = Guid), @_outer_CountryID='?' (DbType = Guid), @_outer_ID='?' (DbType = Guid)], CommandType='Text', CommandTimeout='30']
SELECT TOP(1) [qf4].[Name]
FROM [CityNames] AS [qf4]
WHERE ([qf4].[LanguageID] = @__ef_filter__LanguageID_3) AND (((@_outer_TenantID = [qf4].[TenantID]) AND (@_outer_CountryID = [qf4].[CountryID])) AND (@_outer_ID = [qf4].[CityID]))
Microsoft.EntityFrameworkCore.Database.Command:Information: Executed DbCommand (70ms) [Parameters=[@__ef_filter__LanguageID_3='?' (Size = 3), @_outer_TenantID='?' (DbType = Guid), @_outer_CountryID='?' (DbType = Guid), @_outer_ID='?' (DbType = Guid)], CommandType='Text', CommandTimeout='30']
SELECT TOP(1) [qf4].[Name]
FROM [CityNames] AS [qf4]
WHERE ([qf4].[LanguageID] = @__ef_filter__LanguageID_3) AND (((@_outer_TenantID = [qf4].[TenantID]) AND (@_outer_CountryID = [qf4].[CountryID])) AND (@_outer_ID = [qf4].[CityID]))
Microsoft.EntityFrameworkCore.Database.Command:Information: Executed DbCommand (68ms) [Parameters=[@__ef_filter__LanguageID_3='?' (Size = 3), @_outer_TenantID='?' (DbType = Guid), @_outer_CountryID='?' (DbType = Guid), @_outer_ID='?' (DbType = Guid)], CommandType='Text', CommandTimeout='30']
SELECT TOP(1) [qf4].[Name]
FROM [CityNames] AS [qf4]
WHERE ([qf4].[LanguageID] = @__ef_filter__LanguageID_3) AND (((@_outer_TenantID = [qf4].[TenantID]) AND (@_outer_CountryID = [qf4].[CountryID])) AND (@_outer_ID = [qf4].[CityID]))
Microsoft.EntityFrameworkCore.Database.Command:Information: Executed DbCommand (66ms) [Parameters=[@__ef_filter__LanguageID_3='?' (Size = 3), @_outer_TenantID='?' (DbType = Guid), @_outer_CountryID='?' (DbType = Guid), @_outer_ID='?' (DbType = Guid)], CommandType='Text', CommandTimeout='30']
SELECT TOP(1) [qf4].[Name]
FROM [CityNames] AS [qf4]
WHERE ([qf4].[LanguageID] = @__ef_filter__LanguageID_3) AND (((@_outer_TenantID = [qf4].[TenantID]) AND (@_outer_CountryID = [qf4].[CountryID])) AND (@_outer_ID = [qf4].[CityID]))
Microsoft.EntityFrameworkCore.Database.Command:Information: Executed DbCommand (87ms) [Parameters=[@__ef_filter__LanguageID_3='?' (Size = 3), @_outer_TenantID='?' (DbType = Guid), @_outer_CountryID='?' (DbType = Guid), @_outer_ID='?' (DbType = Guid)], CommandType='Text', CommandTimeout='30']
SELECT TOP(1) [qf4].[Name]
FROM [CityNames] AS [qf4]
WHERE ([qf4].[LanguageID] = @__ef_filter__LanguageID_3) AND (((@_outer_TenantID = [qf4].[TenantID]) AND (@_outer_CountryID = [qf4].[CountryID])) AND (@_outer_ID = [qf4].[CityID]))
Microsoft.EntityFrameworkCore.Database.Command:Information: Executed DbCommand (66ms) [Parameters=[@__ef_filter__LanguageID_3='?' (Size = 3), @_outer_TenantID='?' (DbType = Guid), @_outer_CountryID='?' (DbType = Guid), @_outer_ID='?' (DbType = Guid)], CommandType='Text', CommandTimeout='30']
SELECT TOP(1) [qf4].[Name]
FROM [CityNames] AS [qf4]
WHERE ([qf4].[LanguageID] = @__ef_filter__LanguageID_3) AND (((@_outer_TenantID = [qf4].[TenantID]) AND (@_outer_CountryID = [qf4].[CountryID])) AND (@_outer_ID = [qf4].[CityID]))
Microsoft.EntityFrameworkCore.Database.Command:Information: Executed DbCommand (67ms) [Parameters=[@__ef_filter__LanguageID_3='?' (Size = 3), @_outer_TenantID='?' (DbType = Guid), @_outer_CountryID='?' (DbType = Guid), @_outer_ID='?' (DbType = Guid)], CommandType='Text', CommandTimeout='30']
SELECT TOP(1) [qf4].[Name]
FROM [CityNames] AS [qf4]
WHERE ([qf4].[LanguageID] = @__ef_filter__LanguageID_3) AND (((@_outer_TenantID = [qf4].[TenantID]) AND (@_outer_CountryID = [qf4].[CountryID])) AND (@_outer_ID = [qf4].[CityID]))
Microsoft.EntityFrameworkCore.Database.Command:Information: Executed DbCommand (68ms) [Parameters=[@__ef_filter__LanguageID_3='?' (Size = 3), @_outer_TenantID='?' (DbType = Guid), @_outer_CountryID='?' (DbType = Guid), @_outer_ID='?' (DbType = Guid)], CommandType='Text', CommandTimeout='30']
SELECT TOP(1) [qf4].[Name]
FROM [CityNames] AS [qf4]
WHERE ([qf4].[LanguageID] = @__ef_filter__LanguageID_3) AND (((@_outer_TenantID = [qf4].[TenantID]) AND (@_outer_CountryID = [qf4].[CountryID])) AND (@_outer_ID = [qf4].[CityID]))
Microsoft.EntityFrameworkCore.Database.Command:Information: Executed DbCommand (68ms) [Parameters=[@__ef_filter__LanguageID_3='?' (Size = 3), @_outer_TenantID='?' (DbType = Guid), @_outer_CountryID='?' (DbType = Guid), @_outer_ID='?' (DbType = Guid)], CommandType='Text', CommandTimeout='30']
SELECT TOP(1) [qf4].[Name]
FROM [CityNames] AS [qf4]
WHERE ([qf4].[LanguageID] = @__ef_filter__LanguageID_3) AND (((@_outer_TenantID = [qf4].[TenantID]) AND (@_outer_CountryID = [qf4].[CountryID])) AND (@_outer_ID = [qf4].[CityID]))
Microsoft.EntityFrameworkCore.Database.Command:Information: Executed DbCommand (66ms) [Parameters=[@__ef_filter__LanguageID_3='?' (Size = 3), @_outer_TenantID='?' (DbType = Guid), @_outer_CountryID='?' (DbType = Guid), @_outer_ID='?' (DbType = Guid)], CommandType='Text', CommandTimeout='30']
SELECT TOP(1) [qf4].[Name]
FROM [CityNames] AS [qf4]
WHERE ([qf4].[LanguageID] = @__ef_filter__LanguageID_3) AND (((@_outer_TenantID = [qf4].[TenantID]) AND (@_outer_CountryID = [qf4].[CountryID])) AND (@_outer_ID = [qf4].[CityID]))
Microsoft.EntityFrameworkCore.Database.Command:Information: Executed DbCommand (79ms) [Parameters=[@__ef_filter__LanguageID_3='?' (Size = 3), @_outer_TenantID='?' (DbType = Guid), @_outer_CountryID='?' (DbType = Guid), @_outer_ID='?' (DbType = Guid)], CommandType='Text', CommandTimeout='30']
SELECT TOP(1) [qf4].[Name]
FROM [CityNames] AS [qf4]
WHERE ([qf4].[LanguageID] = @__ef_filter__LanguageID_3) AND (((@_outer_TenantID = [qf4].[TenantID]) AND (@_outer_CountryID = [qf4].[CountryID])) AND (@_outer_ID = [qf4].[CityID]))
Microsoft.EntityFrameworkCore.Database.Command:Information: Executed DbCommand (89ms) [Parameters=[@__ef_filter__LanguageID_3='?' (Size = 3), @_outer_TenantID='?' (DbType = Guid), @_outer_CountryID='?' (DbType = Guid), @_outer_ID='?' (DbType = Guid)], CommandType='Text', CommandTimeout='30']
SELECT TOP(1) [qf4].[Name]
FROM [CityNames] AS [qf4]
WHERE ([qf4].[LanguageID] = @__ef_filter__LanguageID_3) AND (((@_outer_TenantID = [qf4].[TenantID]) AND (@_outer_CountryID = [qf4].[CountryID])) AND (@_outer_ID = [qf4].[CityID]))
Microsoft.EntityFrameworkCore.Database.Command:Information: Executed DbCommand (101ms) [Parameters=[@__ef_filter__LanguageID_3='?' (Size = 3), @_outer_TenantID='?' (DbType = Guid), @_outer_CountryID='?' (DbType = Guid), @_outer_ID='?' (DbType = Guid)], CommandType='Text', CommandTimeout='30']
SELECT TOP(1) [qf4].[Name]
FROM [CityNames] AS [qf4]
WHERE ([qf4].[LanguageID] = @__ef_filter__LanguageID_3) AND (((@_outer_TenantID = [qf4].[TenantID]) AND (@_outer_CountryID = [qf4].[CountryID])) AND (@_outer_ID = [qf4].[CityID]))
Microsoft.EntityFrameworkCore.Database.Command:Information: Executed DbCommand (87ms) [Parameters=[@__ef_filter__LanguageID_3='?' (Size = 3), @_outer_TenantID='?' (DbType = Guid), @_outer_CountryID='?' (DbType = Guid), @_outer_ID='?' (DbType = Guid)], CommandType='Text', CommandTimeout='30']
SELECT TOP(1) [qf4].[Name]
FROM [CityNames] AS [qf4]
WHERE ([qf4].[LanguageID] = @__ef_filter__LanguageID_3) AND (((@_outer_TenantID = [qf4].[TenantID]) AND (@_outer_CountryID = [qf4].[CountryID])) AND (@_outer_ID = [qf4].[CityID]))
Microsoft.EntityFrameworkCore.Database.Command:Information: Executed DbCommand (70ms) [Parameters=[@__ef_filter__LanguageID_3='?' (Size = 3), @_outer_TenantID='?' (DbType = Guid), @_outer_CountryID='?' (DbType = Guid), @_outer_ID='?' (DbType = Guid)], CommandType='Text', CommandTimeout='30']
SELECT TOP(1) [qf4].[Name]
FROM [CityNames] AS [qf4]
WHERE ([qf4].[LanguageID] = @__ef_filter__LanguageID_3) AND (((@_outer_TenantID = [qf4].[TenantID]) AND (@_outer_CountryID = [qf4].[CountryID])) AND (@_outer_ID = [qf4].[CityID]))
Microsoft.EntityFrameworkCore.Database.Command:Information: Executed DbCommand (65ms) [Parameters=[@__ef_filter__LanguageID_3='?' (Size = 3), @_outer_TenantID='?' (DbType = Guid), @_outer_CountryID='?' (DbType = Guid), @_outer_ID='?' (DbType = Guid)], CommandType='Text', CommandTimeout='30']
SELECT TOP(1) [qf4].[Name]
FROM [CityNames] AS [qf4]
WHERE ([qf4].[LanguageID] = @__ef_filter__LanguageID_3) AND (((@_outer_TenantID = [qf4].[TenantID]) AND (@_outer_CountryID = [qf4].[CountryID])) AND (@_outer_ID = [qf4].[CityID]))
Microsoft.EntityFrameworkCore.Database.Command:Information: Executed DbCommand (71ms) [Parameters=[@__ef_filter__LanguageID_3='?' (Size = 3), @_outer_TenantID='?' (DbType = Guid), @_outer_CountryID='?' (DbType = Guid), @_outer_ID='?' (DbType = Guid)], CommandType='Text', CommandTimeout='30']
SELECT TOP(1) [qf4].[Name]
FROM [CityNames] AS [qf4]
WHERE ([qf4].[LanguageID] = @__ef_filter__LanguageID_3) AND (((@_outer_TenantID = [qf4].[TenantID]) AND (@_outer_CountryID = [qf4].[CountryID])) AND (@_outer_ID = [qf4].[CityID]))
Microsoft.EntityFrameworkCore.Database.Command:Information: Executed DbCommand (70ms) [Parameters=[@__ef_filter__LanguageID_3='?' (Size = 3), @_outer_TenantID='?' (DbType = Guid), @_outer_CountryID='?' (DbType = Guid), @_outer_ID='?' (DbType = Guid)], CommandType='Text', CommandTimeout='30']
SELECT TOP(1) [qf4].[Name]
FROM [CityNames] AS [qf4]
WHERE ([qf4].[LanguageID] = @__ef_filter__LanguageID_3) AND (((@_outer_TenantID = [qf4].[TenantID]) AND (@_outer_CountryID = [qf4].[CountryID])) AND (@_outer_ID = [qf4].[CityID]))
Microsoft.EntityFrameworkCore.Database.Command:Information: Executed DbCommand (97ms) [Parameters=[@__TenantID_0='?' (DbType = Guid), @__Filter_1='?' (Size = 4000), @__Filter_2='?' (Size = 4000), @__Filter_3='?' (Size = 4000), @__Filter_4='?' (Size = 4000), @__Filter_5='?' (Size = 4000), @__ef_filter__LanguageID_0='?' (Size = 3), @__Filter_6='?' (Size = 4000), @__ef_filter__LanguageID_1='?' (Size = 3), @__Filter_7='?' (Size = 4000), @__Filter_8='?' (Size = 4000)], CommandType='Text', CommandTimeout='30']

And at the beginning I got this warning (it doesn't consider it as navigation property, filter should be set automatically, idem for language query filter, and indeed it works because I can see correct data returned)

Microsoft.EntityFrameworkCore.Query:Warning: Query: '(from CityName _1 in [w].BirthCity.Names select [_1].Name).FirstOrDefault()' uses First/FirstOrDefault/Last/LastOrDefault operation without OrderBy and filter which may lead to unpredictable results.

Array index instead translate as you see in the first post, bad.

berets76 commented 6 years ago

However wrong array index translation is better than wrong FirstOrDefault, because with FirstOrDefault my query execution time raise from 500 ms to 18 seconds!

Array index -> 3 queries always -> 500 ms (for 25 records) FirstOrDefault -> 1 + (2*retrievedRecords) queries -> 18 seconds (for 25 records)

smitpatel commented 6 years ago

Based on bits and pieces of data posted above, Following query

var data = db.Set<Guest>()
                        .Where(w => w.TenantID == TenantID && w.Cancelled == null &&
                        (w.VAT.Contains(Filter) || w.First.Contains(Filter) || w.Last.Contains(Filter)
                        || w.BirthDate.ToString().Contains(Filter) ||
                        w.ResidenceAddress.Contains(Filter) || w.BirthCountry.Names.Any(a => a.Name.Contains(Filter)) ||
                        w.BirthCity.Names.Any(a => a.Name.Contains(Filter))))
                        .OrderBy(o => o.ID)
                        .Skip(0).Take(10)
                        .Select(s => new
                        {
                            ID = s.ID,
                            Name = s.FullName,
                            VAT = s.VAT,
                            BirthDate = s.BirthDate.ToString("yyyy/MM/dd"),
                            ResidenceAddress = s.ResidenceAddress,
                            BirthCountryName = s.BirthCountry.Names.FirstOrDefault().Name,
                            BirthCityName = s.BirthCity.Names.FirstOrDefault().Name,
                            Reservations = s.ReservationsNumber,
                            Bookable = s.Bookable,
                            IsHospitalized = s.IsHospitalized,
                            IsPresent = s.IsPresent
                        })
                        .AsNoTracking()
                        .ToList();

Generates this SQL

  SELECT [w].[ID], [w].[FullName] AS [Name], [w].[VAT], [w].[BirthDate], [w].[ResidenceAddress], (
      SELECT TOP(1) [qf1].[Name]
      FROM [CountryName] AS [qf1]
      WHERE [qf1].[LanguageID] IS NULL AND (([w.BirthCountry].[TenantID] = [qf1].[TenantID]) AND ([w.BirthCountry].[ID] = [qf1].[CountryID]))
  ) AS [BirthCountryName], (
      SELECT TOP(1) [qf2].[Name]
      FROM [CityName] AS [qf2]
      WHERE [qf2].[LanguageID] IS NULL AND ((([w.BirthCity].[TenantID] = [qf2].[TenantID]) AND ([w.BirthCity].[CountryID] = [qf2].[CountryID])) AND ([w.BirthCity].[ID] = [qf2].[CityID]))
  ) AS [BirthCityName], [w].[ReservationsNumber] AS [Reservations], [w].[Bookable], [w].[IsHospitalized], [w].[IsPresent]
  FROM [Guest] AS [w]
  LEFT JOIN [City] AS [w.BirthCity] ON (([w].[TenantID] = [w.BirthCity].[TenantID]) AND ([w].[BirthCountryID] = [w.BirthCity].[CountryID])) AND ([w].[BirthCityID] = [w.BirthCity].[ID])
  LEFT JOIN [Country] AS [w.BirthCountry] ON ([w].[TenantID] = [w.BirthCountry].[TenantID]) AND ([w].[BirthCountryID] = [w.BirthCountry].[ID])
  WHERE (([w].[TenantID] = @__TenantID_0) AND [w].[Cancelled] IS NULL) AND ((((((((CHARINDEX(@__Filter_1, [w].[VAT]) > 0) OR (@__Filter_1 = N'')) OR ((CHARINDEX(@__Filter_2, [w].[First]) > 0) OR (@__Filter_2 = N''))) OR ((CHARINDEX(@__Filter_3, [w].[Last]) > 0) OR (@__Filter_3 = N''))) OR ((CHARINDEX(@__Filter_4, CONVERT(VARCHAR(100), [w].[BirthDate])) > 0) OR (@__Filter_4 = N''))) OR ((CHARINDEX(@__Filter_5, [w].[ResidenceAddress]) > 0) OR (@__Filter_5 = N''))) OR EXISTS (
      SELECT 1
      FROM [CountryName] AS [qf]
      WHERE ([qf].[LanguageID] IS NULL AND ((CHARINDEX(@__Filter_6, [qf].[Name]) > 0) OR (@__Filter_6 = N''))) AND (([w.BirthCountry].[TenantID] = [qf].[TenantID]) AND ([w.BirthCountry].[ID] = [qf].[CountryID])))) OR EXISTS (
      SELECT 1
      FROM [CityName] AS [qf0]
      WHERE ([qf0].[LanguageID] IS NULL AND ((CHARINDEX(@__Filter_7, [qf0].[Name]) > 0) OR (@__Filter_7 = N''))) AND ((([w.BirthCity].[TenantID] = [qf0].[TenantID]) AND ([w.BirthCity].[CountryID] = [qf0].[CountryID])) AND ([w.BirthCity].[ID] = [qf0].[CityID]))))
  ORDER BY [w].[ID]
  OFFSET @__p_8 ROWS FETCH NEXT @__p_9 ROWS ONLY

Both the collection FirstOrDefault properties are lifted as expected. (tested with 2.0.1 & 2.1.0-rc1 packages)

This is the repro code I used https://gist.github.com/smitpatel/d7dda5ca29a618c9cbd236a2683e73ae

Please post a full repro project which demonstrate the issue you are seeing so we can investigate further.

berets76 commented 6 years ago

Ok, found the problem.

Actually this is my real [Guest] entity

    public class Guest
    {
        [Key, Column(Order = 0)]
        public Guid TenantID { get; set; }
        [Key, Column(Order = 1)]
        public Guid ID { get; set; }
        public Guid? BirthCountryID { get; set; }
        public virtual Country BirthCountry { get; set; }
        public Guid? BirthCityID { get; set; }
        public virtual City BirthCity { get; set; }
        public DateTime? Cancelled { get; set; }
        public string VAT { get; set; }
        public string First { get; set; }
        public string Last { get; set; }
        public DateTime BirthDate { get; set; }
        public string ResidenceAddress { get; set; }
        [NotMapped]
        public string FullName => First + " " + Last;
        public bool IsHospitalized { get; set; }
        public bool IsPresent { get; set; }
        public short ReservationsNumber { get; set; }
    }

FullName property is a [NotMapped] expression bodied member, and this is the problem.

If I return s.Last or s.First SQL is generated correctly, instead if I use s.FullName, I got:

1) if no record found this SQL

SELECT [w].[TenantID], [w].[ID], [w].[BirthCityID], [w].[BirthCountryID], [w].[BirthDate], [w].[Cancelled], [w].[First], [w].[IsHospitalized], 
[w].[IsPresent], [w].[Last], [w].[ReservationsNumber] AS [Reservations], [w].[ResidenceAddress], [w].[VAT], [w.BirthCountry].[TenantID], 
[w.BirthCountry].[ID], [w.BirthCity].[TenantID], [w.BirthCity].[CountryID], [w.BirthCity].[ID]
FROM [Guests] AS [w]
LEFT JOIN [Cities] AS [w.BirthCity] ON (([w].[TenantID] = [w.BirthCity].[TenantID]) AND ([w].[BirthCountryID] = [w.BirthCity].[CountryID])) AND ([w].[BirthCityID] = [w.BirthCity].[ID])
LEFT JOIN [Countries] AS [w.BirthCountry] ON ([w].[TenantID] = [w.BirthCountry].[TenantID]) AND ([w].[BirthCountryID] = [w.BirthCountry].[ID])
WHERE (([w].[TenantID] = @__TenantID_0) AND [w].[Cancelled] IS NULL) AND ((((((((CHARINDEX(@__Filter_1, [w].[VAT]) > 0) OR (@__Filter_1 = N'')) OR 
  ((CHARINDEX(@__Filter_2, [w].[First]) > 0) OR (@__Filter_2 = N''))) OR ((CHARINDEX(@__Filter_3, [w].[Last]) > 0) OR (@__Filter_3 = N''))) OR 
  ((CHARINDEX(@__Filter_4, CONVERT(VARCHAR(100), [w].[BirthDate])) > 0) OR (@__Filter_4 = N''))) OR ((CHARINDEX(@__Filter_5, [w].[ResidenceAddress]) > 0) OR 
  (@__Filter_5 = N''))) OR EXISTS (
      SELECT 1
      FROM [CountryNames] AS [qf]
      WHERE (([qf].[LanguageID] = @__ef_filter__LanguageID_0) AND ((CHARINDEX(@__Filter_6, [qf].[Name]) > 0) OR (@__Filter_6 = N''))) AND (([w.BirthCountry].[TenantID] = [qf].[TenantID]) AND 
      ([w.BirthCountry].[ID] = [qf].[CountryID])))) OR EXISTS (
      SELECT 1
      FROM [CityNames] AS [qf0]
      WHERE (([qf0].[LanguageID] = @__ef_filter__LanguageID_1) AND ((CHARINDEX(@__Filter_7, [qf0].[Name]) > 0) OR (@__Filter_7 = N''))) AND ((([w.BirthCity].[TenantID] = [qf0].[TenantID]) AND 
      ([w.BirthCity].[CountryID] = [qf0].[CountryID])) AND ([w.BirthCity].[ID] = [qf0].[CityID]))))
ORDER BY [w].[ID]
OFFSET @__p_8 ROWS FETCH NEXT @__p_9 ROWS ONLY

2) if it found some records (10 in this case)

SELECT [w].[TenantID], [w].[ID], [w].[BirthCityID], [w].[BirthCountryID], [w].[BirthDate], [w].[Cancelled], [w].[First], [w].[IsHospitalized], [w].[IsPresent], [w].[Last], [w].[ReservationsNumber] AS [Reservations], [w].[ResidenceAddress], [w].[VAT], [w.BirthCountry].[TenantID], [w.BirthCountry].[ID], [w.BirthCity].[TenantID], [w.BirthCity].[CountryID], [w.BirthCity].[ID]
      FROM [Guests] AS [w]
      LEFT JOIN [Cities] AS [w.BirthCity] ON (([w].[TenantID] = [w.BirthCity].[TenantID]) AND ([w].[BirthCountryID] = [w.BirthCity].[CountryID])) AND ([w].[BirthCityID] = [w.BirthCity].[ID])
      LEFT JOIN [Countries] AS [w.BirthCountry] ON ([w].[TenantID] = [w.BirthCountry].[TenantID]) AND ([w].[BirthCountryID] = [w.BirthCountry].[ID])
      WHERE (([w].[TenantID] = @__TenantID_0) AND [w].[Cancelled] IS NULL) AND ((((((((CHARINDEX(@__Filter_1, [w].[VAT]) > 0) OR (@__Filter_1 = N'')) OR ((CHARINDEX(@__Filter_2, [w].[First]) > 0) OR (@__Filter_2 = N''))) OR ((CHARINDEX(@__Filter_3, [w].[Last]) > 0) OR (@__Filter_3 = N''))) OR ((CHARINDEX(@__Filter_4, CONVERT(VARCHAR(100), [w].[BirthDate])) > 0) OR (@__Filter_4 = N''))) OR ((CHARINDEX(@__Filter_5, [w].[ResidenceAddress]) > 0) OR (@__Filter_5 = N''))) OR EXISTS (
          SELECT 1
          FROM [CountryNames] AS [qf]
          WHERE (([qf].[LanguageID] = @__ef_filter__LanguageID_0) AND ((CHARINDEX(@__Filter_6, [qf].[Name]) > 0) OR (@__Filter_6 = N''))) AND (([w.BirthCountry].[TenantID] = [qf].[TenantID]) AND ([w.BirthCountry].[ID] = [qf].[CountryID])))) OR EXISTS (
          SELECT 1
          FROM [CityNames] AS [qf0]
          WHERE (([qf0].[LanguageID] = @__ef_filter__LanguageID_1) AND ((CHARINDEX(@__Filter_7, [qf0].[Name]) > 0) OR (@__Filter_7 = N''))) AND ((([w.BirthCity].[TenantID] = [qf0].[TenantID]) AND ([w.BirthCity].[CountryID] = [qf0].[CountryID])) AND ([w.BirthCity].[ID] = [qf0].[CityID]))))
      ORDER BY [w].[ID]
      OFFSET @__p_8 ROWS FETCH NEXT @__p_9 ROWS ONLY
dbug: Microsoft.EntityFrameworkCore.Database.Command[20300]
      A data reader was disposed.
dbug: Microsoft.EntityFrameworkCore.Database.Command[20100]
      Executing DbCommand [Parameters=[@__ef_filter__LanguageID_2='it' (Size = 3), @_outer_TenantID='ca40afca-1a33-e811-80c2-0003ff5302d4', @_outer_ID='a6245729-1d33-e811-80c2-0003ff5302d4'], CommandType='Text', CommandTimeout='30']
      SELECT TOP(1) [qf3].[Name]
      FROM [CountryNames] AS [qf3]
      WHERE ([qf3].[LanguageID] = @__ef_filter__LanguageID_2) AND ((@_outer_TenantID = [qf3].[TenantID]) AND (@_outer_ID = [qf3].[CountryID]))
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (62ms) [Parameters=[@__ef_filter__LanguageID_2='it' (Size = 3), @_outer_TenantID='ca40afca-1a33-e811-80c2-0003ff5302d4', @_outer_ID='a6245729-1d33-e811-80c2-0003ff5302d4'], CommandType='Text', CommandTimeout='30']
      SELECT TOP(1) [qf3].[Name]
      FROM [CountryNames] AS [qf3]
      WHERE ([qf3].[LanguageID] = @__ef_filter__LanguageID_2) AND ((@_outer_TenantID = [qf3].[TenantID]) AND (@_outer_ID = [qf3].[CountryID]))
dbug: Microsoft.EntityFrameworkCore.Database.Command[20300]
      A data reader was disposed.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20002]
      Closing connection to database 'MyDatabase' on server 'tcp:mydatabase.database.windows.net,1433'.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20003]
      Closed connection to database 'MyDatabase' on server 'tcp:mydatabase.database.windows.net,1433'.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20000]
      Opening connection to database 'MyDatabase' on server 'tcp:mydatabase.database.windows.net,1433'.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20001]
      Opened connection to database 'MyDatabase' on server 'tcp:mydatabase.database.windows.net,1433'.
dbug: Microsoft.EntityFrameworkCore.Database.Command[20100]
      Executing DbCommand [Parameters=[@__ef_filter__LanguageID_3='it' (Size = 3), @_outer_TenantID1='ca40afca-1a33-e811-80c2-0003ff5302d4', @_outer_CountryID='a6245729-1d33-e811-80c2-0003ff5302d4', @_outer_ID1='6ff2b8b9-1e33-e811-80c2-0003ff5302d4'], CommandType='Text', CommandTimeout='30']
      SELECT TOP(1) [qf4].[Name]
      FROM [CityNames] AS [qf4]
      WHERE ([qf4].[LanguageID] = @__ef_filter__LanguageID_3) AND (((@_outer_TenantID1 = [qf4].[TenantID]) AND (@_outer_CountryID = [qf4].[CountryID])) AND (@_outer_ID1 = [qf4].[CityID]))
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (78ms) [Parameters=[@__ef_filter__LanguageID_3='it' (Size = 3), @_outer_TenantID1='ca40afca-1a33-e811-80c2-0003ff5302d4', @_outer_CountryID='a6245729-1d33-e811-80c2-0003ff5302d4', @_outer_ID1='6ff2b8b9-1e33-e811-80c2-0003ff5302d4'], CommandType='Text', CommandTimeout='30']
      SELECT TOP(1) [qf4].[Name]
      FROM [CityNames] AS [qf4]
      WHERE ([qf4].[LanguageID] = @__ef_filter__LanguageID_3) AND (((@_outer_TenantID1 = [qf4].[TenantID]) AND (@_outer_CountryID = [qf4].[CountryID])) AND (@_outer_ID1 = [qf4].[CityID]))
dbug: Microsoft.EntityFrameworkCore.Database.Command[20300]
      A data reader was disposed.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20002]
      Closing connection to database 'MyDatabase' on server 'tcp:mydatabase.database.windows.net,1433'.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20003]
      Closed connection to database 'MyDatabase' on server 'tcp:mydatabase.database.windows.net,1433'.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20000]
      Opening connection to database 'MyDatabase' on server 'tcp:mydatabase.database.windows.net,1433'.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20001]
      Opened connection to database 'MyDatabase' on server 'tcp:mydatabase.database.windows.net,1433'.
dbug: Microsoft.EntityFrameworkCore.Database.Command[20100]
      Executing DbCommand [Parameters=[@__ef_filter__LanguageID_2='it' (Size = 3), @_outer_TenantID='ca40afca-1a33-e811-80c2-0003ff5302d4', @_outer_ID='a6245729-1d33-e811-80c2-0003ff5302d4'], CommandType='Text', CommandTimeout='30']
      SELECT TOP(1) [qf3].[Name]
      FROM [CountryNames] AS [qf3]
      WHERE ([qf3].[LanguageID] = @__ef_filter__LanguageID_2) AND ((@_outer_TenantID = [qf3].[TenantID]) AND (@_outer_ID = [qf3].[CountryID]))
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (60ms) [Parameters=[@__ef_filter__LanguageID_2='it' (Size = 3), @_outer_TenantID='ca40afca-1a33-e811-80c2-0003ff5302d4', @_outer_ID='a6245729-1d33-e811-80c2-0003ff5302d4'], CommandType='Text', CommandTimeout='30']
      SELECT TOP(1) [qf3].[Name]
      FROM [CountryNames] AS [qf3]
      WHERE ([qf3].[LanguageID] = @__ef_filter__LanguageID_2) AND ((@_outer_TenantID = [qf3].[TenantID]) AND (@_outer_ID = [qf3].[CountryID]))
dbug: Microsoft.EntityFrameworkCore.Database.Command[20300]
      A data reader was disposed.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20002]
      Closing connection to database 'MyDatabase' on server 'tcp:mydatabase.database.windows.net,1433'.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20003]
      Closed connection to database 'MyDatabase' on server 'tcp:mydatabase.database.windows.net,1433'.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20000]
      Opening connection to database 'MyDatabase' on server 'tcp:mydatabase.database.windows.net,1433'.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20001]
      Opened connection to database 'MyDatabase' on server 'tcp:mydatabase.database.windows.net,1433'.
dbug: Microsoft.EntityFrameworkCore.Database.Command[20100]
      Executing DbCommand [Parameters=[@__ef_filter__LanguageID_3='it' (Size = 3), @_outer_TenantID1='ca40afca-1a33-e811-80c2-0003ff5302d4', @_outer_CountryID='a6245729-1d33-e811-80c2-0003ff5302d4', @_outer_ID1='f99211dc-2233-e811-80c2-0003ff5302d4'], CommandType='Text', CommandTimeout='30']
      SELECT TOP(1) [qf4].[Name]
      FROM [CityNames] AS [qf4]
      WHERE ([qf4].[LanguageID] = @__ef_filter__LanguageID_3) AND (((@_outer_TenantID1 = [qf4].[TenantID]) AND (@_outer_CountryID = [qf4].[CountryID])) AND (@_outer_ID1 = [qf4].[CityID]))
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (150ms) [Parameters=[@__ef_filter__LanguageID_3='it' (Size = 3), @_outer_TenantID1='ca40afca-1a33-e811-80c2-0003ff5302d4', @_outer_CountryID='a6245729-1d33-e811-80c2-0003ff5302d4', @_outer_ID1='f99211dc-2233-e811-80c2-0003ff5302d4'], CommandType='Text', CommandTimeout='30']
      SELECT TOP(1) [qf4].[Name]
      FROM [CityNames] AS [qf4]
      WHERE ([qf4].[LanguageID] = @__ef_filter__LanguageID_3) AND (((@_outer_TenantID1 = [qf4].[TenantID]) AND (@_outer_CountryID = [qf4].[CountryID])) AND (@_outer_ID1 = [qf4].[CityID]))
dbug: Microsoft.EntityFrameworkCore.Database.Command[20300]
      A data reader was disposed.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20002]
      Closing connection to database 'MyDatabase' on server 'tcp:mydatabase.database.windows.net,1433'.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20003]
      Closed connection to database 'MyDatabase' on server 'tcp:mydatabase.database.windows.net,1433'.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20000]
      Opening connection to database 'MyDatabase' on server 'tcp:mydatabase.database.windows.net,1433'.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20001]
      Opened connection to database 'MyDatabase' on server 'tcp:mydatabase.database.windows.net,1433'.
dbug: Microsoft.EntityFrameworkCore.Database.Command[20100]
      Executing DbCommand [Parameters=[@__ef_filter__LanguageID_2='it' (Size = 3), @_outer_TenantID='ca40afca-1a33-e811-80c2-0003ff5302d4', @_outer_ID='a6245729-1d33-e811-80c2-0003ff5302d4'], CommandType='Text', CommandTimeout='30']
      SELECT TOP(1) [qf3].[Name]
      FROM [CountryNames] AS [qf3]
      WHERE ([qf3].[LanguageID] = @__ef_filter__LanguageID_2) AND ((@_outer_TenantID = [qf3].[TenantID]) AND (@_outer_ID = [qf3].[CountryID]))
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (76ms) [Parameters=[@__ef_filter__LanguageID_2='it' (Size = 3), @_outer_TenantID='ca40afca-1a33-e811-80c2-0003ff5302d4', @_outer_ID='a6245729-1d33-e811-80c2-0003ff5302d4'], CommandType='Text', CommandTimeout='30']
      SELECT TOP(1) [qf3].[Name]
      FROM [CountryNames] AS [qf3]
      WHERE ([qf3].[LanguageID] = @__ef_filter__LanguageID_2) AND ((@_outer_TenantID = [qf3].[TenantID]) AND (@_outer_ID = [qf3].[CountryID]))
dbug: Microsoft.EntityFrameworkCore.Database.Command[20300]
      A data reader was disposed.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20002]
      Closing connection to database 'MyDatabase' on server 'tcp:mydatabase.database.windows.net,1433'.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20003]
      Closed connection to database 'MyDatabase' on server 'tcp:mydatabase.database.windows.net,1433'.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20000]
      Opening connection to database 'MyDatabase' on server 'tcp:mydatabase.database.windows.net,1433'.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20001]
      Opened connection to database 'MyDatabase' on server 'tcp:mydatabase.database.windows.net,1433'.
dbug: Microsoft.EntityFrameworkCore.Database.Command[20100]
      Executing DbCommand [Parameters=[@__ef_filter__LanguageID_3='it' (Size = 3), @_outer_TenantID1='ca40afca-1a33-e811-80c2-0003ff5302d4', @_outer_CountryID='a6245729-1d33-e811-80c2-0003ff5302d4', @_outer_ID1='2011ddc9-2133-e811-80c2-0003ff5302d4'], CommandType='Text', CommandTimeout='30']
      SELECT TOP(1) [qf4].[Name]
      FROM [CityNames] AS [qf4]
      WHERE ([qf4].[LanguageID] = @__ef_filter__LanguageID_3) AND (((@_outer_TenantID1 = [qf4].[TenantID]) AND (@_outer_CountryID = [qf4].[CountryID])) AND (@_outer_ID1 = [qf4].[CityID]))
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (59ms) [Parameters=[@__ef_filter__LanguageID_3='it' (Size = 3), @_outer_TenantID1='ca40afca-1a33-e811-80c2-0003ff5302d4', @_outer_CountryID='a6245729-1d33-e811-80c2-0003ff5302d4', @_outer_ID1='2011ddc9-2133-e811-80c2-0003ff5302d4'], CommandType='Text', CommandTimeout='30']
      SELECT TOP(1) [qf4].[Name]
      FROM [CityNames] AS [qf4]
      WHERE ([qf4].[LanguageID] = @__ef_filter__LanguageID_3) AND (((@_outer_TenantID1 = [qf4].[TenantID]) AND (@_outer_CountryID = [qf4].[CountryID])) AND (@_outer_ID1 = [qf4].[CityID]))
dbug: Microsoft.EntityFrameworkCore.Database.Command[20300]
      A data reader was disposed.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20002]
      Closing connection to database 'MyDatabase' on server 'tcp:mydatabase.database.windows.net,1433'.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20003]
      Closed connection to database 'MyDatabase' on server 'tcp:mydatabase.database.windows.net,1433'.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20000]
      Opening connection to database 'MyDatabase' on server 'tcp:mydatabase.database.windows.net,1433'.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20001]
      Opened connection to database 'MyDatabase' on server 'tcp:mydatabase.database.windows.net,1433'.
dbug: Microsoft.EntityFrameworkCore.Database.Command[20100]
      Executing DbCommand [Parameters=[@__ef_filter__LanguageID_2='it' (Size = 3), @_outer_TenantID='ca40afca-1a33-e811-80c2-0003ff5302d4', @_outer_ID='a6245729-1d33-e811-80c2-0003ff5302d4'], CommandType='Text', CommandTimeout='30']
      SELECT TOP(1) [qf3].[Name]
      FROM [CountryNames] AS [qf3]
      WHERE ([qf3].[LanguageID] = @__ef_filter__LanguageID_2) AND ((@_outer_TenantID = [qf3].[TenantID]) AND (@_outer_ID = [qf3].[CountryID]))
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (60ms) [Parameters=[@__ef_filter__LanguageID_2='it' (Size = 3), @_outer_TenantID='ca40afca-1a33-e811-80c2-0003ff5302d4', @_outer_ID='a6245729-1d33-e811-80c2-0003ff5302d4'], CommandType='Text', CommandTimeout='30']
      SELECT TOP(1) [qf3].[Name]
      FROM [CountryNames] AS [qf3]
      WHERE ([qf3].[LanguageID] = @__ef_filter__LanguageID_2) AND ((@_outer_TenantID = [qf3].[TenantID]) AND (@_outer_ID = [qf3].[CountryID]))
dbug: Microsoft.EntityFrameworkCore.Database.Command[20300]
      A data reader was disposed.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20002]
      Closing connection to database 'MyDatabase' on server 'tcp:mydatabase.database.windows.net,1433'.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20003]
      Closed connection to database 'MyDatabase' on server 'tcp:mydatabase.database.windows.net,1433'.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20000]
      Opening connection to database 'MyDatabase' on server 'tcp:mydatabase.database.windows.net,1433'.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20001]
      Opened connection to database 'MyDatabase' on server 'tcp:mydatabase.database.windows.net,1433'.
dbug: Microsoft.EntityFrameworkCore.Database.Command[20100]
      Executing DbCommand [Parameters=[@__ef_filter__LanguageID_3='it' (Size = 3), @_outer_TenantID1='ca40afca-1a33-e811-80c2-0003ff5302d4', @_outer_CountryID='a6245729-1d33-e811-80c2-0003ff5302d4', @_outer_ID1='7b099afd-1d33-e811-80c2-0003ff5302d4'], CommandType='Text', CommandTimeout='30']
      SELECT TOP(1) [qf4].[Name]
      FROM [CityNames] AS [qf4]
      WHERE ([qf4].[LanguageID] = @__ef_filter__LanguageID_3) AND (((@_outer_TenantID1 = [qf4].[TenantID]) AND (@_outer_CountryID = [qf4].[CountryID])) AND (@_outer_ID1 = [qf4].[CityID]))
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (58ms) [Parameters=[@__ef_filter__LanguageID_3='it' (Size = 3), @_outer_TenantID1='ca40afca-1a33-e811-80c2-0003ff5302d4', @_outer_CountryID='a6245729-1d33-e811-80c2-0003ff5302d4', @_outer_ID1='7b099afd-1d33-e811-80c2-0003ff5302d4'], CommandType='Text', CommandTimeout='30']
      SELECT TOP(1) [qf4].[Name]
      FROM [CityNames] AS [qf4]
      WHERE ([qf4].[LanguageID] = @__ef_filter__LanguageID_3) AND (((@_outer_TenantID1 = [qf4].[TenantID]) AND (@_outer_CountryID = [qf4].[CountryID])) AND (@_outer_ID1 = [qf4].[CityID]))
dbug: Microsoft.EntityFrameworkCore.Database.Command[20300]
      A data reader was disposed.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20002]
      Closing connection to database 'MyDatabase' on server 'tcp:mydatabase.database.windows.net,1433'.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20003]
      Closed connection to database 'MyDatabase' on server 'tcp:mydatabase.database.windows.net,1433'.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20000]
      Opening connection to database 'MyDatabase' on server 'tcp:mydatabase.database.windows.net,1433'.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20001]
      Opened connection to database 'MyDatabase' on server 'tcp:mydatabase.database.windows.net,1433'.
dbug: Microsoft.EntityFrameworkCore.Database.Command[20100]
      Executing DbCommand [Parameters=[@__ef_filter__LanguageID_2='it' (Size = 3), @_outer_TenantID='ca40afca-1a33-e811-80c2-0003ff5302d4', @_outer_ID='a6245729-1d33-e811-80c2-0003ff5302d4'], CommandType='Text', CommandTimeout='30']
      SELECT TOP(1) [qf3].[Name]
      FROM [CountryNames] AS [qf3]
      WHERE ([qf3].[LanguageID] = @__ef_filter__LanguageID_2) AND ((@_outer_TenantID = [qf3].[TenantID]) AND (@_outer_ID = [qf3].[CountryID]))
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (66ms) [Parameters=[@__ef_filter__LanguageID_2='it' (Size = 3), @_outer_TenantID='ca40afca-1a33-e811-80c2-0003ff5302d4', @_outer_ID='a6245729-1d33-e811-80c2-0003ff5302d4'], CommandType='Text', CommandTimeout='30']
      SELECT TOP(1) [qf3].[Name]
      FROM [CountryNames] AS [qf3]
      WHERE ([qf3].[LanguageID] = @__ef_filter__LanguageID_2) AND ((@_outer_TenantID = [qf3].[TenantID]) AND (@_outer_ID = [qf3].[CountryID]))
dbug: Microsoft.EntityFrameworkCore.Database.Command[20300]
      A data reader was disposed.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20002]
      Closing connection to database 'MyDatabase' on server 'tcp:mydatabase.database.windows.net,1433'.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20003]
      Closed connection to database 'MyDatabase' on server 'tcp:mydatabase.database.windows.net,1433'.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20000]
      Opening connection to database 'MyDatabase' on server 'tcp:mydatabase.database.windows.net,1433'.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20001]
      Opened connection to database 'MyDatabase' on server 'tcp:mydatabase.database.windows.net,1433'.
dbug: Microsoft.EntityFrameworkCore.Database.Command[20100]
      Executing DbCommand [Parameters=[@__ef_filter__LanguageID_3='it' (Size = 3), @_outer_TenantID1='ca40afca-1a33-e811-80c2-0003ff5302d4', @_outer_CountryID='a6245729-1d33-e811-80c2-0003ff5302d4', @_outer_ID1='ba1869f1-1d33-e811-80c2-0003ff5302d4'], CommandType='Text', CommandTimeout='30']
      SELECT TOP(1) [qf4].[Name]
      FROM [CityNames] AS [qf4]
      WHERE ([qf4].[LanguageID] = @__ef_filter__LanguageID_3) AND (((@_outer_TenantID1 = [qf4].[TenantID]) AND (@_outer_CountryID = [qf4].[CountryID])) AND (@_outer_ID1 = [qf4].[CityID]))
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (59ms) [Parameters=[@__ef_filter__LanguageID_3='it' (Size = 3), @_outer_TenantID1='ca40afca-1a33-e811-80c2-0003ff5302d4', @_outer_CountryID='a6245729-1d33-e811-80c2-0003ff5302d4', @_outer_ID1='ba1869f1-1d33-e811-80c2-0003ff5302d4'], CommandType='Text', CommandTimeout='30']
      SELECT TOP(1) [qf4].[Name]
      FROM [CityNames] AS [qf4]
      WHERE ([qf4].[LanguageID] = @__ef_filter__LanguageID_3) AND (((@_outer_TenantID1 = [qf4].[TenantID]) AND (@_outer_CountryID = [qf4].[CountryID])) AND (@_outer_ID1 = [qf4].[CityID]))
dbug: Microsoft.EntityFrameworkCore.Database.Command[20300]
      A data reader was disposed.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20002]
      Closing connection to database 'MyDatabase' on server 'tcp:mydatabase.database.windows.net,1433'.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20003]
      Closed connection to database 'MyDatabase' on server 'tcp:mydatabase.database.windows.net,1433'.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20000]
      Opening connection to database 'MyDatabase' on server 'tcp:mydatabase.database.windows.net,1433'.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20001]
      Opened connection to database 'MyDatabase' on server 'tcp:mydatabase.database.windows.net,1433'.
dbug: Microsoft.EntityFrameworkCore.Database.Command[20100]
      Executing DbCommand [Parameters=[@__ef_filter__LanguageID_2='it' (Size = 3), @_outer_TenantID='ca40afca-1a33-e811-80c2-0003ff5302d4', @_outer_ID='a6245729-1d33-e811-80c2-0003ff5302d4'], CommandType='Text', CommandTimeout='30']
      SELECT TOP(1) [qf3].[Name]
      FROM [CountryNames] AS [qf3]
      WHERE ([qf3].[LanguageID] = @__ef_filter__LanguageID_2) AND ((@_outer_TenantID = [qf3].[TenantID]) AND (@_outer_ID = [qf3].[CountryID]))
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (58ms) [Parameters=[@__ef_filter__LanguageID_2='it' (Size = 3), @_outer_TenantID='ca40afca-1a33-e811-80c2-0003ff5302d4', @_outer_ID='a6245729-1d33-e811-80c2-0003ff5302d4'], CommandType='Text', CommandTimeout='30']
      SELECT TOP(1) [qf3].[Name]
      FROM [CountryNames] AS [qf3]
      WHERE ([qf3].[LanguageID] = @__ef_filter__LanguageID_2) AND ((@_outer_TenantID = [qf3].[TenantID]) AND (@_outer_ID = [qf3].[CountryID]))
dbug: Microsoft.EntityFrameworkCore.Database.Command[20300]
      A data reader was disposed.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20002]
      Closing connection to database 'MyDatabase' on server 'tcp:mydatabase.database.windows.net,1433'.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20003]
      Closed connection to database 'MyDatabase' on server 'tcp:mydatabase.database.windows.net,1433'.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20000]
      Opening connection to database 'MyDatabase' on server 'tcp:mydatabase.database.windows.net,1433'.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20001]
      Opened connection to database 'MyDatabase' on server 'tcp:mydatabase.database.windows.net,1433'.
dbug: Microsoft.EntityFrameworkCore.Database.Command[20100]
      Executing DbCommand [Parameters=[@__ef_filter__LanguageID_3='it' (Size = 3), @_outer_TenantID1='ca40afca-1a33-e811-80c2-0003ff5302d4', @_outer_CountryID='a6245729-1d33-e811-80c2-0003ff5302d4', @_outer_ID1='4a625300-2333-e811-80c2-0003ff5302d4'], CommandType='Text', CommandTimeout='30']
      SELECT TOP(1) [qf4].[Name]
      FROM [CityNames] AS [qf4]
      WHERE ([qf4].[LanguageID] = @__ef_filter__LanguageID_3) AND (((@_outer_TenantID1 = [qf4].[TenantID]) AND (@_outer_CountryID = [qf4].[CountryID])) AND (@_outer_ID1 = [qf4].[CityID]))
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (87ms) [Parameters=[@__ef_filter__LanguageID_3='it' (Size = 3), @_outer_TenantID1='ca40afca-1a33-e811-80c2-0003ff5302d4', @_outer_CountryID='a6245729-1d33-e811-80c2-0003ff5302d4', @_outer_ID1='4a625300-2333-e811-80c2-0003ff5302d4'], CommandType='Text', CommandTimeout='30']
      SELECT TOP(1) [qf4].[Name]
      FROM [CityNames] AS [qf4]
      WHERE ([qf4].[LanguageID] = @__ef_filter__LanguageID_3) AND (((@_outer_TenantID1 = [qf4].[TenantID]) AND (@_outer_CountryID = [qf4].[CountryID])) AND (@_outer_ID1 = [qf4].[CityID]))
dbug: Microsoft.EntityFrameworkCore.Database.Command[20300]
      A data reader was disposed.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20002]
      Closing connection to database 'MyDatabase' on server 'tcp:mydatabase.database.windows.net,1433'.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20003]
      Closed connection to database 'MyDatabase' on server 'tcp:mydatabase.database.windows.net,1433'.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20000]
      Opening connection to database 'MyDatabase' on server 'tcp:mydatabase.database.windows.net,1433'.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20001]
      Opened connection to database 'MyDatabase' on server 'tcp:mydatabase.database.windows.net,1433'.
dbug: Microsoft.EntityFrameworkCore.Database.Command[20100]
      Executing DbCommand [Parameters=[@__ef_filter__LanguageID_2='it' (Size = 3), @_outer_TenantID='ca40afca-1a33-e811-80c2-0003ff5302d4', @_outer_ID='a6245729-1d33-e811-80c2-0003ff5302d4'], CommandType='Text', CommandTimeout='30']
      SELECT TOP(1) [qf3].[Name]
      FROM [CountryNames] AS [qf3]
      WHERE ([qf3].[LanguageID] = @__ef_filter__LanguageID_2) AND ((@_outer_TenantID = [qf3].[TenantID]) AND (@_outer_ID = [qf3].[CountryID]))
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (57ms) [Parameters=[@__ef_filter__LanguageID_2='it' (Size = 3), @_outer_TenantID='ca40afca-1a33-e811-80c2-0003ff5302d4', @_outer_ID='a6245729-1d33-e811-80c2-0003ff5302d4'], CommandType='Text', CommandTimeout='30']
      SELECT TOP(1) [qf3].[Name]
      FROM [CountryNames] AS [qf3]
      WHERE ([qf3].[LanguageID] = @__ef_filter__LanguageID_2) AND ((@_outer_TenantID = [qf3].[TenantID]) AND (@_outer_ID = [qf3].[CountryID]))
dbug: Microsoft.EntityFrameworkCore.Database.Command[20300]
      A data reader was disposed.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20002]
      Closing connection to database 'MyDatabase' on server 'tcp:mydatabase.database.windows.net,1433'.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20003]
      Closed connection to database 'MyDatabase' on server 'tcp:mydatabase.database.windows.net,1433'.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20000]
      Opening connection to database 'MyDatabase' on server 'tcp:mydatabase.database.windows.net,1433'.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20001]
      Opened connection to database 'MyDatabase' on server 'tcp:mydatabase.database.windows.net,1433'.
dbug: Microsoft.EntityFrameworkCore.Database.Command[20100]
      Executing DbCommand [Parameters=[@__ef_filter__LanguageID_3='it' (Size = 3), @_outer_TenantID1='ca40afca-1a33-e811-80c2-0003ff5302d4', @_outer_CountryID='a6245729-1d33-e811-80c2-0003ff5302d4', @_outer_ID1='c45d7119-2033-e811-80c2-0003ff5302d4'], CommandType='Text', CommandTimeout='30']
      SELECT TOP(1) [qf4].[Name]
      FROM [CityNames] AS [qf4]
      WHERE ([qf4].[LanguageID] = @__ef_filter__LanguageID_3) AND (((@_outer_TenantID1 = [qf4].[TenantID]) AND (@_outer_CountryID = [qf4].[CountryID])) AND (@_outer_ID1 = [qf4].[CityID]))
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (59ms) [Parameters=[@__ef_filter__LanguageID_3='it' (Size = 3), @_outer_TenantID1='ca40afca-1a33-e811-80c2-0003ff5302d4', @_outer_CountryID='a6245729-1d33-e811-80c2-0003ff5302d4', @_outer_ID1='c45d7119-2033-e811-80c2-0003ff5302d4'], CommandType='Text', CommandTimeout='30']
      SELECT TOP(1) [qf4].[Name]
      FROM [CityNames] AS [qf4]
      WHERE ([qf4].[LanguageID] = @__ef_filter__LanguageID_3) AND (((@_outer_TenantID1 = [qf4].[TenantID]) AND (@_outer_CountryID = [qf4].[CountryID])) AND (@_outer_ID1 = [qf4].[CityID]))
dbug: Microsoft.EntityFrameworkCore.Database.Command[20300]
      A data reader was disposed.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20002]
      Closing connection to database 'MyDatabase' on server 'tcp:mydatabase.database.windows.net,1433'.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20003]
      Closed connection to database 'MyDatabase' on server 'tcp:mydatabase.database.windows.net,1433'.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20000]
      Opening connection to database 'MyDatabase' on server 'tcp:mydatabase.database.windows.net,1433'.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20001]
      Opened connection to database 'MyDatabase' on server 'tcp:mydatabase.database.windows.net,1433'.
dbug: Microsoft.EntityFrameworkCore.Database.Command[20100]
      Executing DbCommand [Parameters=[@__ef_filter__LanguageID_2='it' (Size = 3), @_outer_TenantID='ca40afca-1a33-e811-80c2-0003ff5302d4', @_outer_ID='a6245729-1d33-e811-80c2-0003ff5302d4'], CommandType='Text', CommandTimeout='30']
      SELECT TOP(1) [qf3].[Name]
      FROM [CountryNames] AS [qf3]
      WHERE ([qf3].[LanguageID] = @__ef_filter__LanguageID_2) AND ((@_outer_TenantID = [qf3].[TenantID]) AND (@_outer_ID = [qf3].[CountryID]))
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (57ms) [Parameters=[@__ef_filter__LanguageID_2='it' (Size = 3), @_outer_TenantID='ca40afca-1a33-e811-80c2-0003ff5302d4', @_outer_ID='a6245729-1d33-e811-80c2-0003ff5302d4'], CommandType='Text', CommandTimeout='30']
      SELECT TOP(1) [qf3].[Name]
      FROM [CountryNames] AS [qf3]
      WHERE ([qf3].[LanguageID] = @__ef_filter__LanguageID_2) AND ((@_outer_TenantID = [qf3].[TenantID]) AND (@_outer_ID = [qf3].[CountryID]))
dbug: Microsoft.EntityFrameworkCore.Database.Command[20300]
      A data reader was disposed.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20002]
      Closing connection to database 'MyDatabase' on server 'tcp:mydatabase.database.windows.net,1433'.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20003]
      Closed connection to database 'MyDatabase' on server 'tcp:mydatabase.database.windows.net,1433'.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20000]
      Opening connection to database 'MyDatabase' on server 'tcp:mydatabase.database.windows.net,1433'.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20001]
      Opened connection to database 'MyDatabase' on server 'tcp:mydatabase.database.windows.net,1433'.
dbug: Microsoft.EntityFrameworkCore.Database.Command[20100]
      Executing DbCommand [Parameters=[@__ef_filter__LanguageID_3='it' (Size = 3), @_outer_TenantID1='ca40afca-1a33-e811-80c2-0003ff5302d4', @_outer_CountryID='a6245729-1d33-e811-80c2-0003ff5302d4', @_outer_ID1='ba1869f1-1d33-e811-80c2-0003ff5302d4'], CommandType='Text', CommandTimeout='30']
      SELECT TOP(1) [qf4].[Name]
      FROM [CityNames] AS [qf4]
      WHERE ([qf4].[LanguageID] = @__ef_filter__LanguageID_3) AND (((@_outer_TenantID1 = [qf4].[TenantID]) AND (@_outer_CountryID = [qf4].[CountryID])) AND (@_outer_ID1 = [qf4].[CityID]))
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (67ms) [Parameters=[@__ef_filter__LanguageID_3='it' (Size = 3), @_outer_TenantID1='ca40afca-1a33-e811-80c2-0003ff5302d4', @_outer_CountryID='a6245729-1d33-e811-80c2-0003ff5302d4', @_outer_ID1='ba1869f1-1d33-e811-80c2-0003ff5302d4'], CommandType='Text', CommandTimeout='30']
      SELECT TOP(1) [qf4].[Name]
      FROM [CityNames] AS [qf4]
      WHERE ([qf4].[LanguageID] = @__ef_filter__LanguageID_3) AND (((@_outer_TenantID1 = [qf4].[TenantID]) AND (@_outer_CountryID = [qf4].[CountryID])) AND (@_outer_ID1 = [qf4].[CityID]))
dbug: Microsoft.EntityFrameworkCore.Database.Command[20300]
      A data reader was disposed.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20002]
      Closing connection to database 'MyDatabase' on server 'tcp:mydatabase.database.windows.net,1433'.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20003]
      Closed connection to database 'MyDatabase' on server 'tcp:mydatabase.database.windows.net,1433'.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20000]
      Opening connection to database 'MyDatabase' on server 'tcp:mydatabase.database.windows.net,1433'.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20001]
      Opened connection to database 'MyDatabase' on server 'tcp:mydatabase.database.windows.net,1433'.
dbug: Microsoft.EntityFrameworkCore.Database.Command[20100]
      Executing DbCommand [Parameters=[@__ef_filter__LanguageID_2='it' (Size = 3), @_outer_TenantID='ca40afca-1a33-e811-80c2-0003ff5302d4', @_outer_ID='a6245729-1d33-e811-80c2-0003ff5302d4'], CommandType='Text', CommandTimeout='30']
      SELECT TOP(1) [qf3].[Name]
      FROM [CountryNames] AS [qf3]
      WHERE ([qf3].[LanguageID] = @__ef_filter__LanguageID_2) AND ((@_outer_TenantID = [qf3].[TenantID]) AND (@_outer_ID = [qf3].[CountryID]))
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (58ms) [Parameters=[@__ef_filter__LanguageID_2='it' (Size = 3), @_outer_TenantID='ca40afca-1a33-e811-80c2-0003ff5302d4', @_outer_ID='a6245729-1d33-e811-80c2-0003ff5302d4'], CommandType='Text', CommandTimeout='30']
      SELECT TOP(1) [qf3].[Name]
      FROM [CountryNames] AS [qf3]
      WHERE ([qf3].[LanguageID] = @__ef_filter__LanguageID_2) AND ((@_outer_TenantID = [qf3].[TenantID]) AND (@_outer_ID = [qf3].[CountryID]))
dbug: Microsoft.EntityFrameworkCore.Database.Command[20300]
      A data reader was disposed.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20002]
      Closing connection to database 'MyDatabase' on server 'tcp:mydatabase.database.windows.net,1433'.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20003]
      Closed connection to database 'MyDatabase' on server 'tcp:mydatabase.database.windows.net,1433'.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20000]
      Opening connection to database 'MyDatabase' on server 'tcp:mydatabase.database.windows.net,1433'.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20001]
      Opened connection to database 'MyDatabase' on server 'tcp:mydatabase.database.windows.net,1433'.
dbug: Microsoft.EntityFrameworkCore.Database.Command[20100]
      Executing DbCommand [Parameters=[@__ef_filter__LanguageID_3='it' (Size = 3), @_outer_TenantID1='ca40afca-1a33-e811-80c2-0003ff5302d4', @_outer_CountryID='a6245729-1d33-e811-80c2-0003ff5302d4', @_outer_ID1='75f2b8b9-1e33-e811-80c2-0003ff5302d4'], CommandType='Text', CommandTimeout='30']
      SELECT TOP(1) [qf4].[Name]
      FROM [CityNames] AS [qf4]
      WHERE ([qf4].[LanguageID] = @__ef_filter__LanguageID_3) AND (((@_outer_TenantID1 = [qf4].[TenantID]) AND (@_outer_CountryID = [qf4].[CountryID])) AND (@_outer_ID1 = [qf4].[CityID]))
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (59ms) [Parameters=[@__ef_filter__LanguageID_3='it' (Size = 3), @_outer_TenantID1='ca40afca-1a33-e811-80c2-0003ff5302d4', @_outer_CountryID='a6245729-1d33-e811-80c2-0003ff5302d4', @_outer_ID1='75f2b8b9-1e33-e811-80c2-0003ff5302d4'], CommandType='Text', CommandTimeout='30']
      SELECT TOP(1) [qf4].[Name]
      FROM [CityNames] AS [qf4]
      WHERE ([qf4].[LanguageID] = @__ef_filter__LanguageID_3) AND (((@_outer_TenantID1 = [qf4].[TenantID]) AND (@_outer_CountryID = [qf4].[CountryID])) AND (@_outer_ID1 = [qf4].[CityID]))
dbug: Microsoft.EntityFrameworkCore.Database.Command[20300]
      A data reader was disposed.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20002]
      Closing connection to database 'MyDatabase' on server 'tcp:mydatabase.database.windows.net,1433'.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20003]
      Closed connection to database 'MyDatabase' on server 'tcp:mydatabase.database.windows.net,1433'.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20000]
      Opening connection to database 'MyDatabase' on server 'tcp:mydatabase.database.windows.net,1433'.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20001]
      Opened connection to database 'MyDatabase' on server 'tcp:mydatabase.database.windows.net,1433'.
dbug: Microsoft.EntityFrameworkCore.Database.Command[20100]
      Executing DbCommand [Parameters=[@__ef_filter__LanguageID_2='it' (Size = 3), @_outer_TenantID='ca40afca-1a33-e811-80c2-0003ff5302d4', @_outer_ID='a6245729-1d33-e811-80c2-0003ff5302d4'], CommandType='Text', CommandTimeout='30']
      SELECT TOP(1) [qf3].[Name]
      FROM [CountryNames] AS [qf3]
      WHERE ([qf3].[LanguageID] = @__ef_filter__LanguageID_2) AND ((@_outer_TenantID = [qf3].[TenantID]) AND (@_outer_ID = [qf3].[CountryID]))
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (59ms) [Parameters=[@__ef_filter__LanguageID_2='it' (Size = 3), @_outer_TenantID='ca40afca-1a33-e811-80c2-0003ff5302d4', @_outer_ID='a6245729-1d33-e811-80c2-0003ff5302d4'], CommandType='Text', CommandTimeout='30']
      SELECT TOP(1) [qf3].[Name]
      FROM [CountryNames] AS [qf3]
      WHERE ([qf3].[LanguageID] = @__ef_filter__LanguageID_2) AND ((@_outer_TenantID = [qf3].[TenantID]) AND (@_outer_ID = [qf3].[CountryID]))
dbug: Microsoft.EntityFrameworkCore.Database.Command[20300]
      A data reader was disposed.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20002]
      Closing connection to database 'MyDatabase' on server 'tcp:mydatabase.database.windows.net,1433'.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20003]
      Closed connection to database 'MyDatabase' on server 'tcp:mydatabase.database.windows.net,1433'.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20000]
      Opening connection to database 'MyDatabase' on server 'tcp:mydatabase.database.windows.net,1433'.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20001]
      Opened connection to database 'MyDatabase' on server 'tcp:mydatabase.database.windows.net,1433'.
dbug: Microsoft.EntityFrameworkCore.Database.Command[20100]
      Executing DbCommand [Parameters=[@__ef_filter__LanguageID_3='it' (Size = 3), @_outer_TenantID1='ca40afca-1a33-e811-80c2-0003ff5302d4', @_outer_CountryID='a6245729-1d33-e811-80c2-0003ff5302d4', @_outer_ID1='72099afd-1d33-e811-80c2-0003ff5302d4'], CommandType='Text', CommandTimeout='30']
      SELECT TOP(1) [qf4].[Name]
      FROM [CityNames] AS [qf4]
      WHERE ([qf4].[LanguageID] = @__ef_filter__LanguageID_3) AND (((@_outer_TenantID1 = [qf4].[TenantID]) AND (@_outer_CountryID = [qf4].[CountryID])) AND (@_outer_ID1 = [qf4].[CityID]))
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (77ms) [Parameters=[@__ef_filter__LanguageID_3='it' (Size = 3), @_outer_TenantID1='ca40afca-1a33-e811-80c2-0003ff5302d4', @_outer_CountryID='a6245729-1d33-e811-80c2-0003ff5302d4', @_outer_ID1='72099afd-1d33-e811-80c2-0003ff5302d4'], CommandType='Text', CommandTimeout='30']
      SELECT TOP(1) [qf4].[Name]
      FROM [CityNames] AS [qf4]
      WHERE ([qf4].[LanguageID] = @__ef_filter__LanguageID_3) AND (((@_outer_TenantID1 = [qf4].[TenantID]) AND (@_outer_CountryID = [qf4].[CountryID])) AND (@_outer_ID1 = [qf4].[CityID]))
dbug: Microsoft.EntityFrameworkCore.Database.Command[20300]
      A data reader was disposed.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20002]
      Closing connection to database 'MyDatabase' on server 'tcp:mydatabase.database.windows.net,1433'.
dbug: Microsoft.EntityFrameworkCore.Database.Connection[20003]
      Closed connection to database 'MyDatabase' on server 'tcp:mydatabase.database.windows.net,1433'.
dbug: Microsoft.EntityFrameworkCore.Infrastructure[10407]
      'MyContext' disposed.
Program finished.
smitpatel commented 6 years ago

Repro: Model

    public class Blog
    {
        public int Id { get; set; }
        [NotMapped]
        public string Title { get; set; }
        public List<Post> Posts { get; set; }
    }
    public class Post
    {
        public int Id { get; set; }
        public int BlogId { get; set; }
        public Blog Blog { get; set; }
        public string Name { get; set; }
    }

Query

var blog = db.Blogs.Select(
    e => new
    {
        Id = e.Id,
        Title = e.Title,
        FirstPostName = e.Posts.OrderBy(i => i.Id).FirstOrDefault().Name
    }).ToList();

Generates N + 1 queries. If Title is mapped then it is getting lifted correctly. We should lift the subquery regardless of there is client eval or not.