Query: Include() for related collections are dropped when use Skip()

[m-anthoine]

Visual Studio 201 update 3
Asp.net Core website project
ef7 : V1.0.0
postgresql database : Npgsql.EntityFrameworkCore.PostgreSQL v1.0.0

I have a context with some entities :

    public class CatalogueContext : IdentityDbContext<ApplicationUser>
    {
        public CatalogueContext(DbContextOptions<CatalogueContext> options)
            : base(options)
        {
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);

            modelBuilder.Entity<ProduitCategorie>()
                .HasKey(t => new { t.Produit_ProduitId, t.Categorie_CategorieId });

            modelBuilder.Entity<ProduitCategorie>()
                .HasOne(pt => pt.Produit)
                .WithMany(p => p.ProduitCategories)
                .HasForeignKey(pt => pt.Produit_ProduitId);

            modelBuilder.Entity<ProduitCategorie>()
                .HasOne(pt => pt.Categorie)
                .WithMany(t => t.ProduitCategories)
                .HasForeignKey(pt => pt.Categorie_CategorieId);
        }

        public DbSet<Famille> Familles { get; set; }
        public DbSet<Categorie> Categories { get; set; }
        public DbSet<Produit> Produits { get; set; }
    }

    public class Famille 
    {
        public int FamilleId { get; set; }
        [Required]
        [MaxLength(250)]
        public string Titre { get; set; }
        public virtual ICollection<Categorie> Categories { get; set; }

        public int OrdreAffichage { get; set; }

        public Famille()
        {
            Categories = new List<Categorie>();
        }
    }

    public class Categorie
    {
        public int CategorieId { get; set; }
        [Required]
        [MaxLength(250)]
        public string Titre { get; set; }
        public virtual ICollection<ProduitCategorie> ProduitCategories { get; set; }

        [ForeignKey("Famille_FamilleId")]
        public virtual Famille Famille { get; set; }
        [Required]
        public int Famille_FamilleId { get; set; }

        public int OrdreAffichage { get; set; }

        public Categorie()
        {
            ProduitCategories = new List<ProduitCategorie>();
        }
    }

    public class Produit
    {
        public int ProduitId { get; set; }
        [Required]
        [MaxLength(250)]
        public string Titre { get; set; }

        public string Description { get; set; }

        public virtual ICollection<ProduitCategorie> ProduitCategories { get; set; }

        public Produit()
        {
            ProduitCategories = new List<ProduitCategorie>();
        }
    }

    public class ProduitCategorie
    {

        [Required]
        public int Produit_ProduitId { get; set; }
        [ForeignKey("Produit_ProduitId")]
        public Produit Produit { get; set; }


        [ForeignKey("Categorie_CategorieId")]
        public Categorie Categorie { get; set; }
        [Required]
        public int Categorie_CategorieId { get; set; }

        public ProduitCategorie()
        {

        }
    }

When i try to get paged data of "Produit" entities, I get some troubles to include its related collection of "ProduitCategorie"s

    public class ProduitService : GenericRepository<Produit>, IProduitService
    {
        public ProduitService(CatalogueContext ctx) :
            base(ctx)
        {
        }

        public void Dispose()
        {
        }

        public List<Produit> GetPagedData(int skip, int take)
        {
            CatalogueContext context = this.context; //injected by service layer constructor
            List<Produit> data = null;

            data = context.Produits.Skip(skip).Take(take).Include(c => c.ProduitCategories).ThenInclude(c => c.Categorie).ThenInclude(c => c.Famille).ToList();

            return data;
        }
    }

When I call GetPagedData(0, 10); the 10th first element are returned with their ProduitCategories collection populated.

When I call it with a skip value other than 0, the collection is always returned with 0 elements.

Maybe it's relevant or not but even skip 0 or skip 10 produced the same output in debug window :

skip(0) :

Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommandBuilderFactory:Information: Executed DbCommand (0ms) [Parameters=[@__p_1='?', @__p_0='?'], CommandType='Text', CommandTimeout='30']
SELECT "p"."ProduitId", "p"."Description", "p"."ImagePath", "p"."Reference", "p"."Titre"
FROM "Produits" AS "p"
ORDER BY "p"."ProduitId"
LIMIT @__p_1 OFFSET @__p_0
Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommandBuilderFactory:Information: Executed DbCommand (0ms) [Parameters=[@__p_1='?', @__p_0='?'], CommandType='Text', CommandTimeout='30']
SELECT "p0"."Produit_ProduitId", "p0"."Categorie_CategorieId", "c"."CategorieId", "c"."Famille_FamilleId", "c"."OrdreAffichage", "c"."Titre", "f"."FamilleId", "f"."OrdreAffichage", "f"."Titre"
FROM "ProduitCategorie" AS "p0"
INNER JOIN "Categories" AS "c" ON "p0"."Categorie_CategorieId" = "c"."CategorieId"
INNER JOIN "Familles" AS "f" ON "c"."Famille_FamilleId" = "f"."FamilleId"
WHERE EXISTS (
    SELECT 1
    FROM "Produits" AS "p"
    WHERE "p0"."Produit_ProduitId" = "p"."ProduitId"
    LIMIT @__p_1 OFFSET @__p_0)
ORDER BY "p0"."Produit_ProduitId"

Skip(10) :

Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommandBuilderFactory:Information: Executed DbCommand (0ms) [Parameters=[@__p_1='?', @__p_0='?'], CommandType='Text', CommandTimeout='30']
SELECT "p"."ProduitId", "p"."Description", "p"."ImagePath", "p"."Reference", "p"."Titre"
FROM "Produits" AS "p"
ORDER BY "p"."ProduitId"
LIMIT @__p_1 OFFSET @__p_0
Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommandBuilderFactory:Information: Executed DbCommand (0ms) [Parameters=[@__p_1='?', @__p_0='?'], CommandType='Text', CommandTimeout='30']
SELECT "p0"."Produit_ProduitId", "p0"."Categorie_CategorieId", "c"."CategorieId", "c"."Famille_FamilleId", "c"."OrdreAffichage", "c"."Titre", "f"."FamilleId", "f"."OrdreAffichage", "f"."Titre"
FROM "ProduitCategorie" AS "p0"
INNER JOIN "Categories" AS "c" ON "p0"."Categorie_CategorieId" = "c"."CategorieId"
INNER JOIN "Familles" AS "f" ON "c"."Famille_FamilleId" = "f"."FamilleId"
WHERE EXISTS (
    SELECT 1
    FROM "Produits" AS "p"
    WHERE "p0"."Produit_ProduitId" = "p"."ProduitId"
    LIMIT @__p_1 OFFSET @__p_0)
ORDER BY "p0"."Produit_ProduitId"

To my tests, there are no problems when I try to include/thenInclude related entities that are not collections.

[bottomup]

I have the same issue with EF Core. Includes are gone when Skip with value > 0 is used. The generated SQL seems to be ok though just as @m-anthoine mentions. Thanks for adding this to the 1.1 release, it is really a breaking issue at the moment.

[smitpatel]

Possible dupe of #5901
It has been fixed in 1.0.1 release. Can you try using 1.0.1 if it still reproduces?

[bottomup]

@smitpatel I'm sorry to say, in the 1.0.1 version it still reproduces when Skip is greater than 0. I'm happy to share more in depth details if needed.

[smitpatel]

Can you show the query generated in 1.0.1?

[bottomup]

Main product query:

SELECT [p].[Id], [p].[CreatedOnUtc], [p].[Description], [p].[Donate], [p].[Price], [p].[SellStatus], [p].[Status], [p].[Title]
FROM [Product] AS [p]
WHERE [p].[Status] = 1
ORDER BY [p].[Id] DESC
OFFSET @__p_0 ROWS FETCH NEXT @__p_1 ROWS ONLY

This is one of the include/theninclude queries (which seems ok):

SELECT [p3].[ProductId], [p3].[CategoryId], [c].[Id], [c].[Name]
FROM [ProductCategory] AS [p3]
INNER JOIN [Category] AS [c] ON [p3].[CategoryId] = [c].[Id]
WHERE EXISTS (
SELECT 1
FROM [Product] AS [p]
WHERE ([p].[Status] = 1) AND ([p3].[ProductId] = [p].[Id])
ORDER BY @@rowcount
OFFSET @__p_0 ROWS FETCH NEXT @__p_1 ROWS ONLY)
ORDER BY [p3].[ProductId] DESC

[smitpatel]

@bottomup - the WHERE EXISTS is incorrect. It should be converted to INNER JOIN. I see your query has predicate, therefore it is still generating WHERE EXISTS.
It is fixed in #6607
#6607 (comment) clarifies the source of the error.

[bottomup]

@smitpatel thanks. Does this mean the next release (1.0,2) fixes the issue?

[smitpatel]

@bottomup - Yes.

[smitpatel]

Fixed in #6607

[divega]

Reopening to go through proper approval process.

[Eilon]

This patch is approved, please ensure it is merged into the correct branch and building as part of the patch train.

[bottomup]

This fix will be part of 1.0.2. Now 1.1.0 was just released, is this fix also included there? I can't find it in the release notes.

[divega]

@bottomup AFAIK all fixes marked as approved for 1.0.2 across all aspnet repos so far have been included in 1.1 RTM as well. In some cases separate issues may exist for each release but I don't think that is generally the case. And I am not sure we took that into account when preparing the release notes for 1.1.

cc @danroth27 @Eilon @rowanmiller @muratg

[bottomup]

@divega I have just tested the issue with the 1.1.0 release, and it seems to be fixed :)

[Eilon]

In general it's fair to assume that every RTM (i.e. non-pre-rel) version was released with all the fixes from all lower versions.

[Eilon]

The main case where we have duplicated bugs for patch + release is when a future release already has a fix, but we later chose to backport a fix to a patch, so we needed to open a new bug for the patch release.

[divega]

In it's fair to assume that every RTM...

@Eilon not sure what you meant but what I meant is that it happens to be the case for all the fixes we have taken so far in 1.0.2.

Worth updating the release notes?

[Eilon]

Oops, I somehow typo'ed out an entire word. I edited it to say In *general* it's fair ....

What release notes are you referring to?

[smitpatel]

In general it's fair to assume that every RTM (i.e. non-pre-rel) version was released with all the fixes from all lower versions.

Does that mean if we fix something in 1.0.3 then we would backport it to 1.1.1 release?

[Eilon]

@smitpatel well, that wouldn't be back-porting, it's front-porting 😄

But actually that's an interesting point, and I hadn't considered that scenario. I think certainly in some cases the answer is yes, we would port such fixes (assuming they actually apply - not all fixes would actually make sense to port), but ultimately it's case-by-case.

[divega]

@Eilon I was referring to https://github.com/aspnet/EntityFramework/releases/tag/rel/1.1.0 and https://github.com/aspnet/home/releases/1.1.0 but not sure if that is what @bottomup was referring to.

[Eilon]

@divega O I C. I'm less concerned about the release notes :)

[bottomup]

@divega yes I was refering to the release notes you link to..