ThenInclude after Take on a ICollection Property generate a wrong sql

[GLuca74]

Hello all.

EF 5.0.0-rc.2.20475.6

using this semplificate :

    public class Zoo 
    {
        public Guid ID { get; set; }
        public virtual ICollection<Animal> Animals { get; set; }
    }

    public class Nation
    {
        public Guid ID { get; set; }
        public string Name { get; set; }
    }
    public  class Animal
    {
        public Guid ID { get; set; }
        public int Age { get; set; }
        public virtual Nation BornNation { get; set; }
    }

    public class MyDBContext : DbContext
    {
        public MyDBContext()
        {
            this.Database.EnsureCreated();
        }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            base.OnConfiguring(optionsBuilder);
            optionsBuilder.UseSqlServer(@"Server=CRM3-NB01\SQLEXPRESS;Database=TestEF;Trusted_Connection=True;");
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);
            modelBuilder.Entity<Animal>().HasKey(itm => itm.ID);
            modelBuilder.Entity<Animal>().HasOne(itm => itm.BornNation).WithMany().IsRequired(false);
            modelBuilder.Entity<Zoo>().HasKey(itm => itm.ID);
            modelBuilder.Entity<Zoo>().HasMany(itm => itm.Animals).WithOne().IsRequired(false);
            modelBuilder.Entity<Nation>().HasKey(itm => itm.ID);
        }
    }

using this query :

using (var DB = new MyDBContext())
            {
                var q = (from itm in DB.Set<Zoo>().Include(itm => itm.Animals.Take(10)).ThenInclude(itm => itm.BornNation) select itm ).FirstOrDefault();
            }

throw the exception :

Microsoft.Data.SqlClient.SqlException: 'Column name 'BornNationID' is not valid.
Column name  'Age' is not valid.
Column name  'BornNationID' is not valid.
Column name  'ZooID' is not valid'

No exception if I remove the Take(10) or if i use ToArray instead of FirstOrDefault

attached the project that reproduce the issue

EF.zip

[GLuca74]

Just to add a detail might be useful, I wrote that if I use ToArray no exception is raised, so:
var q = (from itm in DB.Set<Zoo>().Include(itm => itm.Animals.Take(10)).ThenInclude(itm => itm.BornNation) select itm ).ToArray();

Works.

But actually if I add a Skip at the end, ToArray doesn't work either :

var q = (from itm in DB.Set<Zoo>().Include(itm => itm.Animals.Take(10)).ThenInclude(itm => itm.BornNation) select itm).Skip(3).ToArray();

Same exception is raised

Regards

[davidkarlsson]

I get the same error with Take in the filtered include when it's followed by a ThenInclude and the query is also filtered with Take. For example this query throws a SqlException for me:

var conversations = await dbContext.Conversations
	.Include(a => a.ConversationMessages.Take(1))
	.ThenInclude(a => a.SentByUser)
	.Take(1)
	.ToListAsync();

The generated query looks like this if it's to any help:

SELECT [t].[id], [t].[subject], [t].[updated], [t1].[id], [t1].[conversation_id], [t1].[sent], [t1].[sent_by_user_id], [t1].[text], [t1].[id0], [t1].[absent], [t1].[access_failed_count], [t1].[active], [t1].[concurrency_stamp], [t1].[connected], [t1].[created], [t1].[email], [t1].[email_confirmed], [t1].[first_name], [t1].[image], [t1].[is_ad_user], [t1].[last_name], [t1].[lockout_enabled], [t1].[lockout_end], [t1].[normalized_email], [t1].[normalized_user_name], [t1].[password_hash], [t1].[phone_number], [t1].[phone_number_confirmed], [t1].[pin_password_hash], [t1].[profession_id], [t1].[security_stamp], [t1].[two_factor_enabled], [t1].[updated], [t1].[user_name], [t1].[windows_user_name]
FROM (
    SELECT TOP(1) [c].[id], [c].[subject], [c].[updated]
    FROM [conversations] AS [c]
) AS [t]
OUTER APPLY (
    SELECT [t].[id], [t].[conversation_id], [t].[sent], [t].[sent_by_user_id], [t].[text], [u].[id] AS [id0], [u].[absent], [u].[access_failed_count], [u].[active], [u].[concurrency_stamp], [u].[connected], [u].[created], [u].[email], [u].[email_confirmed], [u].[first_name], [u].[image], [u].[is_ad_user], [u].[last_name], [u].[lockout_enabled], [u].[lockout_end], [u].[normalized_email], [u].[normalized_user_name], [u].[password_hash], [u].[phone_number], [u].[phone_number_confirmed], [u].[pin_password_hash], [u].[profession_id], [u].[security_stamp], [u].[two_factor_enabled], [u].[updated], [u].[user_name], [u].[windows_user_name]
    FROM (
        SELECT TOP(1) [c0].[id], [c0].[conversation_id], [c0].[sent], [c0].[sent_by_user_id], [c0].[text]
        FROM [conversation_messages] AS [c0]
        WHERE [t].[id] = [c0].[conversation_id]
    ) AS [t0]
    INNER JOIN [users] AS [u] ON [t].[sent_by_user_id] = [u].[id]
) AS [t1]
ORDER BY [t].[id], [t1].[id], [t1].[id0]
GO

If I remove the ThenInclude the query works correctly but the navigation property is not loaded obviously.

[BerkanYildiz]

Am I doing it wrong or is this still a thing ? Or is the fix only implemented to the .NET 6 version of the library.. -_-

mstsc_esVNU3vQTJ.mp4