Efcore: EFCore Projection to Children get's lost when using Skip() / Take()

[LMSSonos]

When I run the following code (which worked in EFCore 2.0.3) it fails in the method Projection_Children_With_Take_Fails when accessing the Address.AddressTags.Tag.TagName property because the Tag object is null.

It does work if no Skip/Take is executed. It also works, when it's run in the same context where we first execute the query without skip/take.

Maybe it's related to #18374

Issue shows up with SqlServer and InMemory

Steps to reproduce

Execute the following example

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

namespace EFCore.SkipTakeProjectionBug
{
static class Program
{
///

/// Parent
///

public class Address
{
public Guid AddressId { get; set; } = Guid.NewGuid();
public string CompanyName { get; set; }
public ICollection AddressTags { get; set; }
}

    /// <summary>
    /// Child object
    /// </summary>
    public class AddressTag
    {
        public Guid AddressId { get; set; }
        public Address Address { get; set; }
        public Guid TagId { get; set; }
        public Tag Tag { get; set; }
    }

    /// <summary>
    /// Child of child object
    /// </summary>
    public class Tag
    {
        public Guid TagId { get; set; } = Guid.NewGuid();
        public string TagName { get; set; }
    }

    /// <summary>
    /// Projection model
    /// </summary>
    public class AddressViewModel
    {
        public string CompanyName { get; set; }
        public List<AddressTag> AddressTags { get; set; }
    }

    public class TestContext : DbContext
    {
        public DbSet<Address> Addresses { get; set; }
        public DbSet<AddressTag> AddressTags { get; set; }
        public DbSet<Tag> Tags { get; set; }

        public TestContext(DbContextOptions<TestContext> options)
            : base(options)
        { }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseInMemoryDatabase("bug");
            //optionsBuilder.UseSqlServer(@"Server=.\sqlexpress;Initial Catalog=efcoretest;Integrated Security=SSPI");
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            var configAddressTag = modelBuilder.Entity<AddressTag>();
            configAddressTag.HasKey(x => new { x.TagId, x.AddressId });

            base.OnModelCreating(modelBuilder);
        }
    }

    static async Task Main(string[] args)
    {
        var optionsBuilder = new DbContextOptionsBuilder<TestContext>();
        using (var ctx = new TestContext(optionsBuilder.Options))
        {
            ctx.Database.EnsureDeleted();
            ctx.Database.EnsureCreated();

            var tag1 = new Tag { TagName = "tag one" };
            var tag2 = new Tag { TagName = "tag two" };
            var adr = new Address { CompanyName = "Contoso" };
            ctx.Tags.Add(tag1);
            ctx.Tags.Add(tag2);
            ctx.Addresses.Add(adr);
            ctx.AddressTags.Add(new AddressTag { Address = adr, Tag = tag1 });
            ctx.AddressTags.Add(new AddressTag { Address = adr, Tag = tag2 });
            ctx.SaveChanges();
        }

        using (var ctx = new TestContext(optionsBuilder.Options))
        {
            Projection_Children_Without_Take_Works(ctx); //Works
            Projection_Children_With_Take_Fails(ctx); // Works ??
        }

        using (var ctx = new TestContext(optionsBuilder.Options))
        {
            Projection_Children_With_Take_Fails(ctx); // Fails
        }
    }

    static void Projection_Children_Without_Take_Works(TestContext ctx)
    {
        var query = ctx.Addresses.Include(x => x.AddressTags).ThenInclude(x => x.Tag)
        .Select(x => new AddressViewModel { CompanyName = x.CompanyName, AddressTags = x.AddressTags.ToList() })
        .AsQueryable();
        var first = query.First();

        Console.WriteLine(first.CompanyName);
        Console.WriteLine(first.AddressTags.First().Tag.TagName);
    }

    static void Projection_Children_With_Take_Fails(TestContext ctx)
    {
        var query = ctx.Addresses.Include(x => x.AddressTags).ThenInclude(x => x.Tag)
        .Select(x => new AddressViewModel { CompanyName = x.CompanyName, AddressTags = x.AddressTags.ToList() })
        .AsQueryable();
        var first = query.Skip(0).Take(1).First();

        Console.WriteLine(first.CompanyName);
        // Failure is here when using skip take, the Tag child is not filled
        // except when running on first context with the code above
        Console.WriteLine(first.AddressTags.First().Tag.TagName);
    }
}

}

```

Further technical details

EF Core version: Reproduces on: 3.0.0, 5.0.0-alpha1.19529.5, 3.1.0-preview1.19506.2
Database provider: Microsoft.EntityFrameworkCore.SqlServer & Inmemory
Target framework: .NET Core 3.0
Operating system: Windows
IDE:Visual Studio 2019 16.3.7

[smitpatel]

Note for triage: Need to try on nightly builds, there were couple of PRs which went in which could affect this, but it still may remain as bug.

[ajcvickers]

@smitpatel Still fails with 3.1 preview 3.

[smitpatel]

Do we want to fix this for 3.1? I can investigate (when I get reliable internet)

[ajcvickers]

@smitpatel Possibly. Is it something you think we would patch? (Since the 3.1 bar is basically now the same as the patch bar.) In other words, how bad is it?

[smitpatel]

Ignored includes mainly, which are easy to find out if you are using it. Work-around is to apply skip/take beforehand doing collection projection.

[ajcvickers]

@smitpatel Workaround seems okay for now. We can patch if we get more feedback.

[LMSSonos]

@smitpatel thanks for the workaround suggestion, but @ajcvickers to be honest, applying the workaround is to search through all queries in the code, in a big application it's a lot of work. Especially as you only get the exception at runtime and it was working in EF2.2 - would be nice if it makes into 3.1

[ajcvickers]

Note from triage: we discussed taking this for 3.1, but the risk of breaking other things with a change here is quite high, so based on current value/risk ratio we're not going to take it yet. We could revisit this with feedback from other customers, in which case we will look into quirking.

[SpaceDevOps]

Hi, we just upgraded our application from EF 2.2 to EF 3.0 and ran into the same exception. Is there any option to find the issue using an analyzer?
Searching for all the places where I have to apply the fix gets complicated in your project.
Thank you.

[ajcvickers]

@SpaceDevOps Unfortunately we don't have any analyzers like this.

[saciervo]

I think this is problematic from a product lifecycle standpoint. It's perfectly understandable that devs would need to check on every single query in an application when migrating from EF6 to EFCore, since the underlying technology is completely new. But this is something that used to work in EFCore 2 and now it doesn't work in EFCore 3, but will may be fixed in EFCore 5 🤯

I think there should at least be some "official" advice or a migration path on this (not being "wait for EFCore 5") for the devs that have to maintain and migrate large applications. Like a tool that analyzes the solution and reports all queries that are not EFCore 3 compatible. With such a tool you probably don't even need to fix this issue in EFCore 5, which is a good thing if it really requires quirks to have it back.

[LMSSonos]

@smitpatel thanks for the fix. can i find this in a 3.x nightly build to test?

[collinbarrett]

Can anyone suggest a workaround for this when it does not seem to be possible to move the Take() before the Select() (as suggested by @smitpatel)?

For example, here, the Take() depends on a filtered set from a Select().Where(). The query translates fine without the Take(), but throws the System.ArgumentNullException when adding the Take(). (It's returning IAsyncEnumerable per an interface, but this specific implementation should only return a single SystemAlert.)

public IAsyncEnumerable<SystemAlert> GetAlertsAsync()
{
    return _dbContext.Member.AsNoTracking()
        .Select(m => new
        {
            ParticipantName = m.Name,
            HasAlert = m.LatestCertContact != null  // abbreviated, but this is actually a bigger subquery
        })
        .Where(r => r.HasAlert)
        .Take(1)
        .Select(r => new SystemAlert(r.ParticipantName, AlertText))
        .AsAsyncEnumerable();
}

[smitpatel]

You can move Take before Select, it is bit more verbose.
C# public IAsyncEnumerable<SystemAlert> GetAlertsAsync() { return _dbContext.Member.AsNoTracking() .Where(m => m.LatestCertContact != null // abbreviated, but this is actually a bigger subquery) .Take(1) .Select(m => new { ParticipantName = m.Name, HasAlert = m.LatestCertContact != null // abbreviated, but this is actually a bigger subquery }) .Select(r => new SystemAlert(r.ParticipantName, AlertText)) .AsAsyncEnumerable(); }

[collinbarrett]

Duh. That works. Thanks!

[m4ss1m0g]

Ignored includes mainly, which are easy to find out if you are using it. Work-around is to apply skip/take beforehand doing collection projection.

This bug is still present on Microsoft.AspNetCore.OData 7.4.1 with EFCore 3.1.5 calling the Api from the Postman.
I must select ALL property explicit to get the complex properties