Efcore: Missing schema in generated SQL for ThenInclude

[omatrot]

I have the following EF query:

 var initialList = await

                        context.AlertDetails
                        .Include(ad => ad.TrackedVehicule).ThenInclude(v => v.Device)
                        .Include(ad => ad.TrackedVehicule).ThenInclude(v => v.GpsBoxTypeNavigation).ThenInclude(bt => bt.Icon)
                        .Include(ad => ad.Alert)
                        .Where(ad => ad.Alert.CompanyId == companyId && ad.TrackedVehicule != null && ad.TrackedVehicule.RowEnabled == true && ad.TrackedVehicule.CompanyId == companyId && ad.TrackedVehicule.GpsBoxType == 29)
                         // A limit is applied on returned elements
                         .Take(1000)
                         .OrderByDescending(ad => ad.AlertDateTime)
                         // We disable the tracking mechanism because the context will be thrown away as soon as we have the data
                         .AsNoTracking()
                         // We want an asynchrounous execution
                         .ToListAsync()
                         // We do not need to preserve the context because we're immdiately finished
                         .ConfigureAwait(continueOnCapturedContext: false);

The Icon entity is configured as this:

           builder.Entity<Icon>(entity =>
            {
                entity.ToTable("Icon", "cst");
                entity.Property(e => e.IconId).ValueGeneratedNever();
                entity.Property(e => e.Category)
                    .IsRequired()
                    .HasMaxLength(128)
                    .IsUnicode(false);
                entity.Property(e => e.FileName)
                    .IsRequired()
                    .HasMaxLength(128)
                    .IsUnicode(false);

We can see that the Icon table belongs to the cst schema.

However, the generated SQL do not include the schema name, thus the query is failing:

exec sp_executesql N'SELECT [t].[AlertDetailID], [t].[AckMachineName], [t].[AckPhoneNumber], [t].[AckTime], [t].[AckUserName], [t].[AlertDateTime], [t].[AlertID], [t].[AlertedCorridorId], [t].[AlertedItemId], [t].[AssociatedVehiculeID], [t].[AssociatedVehiculeUserID], [t].[Context], [t].[CustomID], [t].[CustomInfo], [t].[DbInsertTime], [t].[IsFleetAlert], [t].[MessageStatus], [t].[ReceivedTime], [t].[RowVersion], [t].[SafeProtectCustomInfo], [t].[TrackedVehiculeID], [t].[TrackedVehiculeUserID], [v0].[VehiculeID], [v0].[Address], [v0].[AddressProtocol], [v0].[BoardID], [v0].[Category], [v0].[CompanyID], [v0].[CustomId], [v0].[DbInsertTime], [v0].[Description], [v0].[GpsBoxTrackingDelay], [v0].[GpsBoxType], [v0].[HardwareID], [v0].[HasGeoWorker], [v0].[IconID], [v0].[Name], [v0].[PhoneNumber], [v0].[RowEnabled], [v0].[RowVersion], [v0].[VehiculeUserID], [d].[Imei], [d].[AppVersion], [d].[Brand], [d].[Details], [d].[Model], [d].[Name], [d].[OsVersion], [d].[RowVersion], [g].[GpsBoxTypeId], [g].[Category], [g].[IconId], [g].[Name], [i].[IconId], [i].[Category], [i].[FileName], [a1].[AlertID], [a1].[AlertEnabled], [a1].[AlertRule], [a1].[Always], [a1].[CalendarID], [a1].[Category], [a1].[CompanyID], [a1].[Description], [a1].[DisplayName], [a1].[RowEnabled], [a1].[RowVersion], [a1].[Shared], [a1].[UserID]
FROM (
    SELECT TOP(@__p_1) [a].[AlertDetailID], [a].[AckMachineName], [a].[AckPhoneNumber], [a].[AckTime], [a].[AckUserName], [a].[AlertDateTime], [a].[AlertID], [a].[AlertedCorridorId], [a].[AlertedItemId], [a].[AssociatedVehiculeID], [a].[AssociatedVehiculeUserID], [a].[Context], [a].[CustomID], [a].[CustomInfo], [a].[DbInsertTime], [a].[IsFleetAlert], [a].[MessageStatus], [a].[ReceivedTime], [a].[RowVersion], [a].[SafeProtectCustomInfo], [a].[TrackedVehiculeID], [a].[TrackedVehiculeUserID], [a0].[AlertID] AS [AlertID0]
    FROM [AlertDetail] AS [a]
    INNER JOIN [Alert] AS [a0] ON [a].[AlertID] = [a0].[AlertID]
    LEFT JOIN [Vehicule] AS [v] ON [a].[TrackedVehiculeID] = [v].[VehiculeID]
    WHERE ((((([a0].[CompanyID] = @__companyId_0) AND @__companyId_0 IS NOT NULL) AND [v].[VehiculeID] IS NOT NULL) AND (([v].[RowEnabled] = CAST(1 AS bit)) AND [v].[RowEnabled] IS NOT NULL)) AND ((([v].[CompanyID] = @__companyId_0) AND ([v].[CompanyID] IS NOT NULL AND @__companyId_0 IS NOT NULL)) OR ([v].[CompanyID] IS NULL AND @__companyId_0 IS NULL))) AND (([v].[GpsBoxType] = 29) AND [v].[GpsBoxType] IS NOT NULL)
) AS [t]
LEFT JOIN [Vehicule] AS [v0] ON [t].[TrackedVehiculeID] = [v0].[VehiculeID]
LEFT JOIN [SafeProtect].[Device] AS [d] ON [v0].[HardwareID] = [d].[Imei]
LEFT JOIN [GpsBoxType] AS [g] ON [v0].[GpsBoxType] = [g].[GpsBoxTypeId]
LEFT JOIN [Icon] AS [i] ON [g].[IconId] = [i].[IconId]
INNER JOIN [Alert] AS [a1] ON [t].[AlertID] = [a1].[AlertID]
ORDER BY [t].[AlertDateTime] DESC',N'@__p_1 int,@__companyId_0 int',@__p_1=1000,@__companyId_0=1

The exception is:

System.AggregateException
  HResult=0x80131500
  Message=One or more errors occurred. (Invalid object name 'Icon'.)
  Source=System.Private.CoreLib
  StackTrace:
   at System.Threading.Tasks.Task.ThrowIfExceptional(Boolean includeTaskCanceledExceptions)
   at System.Threading.Tasks.Task.Wait(Int32 millisecondsTimeout, CancellationToken cancellationToken)
   at System.Threading.Tasks.Task.Wait()
   at RTE.Technologies.SafeProtect.Common.DummyConsole.Program.Main(String[] args) in C:\Users\olivier.matrot\source\repos\SafeProtect\SafeProtect.Common\SafeProtect.Common.Data.ConsoleTest\Program.cs:line 221

  This exception was originally thrown at this call stack:
    Microsoft.Data.SqlClient.SqlCommand.ExecuteDbDataReaderAsync.AnonymousMethod__164_0(System.Threading.Tasks.Task<Microsoft.Data.SqlClient.SqlDataReader>)
    System.Threading.Tasks.ContinuationResultTaskFromResultTask<TAntecedentResult, TResult>.InnerInvoke()
    System.Threading.Tasks.Task..cctor.AnonymousMethod__274_0(object)
    System.Threading.ExecutionContext.RunInternal(System.Threading.ExecutionContext, System.Threading.ContextCallback, object)
    System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
    System.Threading.ExecutionContext.RunInternal(System.Threading.ExecutionContext, System.Threading.ContextCallback, object)
    System.Threading.Tasks.Task.ExecuteWithThreadLocal(ref System.Threading.Tasks.Task, System.Threading.Thread)
    System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
    System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(System.Threading.Tasks.Task)
    System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(System.Threading.Tasks.Task)
    ...
    [Call Stack Truncated]
Inner Exception 1:
SqlException: Invalid object name 'Icon'.

The correct query needs to include the schema name in front of [Icon]. It should read [cst].[Icon]

Further technical details

EF Core version: 3.1.0-preview1.19528.8
Database provider: (e.g. Microsoft.EntityFrameworkCore.SqlServer)
Target framework: (e.g. .NET Core 3.1 preview)
Operating system: Windows 10
IDE: Visual Studio 2019 16.4.0 Preview 3.

[smitpatel]

@aspnet/efteam should investigate this for 3.1. Code looks fine in flowing schema.

[ajcvickers]

@smitpatel Do you think we will be able to reproduce this with just the information provided above? Do you think we will hit this even for simple queries?

[smitpatel]

We need to test scenario that if you are mapping entityTypes to multiple schemas in the model, we are generating a query involving joins between those tables with correct schema identifiers. I think we don't have test covering multiple schemas in the query. Though I don't believe it is a bug. Just want to make sure we have basic scenario working where there are multiple schemas.

[omatrot]

Here is another query that works well:

```
var initialList = await

                    // This is the base query that starts with the vehicules filtered on the company Id
                    (from vehicule in context.Vehicle

                         // Filter on the company id
                     where vehicule.CompanyId == companyID

                     && vehicule.HardwareId == imei

                     select vehicule)

                     // We include the Device related entity
                     .Include(v => v.Device).ThenInclude(d => d.Registration)

                     // We need the GpsBoxType Name
                     .Include(v => v.GpsBoxTypeNavigation).ThenInclude(t => t.Icon)

                     // Include GPsBoxInfo related entity
                     .Include(v3 => v3.VehiculeGpsBoxInfo).ThenInclude(a => a.VehiculeConfigurationNavigation)

                     // We Include the FleetDetails collection
                     .IncludeFilter(v => v.FleetDetail.Where(fd => fd.RowEnabled == true)).IncludeFilter(v2 => v2.FleetDetail.Select(u => u.Fleet).Where(f => f.RowEnabled == true && f.CompanyId == companyID))


                     // We disable the tracking mechanism because the context will be thrown away as soon as we have the data
                     .AsNoTracking()

                     // We want an asynchrounous execution
                     .SingleOrDefaultAsync()

                     // We do not need to preserve the context because we're immdiately finished
                     .ConfigureAwait(continueOnCapturedContext: false);

```

This seems not be related to the use of ThenInclude alone.
May be this is related to the filtering used in the failing query?

[ajcvickers]

@smitpatel Common cases using schema seems to work--see below.

@omatrot I have not been able to reproduce this. Please post a small, runnable project/solution or complete code listing that demonstrates the behavior you are seeing.

```C#
public class Blog
{
public int Id { get; set; }
public string Title { get; set; }

public ICollection<Post> Posts { get; set; }

}

public class Post
{
public int Id { get; set; }
public string Content { get; set; }

public Blog Blog { get; set; }

}

public class BloggingContext : DbContext
{
private static readonly ILoggerFactory Logger = LoggerFactory.Create(x => x.AddConsole());

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Blog>().ToTable("Blog", "s1");
    modelBuilder.Entity<Post>()
        .ToTable("Post", "s2")
        .HasQueryFilter(p => p.Blog.Id == 0);
}

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    => optionsBuilder
        .UseLoggerFactory(Logger)
        .UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=Test;ConnectRetryCount=0");

}

public class Program
{
public static async Task Main()
{
using (var context = new BloggingContext())
{
context.Database.EnsureDeleted();
context.Database.EnsureCreated();

        context.Add(new Blog
        {
            Posts = new List<Post>
            {
                new Post(),
                new Post()
            }
        });

        context.SaveChanges();
    }

    using (var context = new BloggingContext())
    {
        var blogs = await context.Set<Blog>().Include(e => e.Posts).ToListAsync();
        var posts = await context.Set<Post>().Include(e => e.Blog).ToListAsync();
    }
}

}
```

[omatrot]

I would say that this breaks in my case because I'm also filtering on a reference navigation property. Without that kind of filtering it works well indeed. I'm also using 2 levels of ThenInclude but it works well when I'm not filtering, see above.

[ajcvickers]

@omatrot I've tried various filters on navigation properties and I still cannot reproduce this.

[omatrot]

@ajcvickers I'll try to build a failing sample tomorrow.

[omatrot]

Closing as it was an issue on my side, the Icon identity was not properly configured for the context I was using.