Efcore: The use of the ToListAsync method leads to missing data in version 2.1.0 of Microsoft.AspNetCore.All

[pavlospap]

Hello everybody!

I have an issue since I changed one of my Asp.Net Core projects to target .Net Core 2.1 and updated to the version 2.1.0 of the _Microsoft.AspNetCore.All_ NuGet package. I'm not sure if this is an EF Core issue so move this in the appropriate repo if necessary!

I'm attaching a demo project that reproduces the issue. The url to the controller's test action is _http://localhost:8099/demo/test_.

If someone changes the version of the _Microsoft.AspNetCore.All_ package from 2.1.0 back to 2.0.8 everything works as intended.

So, I have an entity with two details.

```C#
public class User
{
public int Id { get; set; }
public string UserName { get; set; }

public ICollection<Email> Emails { get; set; }
public ICollection<Phone> Phones { get; set; }

}

What I want is to build a query and then execute it asynchronously using the _ToListAsync_ extension. Since the details implement _ICollection_, in order to select specific fields, I use the _ToList_ extension. But that would lead to the execution of the query before the call of the _ToListAsync_. So I figured out a way to avoid this by using the _Task.FromResult_ method (I don't know if this is the best solution though).

```C#
var query = _dbContext.Users
    .Select(user => new User
    {
        Emails = Task.FromResult(user.Emails.Select(email => new Email { Address = email.Address }).ToList()).Result,
        Phones = Task.FromResult(user.Phones.Select(phone => new Phone { Number = phone.Number }).ToList()).Result
    });

var result = await query.ToListAsync();

The problem is that, after the call of the _ToListAsync_, only the second detail (Phones) has data and the first one (Emails) is empty. The problem disappears when I change back to version 2.0.8 of the _Microsoft.AspNetCore.All_ package.

The Debug Output of the 2.1.0 version during the execution of the _ToListAsync_ method is:

Microsoft.EntityFrameworkCore.Database.Command:Information: Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
PRAGMA foreign_keys=ON;
Microsoft.EntityFrameworkCore.Database.Command:Information: Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT "user"."Id"
FROM "Users" AS "user"
ORDER BY "user"."Id"
Microsoft.EntityFrameworkCore.Database.Command:Information: Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT "t"."Id", "user.Emails"."Address", "user.Emails"."UserId"
FROM "Emails" AS "user.Emails"
INNER JOIN (
SELECT "user0"."Id"
FROM "Users" AS "user0"
) AS "t" ON "user.Emails"."UserId" = "t"."Id"
ORDER BY "t"."Id"
Microsoft.EntityFrameworkCore.Database.Command:Information: Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT "t0"."Id", "user.Phones"."Number", "user.Phones"."UserId"
FROM "Phones" AS "user.Phones"
INNER JOIN (
SELECT "user1"."Id"
FROM "Users" AS "user1"
) AS "t0" ON "user.Phones"."UserId" = "t0"."Id"
ORDER BY "t0"."Id"

which is different from the Debug Output of the 2.0.8 version:

Microsoft.AspNetCore.Server.Kestrel:Warning: Heartbeat took longer than "00:00:01" at "06/14/2018 12:09:33 +00:00".
Microsoft.EntityFrameworkCore.Database.Command:Information: Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
PRAGMA foreign_keys=ON;
Microsoft.EntityFrameworkCore.Database.Command:Information: Executed DbCommand (2ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT "user"."Id"
FROM "Users" AS "user"
Microsoft.EntityFrameworkCore.Database.Command:Information: Executed DbCommand (10ms) [Parameters=[@_outer_Id='?'], CommandType='Text', CommandTimeout='30']
SELECT "email"."Address"
FROM "Emails" AS "email"
WHERE @_outer_Id = "email"."UserId"
Microsoft.EntityFrameworkCore.Database.Command:Information: Executed DbCommand (0ms) [Parameters=[@_outer_Id='?'], CommandType='Text', CommandTimeout='30']
SELECT "email"."Address"
FROM "Emails" AS "email"
WHERE @_outer_Id = "email"."UserId"
Microsoft.EntityFrameworkCore.Database.Command:Information: Executed DbCommand (0ms) [Parameters=[@_outer_Id1='?'], CommandType='Text', CommandTimeout='30']
SELECT "phone"."Number"
FROM "Phones" AS "phone"
WHERE @_outer_Id1 = "phone"."UserId"
Microsoft.EntityFrameworkCore.Database.Command:Information: Executed DbCommand (0ms) [Parameters=[@_outer_Id1='?'], CommandType='Text', CommandTimeout='30']
SELECT "phone"."Number"
FROM "Phones" AS "phone"
WHERE @_outer_Id1 = "phone"."UserId"

Did the 2.1.0 package introduced a bug there? Any ideas?

Thanx everyone in advance!

Further technical details

EF Core version: 2.1.0
Database Provider: Microsoft.EntityFrameworkCore.Sqlite
Operating system: win10 1703
IDE: Visual Studio 2017 15.7.3

[ajcvickers]

@pavlospap I wasn't able to completely follow your intention, but if it is to load Users together with their Emails and Phones, then the normal way to do this is with Include:
C# var users = await _dbContext.Users .Include(e => e.Emails) .Include(e => e.Phones) .ToListAsync();

[pavlospap]

@ajcvickers thank you very much for your response but this is not what I need since, this way, every Email's and Phone's column will be included in the select statement in the query that will be executed in the database. I need to include only specific columns.

[smitpatel]

But that would lead to the execution of the query before the call of the ToListAsync

That is not true. Since you are not calling ToList on IQueryable (it is inside lambda), so it is not going to cause the Queryable to enumerate. Hence ToList wouldn't do anything till you call final ToListAsync. Further since you cannot use ToListAsync inside lambda expression, but you want to run query asynchronously (by use of ToListAsync), EF Core will behind the scene convert all ToList in expression to async version and run them (if there are going to be multiple queries) async.

```C#
var query = _dbContext.Users
.Select(user => new User
{
Emails = user.Emails.Select(email => new Email { Address = email.Address }).ToList(),
Phones = user.Phones.Select(phone => new Phone { Number = phone.Number }).ToList()
});

var result = await query.ToListAsync();
`` Will generate the same results as you want and query won't execute anything tillToListAsync` is called.

[smitpatel]

```C#
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;

namespace EFSampleApp
{
public class Program
{
public static void Main(string[] args)
{
using (var db = new MyContext())
{
// Recreate database
db.Database.EnsureDeleted();
db.Database.EnsureCreated();

            // Seed database
            db.Add(new User
            {
                UserName = "Test",
                Emails = new List<Email>
                {
                    new Email{ Address = "abc"}
                },
                Phones = new List<Phone>
                {
                    new Phone
                    {
                        Number = 124
                    }
                }
            });

            db.SaveChanges();
        }

        using (var db = new MyContext())
        {
            // Run queries
            var query = db.Users
                .Select(user => new User
                {
                    Emails = Task.FromResult(user.Emails.Select(email => new Email { Address = email.Address }).ToList()).Result,
                    Phones = Task.FromResult(user.Phones.Select(phone => new Phone { Number = phone.Number }).ToList()).Result
                });
            var result = query.ToListAsync().GetAwaiter().GetResult();

            Console.WriteLine(result[0].Emails.Count);
            Console.WriteLine(result[0].Phones.Count);
        }

        Console.WriteLine("Program finished.");
    }
}


public class MyContext : DbContext
{
    private static ILoggerFactory LoggerFactory => new LoggerFactory().AddConsole(LogLevel.Trace);

    // Declare DBSets
    public DbSet<Blog> Blogs { get; set; }
    public DbSet<User> Users { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        // Select 1 provider
        optionsBuilder
            .UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=_ModelApp;Trusted_Connection=True;Connect Timeout=5;ConnectRetryCount=0")
            //.UseSqlite("filename=_modelApp.db")
            //.UseInMemoryDatabase(databaseName: "_modelApp")
            .EnableSensitiveDataLogging()
            .UseLoggerFactory(LoggerFactory);
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // Configure model
    }
}

public class Blog
{
    public int Id { get; set; }
}

public class User
{
    public int Id { get; set; }
    public string UserName { get; set; }

    public ICollection<Email> Emails { get; set; }
    public ICollection<Phone> Phones { get; set; }
}

public class Email
{
    public int Id { get; set; }
    public string Address { get; set; }
}

public class Phone
{
    public int Id { get; set; }
    public int Number { get; set; }
}

}
```
Generates output as 0 & 1 instead of 1 & 1. Potential data corruption issue.
Used to work correctly in 2.0 release. Issue in correlated subquery optimization.

[pavlospap]

@smitpatel you are absolutely right! I was pretty sure that ToList would cause the Queryable to enumerate. I don't know why I had that idea. I saw the Debug Output and, indeed, nothing happens before ToListAsync is executed. Was that always the case with previous versions? Because I really thought I've seen, during the debugging process, db queries being executed before ToListAsync was called. Obviously I was wrong. Thank you very much for the explanation.

[maumar]

Problem is in the TaskLiftingExpressionVisitor. We get the following expression as input:

Emails = Task<List<Email>> FromResult(List<Email> Result(Task<List<Email>> Unhandled parameter: queryContext.QueryBuffer.CorrelateSubqueryAsync(
    correlatedCollectionId: 0, 
    navigation: User.Emails, 
    resultCollectionFactory: (INavigation n) => new List<Email>(), 
    outerKey: new MaterializedAnonymousObject(new object[]{ (object)!(Unhandled parameter: user.IsEmpty) ? Nullable<int> TryReadValue(Unhandled parameter: user, 0, User.Id) : default(Nullable<int>) }), 
    tracking: False, 
    correlatedCollectionFactory: (Func<IAsyncEnumerable<Tuple<Email, MaterializedAnonymousObject, MaterializedAnonymousObject>>>)() => IAsyncEnumerable<Tuple<Email, MaterializedAnonymousObject, MaterializedAnonymousObject>> _ShapedQuery(
        queryContext: Unhandled parameter: queryContext, 
        shaperCommandContext: SelectExpression: 
            SELECT [t].[Id], [user.Emails].[Address], [user.Emails].[UserId]
            FROM [Email] AS [user.Emails]
            INNER JOIN (
                SELECT [user0].[Id]
                FROM [Users] AS [user0]
            ) AS [t] ON [user.Emails].[UserId] = [t].[Id]
            ORDER BY [t].[Id], 
        shaper: TypedProjectionShaper<...>), 
    correlationPredicate: (MaterializedAnonymousObject o | MaterializedAnonymousObject i) => object i.GetValue(0) == default(object) || object o.GetValue(0) == default(object) ? False : (Nullable<int>)(int)object o.GetValue(0) == (Nullable<int>)object i.GetValue(0), 
    cancellationToken: Unhandled parameter: ct))).Result, 

and it gets lifted twice (FromResult call and CorrelateSubqueryAsync call):

Task<User> _ExecuteAsync(
    taskFactories: new Func<Task<object>>[]
    { 
        () => Task<object> _ToObjectTask(Task<List<Email>> FromResult(List<Email> Result(Task<List<Email>> Unhandled parameter: queryContext.QueryBuffer.CorrelateSubqueryAsync(
            correlatedCollectionId: 0, 
            navigation: User.Emails, 
            resultCollectionFactory: (INavigation n) => new List<Email>(), 
            outerKey: new MaterializedAnonymousObject(new object[]{ (object)!(Unhandled parameter: user.IsEmpty) ? Nullable<int> TryReadValue(Unhandled parameter: user, 0, User.Id) : default(Nullable<int>) }), 
            tracking: False, 
            correlatedCollectionFactory: (Func<IAsyncEnumerable<Tuple<Email, MaterializedAnonymousObject, MaterializedAnonymousObject>>>)() => IAsyncEnumerable<Tuple<Email, MaterializedAnonymousObject, MaterializedAnonymousObject>> _ShapedQuery(
                queryContext: Unhandled parameter: queryContext, 
                shaperCommandContext: SelectExpression: 
                    SELECT [t].[Id], [user.Emails].[Address], [user.Emails].[UserId]
                    FROM [Email] AS [user.Emails]
                    INNER JOIN (
                        SELECT [user0].[Id]
                        FROM [Users] AS [user0]
                    ) AS [t] ON [user.Emails].[UserId] = [t].[Id]
                    ORDER BY [t].[Id], 
                shaper: TypedProjectionShaper<...>), 
            correlationPredicate: (MaterializedAnonymousObject o | MaterializedAnonymousObject i) => object i.GetValue(0) == default(object) || object o.GetValue(0) == default(object) ? False : (Nullable<int>)(int)object o.GetValue(0) == (Nullable<int>)object i.GetValue(0), 
            cancellationToken: Unhandled parameter: ct)))), 
        () => Task<object> _ToObjectTask(Task<List<Email>> Unhandled parameter: queryContext.QueryBuffer.CorrelateSubqueryAsync(
            correlatedCollectionId: 0, 
            navigation: User.Emails, 
            resultCollectionFactory: (INavigation n) => new List<Email>(), 
            outerKey: new MaterializedAnonymousObject(new object[]{ (object)!(Unhandled parameter: user.IsEmpty) ? Nullable<int> TryReadValue(Unhandled parameter: user, 0, User.Id) : default(Nullable<int>) }), 
            tracking: False, 
            correlatedCollectionFactory: (Func<IAsyncEnumerable<Tuple<Email, MaterializedAnonymousObject, MaterializedAnonymousObject>>>)() => IAsyncEnumerable<Tuple<Email, MaterializedAnonymousObject, MaterializedAnonymousObject>> _ShapedQuery(
                queryContext: Unhandled parameter: queryContext, 
                shaperCommandContext: SelectExpression: 
                    SELECT [t].[Id], [user.Emails].[Address], [user.Emails].[UserId]
                    FROM [Email] AS [user.Emails]
                    INNER JOIN (
                        SELECT [user0].[Id]
                        FROM [Users] AS [user0]
                    ) AS [t] ON [user.Emails].[UserId] = [t].[Id]
                    ORDER BY [t].[Id], 
                shaper: TypedProjectionShaper<...>), 
            correlationPredicate: (MaterializedAnonymousObject o | MaterializedAnonymousObject i) => object i.GetValue(0) == default(object) || object o.GetValue(0) == default(object) ? False : (Nullable<int>)(int)object o.GetValue(0) == (Nullable<int>)object i.GetValue(0), 
            cancellationToken: Unhandled parameter: ct)), 

Same CorrelateCollection call (i.e. same correlatedCollectionId) is executed twice. Both executions share the same enumerator, so the first time it populates all entities, and second one finds nothing - hence the 0 elements we are seeing;

[maumar]

fixed in 3d2ba391cc1d8d6cc7b0a67ced4539067ad07a64