sql server does not support correlated subquery in Take(n).Tolist()

[damirci]

I have a simple query with projection and correlated subquery and I have expect one query for query not N+1. I have to say that this code generates query that I expect with EF6.
and I know that query optimization added in 2.1 but I can not figure out this behavior.

Steps to reproduce

different queries are generate for each record for post.PostDisableCoins.ToList().

source.Select(post => new PostProject
            {
                
                PostDisableCoins = post.PostDisableCoins.ToList(),
                WarStartTime = post.WarStartTime,
                WarEndTime = post.WarEndTime,
                WarWinner = post.WarWinner,
                WarDeclarer = post.WarDeclarer
            });

Further technical details

EF Core version: 2.2
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system:
IDE: Visual Studio 2017 15.8.7

[ajcvickers]

@damirci We have not been able to reproduce this--my test code is below. Can you post a runnable project/solution or complete code listing like mine below that reproduces the behavior you are seeing?

public class Post
{
    public int Id { get; set; }
    public ICollection<Coin> PostDisableCoins { get; set; }
    public DateTime WarStartTime { get; set; }
    public DateTime WarEndTime { get; set; }
    public string WarWinner { get; set; }
    public string WarDeclarer { get; set; }
}

public class PostProject
{
    public ICollection<Coin> PostDisableCoins { get; set; }
    public DateTime WarStartTime { get; set; }
    public DateTime WarEndTime { get; set; }
    public string WarWinner { get; set; }
    public string WarDeclarer { get; set; }
}

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

public class BloggingContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=Test;ConnectRetryCount=0");

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

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

            context.Add(new Post { PostDisableCoins = new List<Coin> { new Coin(), new Coin()}});
            context.Add(new Post { PostDisableCoins = new List<Coin> { new Coin(), new Coin() } });
            context.SaveChanges();
        }

        using (var context = new BloggingContext())
        {
            var results = context.Set<Post>().Select(post => new PostProject
            {
                PostDisableCoins = post.PostDisableCoins.ToList(),
                WarStartTime = post.WarStartTime,
                WarEndTime = post.WarEndTime,
                WarWinner = post.WarWinner,
                WarDeclarer = post.WarDeclarer
            }).ToList();
        }
    }
}

[damirci]

I think I forgot Take() function, BTW here is sample code on existed data.

    class Program
    {
        static void Main(string[] args)
        {
            using (var context = new NplusOneContext())
            {
                var results = context.Set<Post>().Select(post => new PostProject
                {
                    PostDisableCoins = post.PostDisableCoins.Take(1).ToList(),
                    WarStartTime = post.WarStartTime,
                    WarEndTime = post.WarEndTime,
                    WarWinnerId = post.WarWinnerId,
                    WarDeclarerId = post.WarDeclarerId
                }).ToList();

                
            }
        }
    }

    public class NplusOneContext : DbContext
    {
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
            => optionsBuilder
                .UseSqlServer(@"Data Source=.;Initial Catalog=Kalgram;Persist Security Info=True;User ID=kalgram;PassWord=kalgram");

        public DbSet<Post> Posts { get; set; }
        public DbSet<PostDisableCoin> PostDisableCoins { get; set; }
    }

    public class Post
    {
        [Key]
        public Guid PostId { get; set; }
        public ICollection<PostDisableCoin> PostDisableCoins { get; set; }
        public DateTime? WarStartTime { get; set; }
        public DateTime? WarEndTime { get; set; }

        public string WarDeclarerId { get; set; }

        public string WarWinnerId { get; set; }
    }

    public class PostProject
    {
        public ICollection<PostDisableCoin> PostDisableCoins { get; set; }
        public DateTime? WarStartTime { get; set; }
        public DateTime? WarEndTime { get; set; }

        public string WarDeclarerId { get; set; }

        public string WarWinnerId { get; set; }
    }

    public class PostDisableCoin
    {
        [Key]
        public int PostDisableCoinId { get; set; }
        public Guid PostId { get; set; }
        public virtual Post Post { get; set; }

        public int CoinAmount { get; set; }
    }

[maumar]

we use ROW_NUMBER function in 5.0 to translate this into a single query.