Wrong Query Generation for Skip() and Take

[roji]

From @chbach on November 18, 2015 20:36

Hi, I recently switched from SQLite to Postgres on EF7 and noticed the following bug. When using Skip() and Take() (LIMIT and OFFSET), some relational data would be missing in the resulting collection of objects.

I have two models, NewsEntry and Tag that are related in a many-to-many relation via the table Tagging. When I query all NewsEntries with Db.NewsEntries.Include(e => e.Taggings), everything works fine and the Tagging-relation is present and I can iterate over them. However, as soon as I start doing something like Db.NewsEntries.Include(e => e.Taggings).OrderByDescending(e => e.Date).Take(12) some of the Tagging-relations are missing.

I analyzed the generated queries and noticed the following thing. This is the generated query for the relation:

SELECT "t"."NewsEntryId", "t"."TagId"
FROM "Tagging" AS "t"
INNER JOIN (
    SELECT DISTINCT "e"."Date", "e"."Id"
    FROM "NewsEntry" AS "e"
    LIMIT 12
) AS "e" ON "t"."NewsEntryId" = "e"."Id"
ORDER BY "e"."Date" DESC, "e"."Id"

What seems odd to me is that the ORDER BY statement is at the very end and sorts the overall result, but should rather sort the NewsEntries, since we want to get the first 12 elements after sorting:

SELECT "t"."NewsEntryId", "t"."TagId"
FROM "Tagging" AS "t"
INNER JOIN (
    SELECT DISTINCT "e"."Date", "e"."Id"
    FROM "NewsEntry" AS "e"
    ORDER BY "e"."Date" DESC, "e"."Id"
    LIMIT 12
) AS "e" ON "t"."NewsEntryId" = "e"."Id"

You can compare the query with the query the SQLite adapter produces here: dotnet/efcore#3758

Can you please have a look into that?

Christoph

EDIT:

Moving the sorting to the front like Db.NewsEntries.OrderByDescending(e => e.Date).Include(e => e.Taggings) actually generates the right query. However there are still Taggings missing! I have no idea how that happens.

Copied from original issue: npgsql/npgsql#872

[roji]

From @SimonTouchtech on November 20, 2015 16:6

Seems to be the same as dotnet/efcore#3732?

[roji]

@chbach, as @SimonTouchtech suggests this seems to be the same as dotnet/efcore#3732, which is a core EF7 and not an Npgsql problem. Can you please confirm and close this issue if so?

[roji]

From @chbach on December 6, 2015 19:26

In fact, I didn't observe this behaviour with SQLite, it only appeared after I switched from SQLite to PostgreSQL. Apart from the wrong query generation, there are still relations missing in the final collection, so something with Include() in combination with Skip() and Take() and the population of relations must be wrong.

[KANekT]

Hi, i have this error in 1.0.1.
Then i use include - Order by not working

If i have select before order - it working

       var entities = this.dbSet.Include(x => x.User).Select(x => new GameRewardLogsGridBackendModel
        {
            Id = x.Id,
            DateReward = x.DateReward,
            Place = x.Place,
            Reward = x.Reward,
            RewardId = x.RewardId,
            GroupingId = x.GroupingId,
            Description = x.Description,
            UserId = x.UserId,
            UserName = x.User.Name,
            DispositionId = x.DispositionId
        }).OrderByDescending(u => u.DateReward)
            .Skip(skip)
            .Take(take);

and not working (ordering by Include(x => x.User))

       var entities = this.dbSet.Include(x => x.User).OrderByDescending(u => u.DateReward)
            .Skip(skip)
            .Take(take).Select(x => new GameRewardLogsGridBackendModel
        {
            Id = x.Id,
            DateReward = x.DateReward,
            Place = x.Place,
            Reward = x.Reward,
            RewardId = x.RewardId,
            GroupingId = x.GroupingId,
            Description = x.Description,
            UserId = x.UserId,
            UserName = x.User.Name,
            DispositionId = x.DispositionId
        });

and not working to

       var entities = this.dbSet.OrderByDescending(u => u.DateReward).Include(x => x.User)
            .Skip(skip)
            .Take(take).Select(x => new GameRewardLogsGridBackendModel
        {
            Id = x.Id,
            DateReward = x.DateReward,
            Place = x.Place,
            Reward = x.Reward,
            RewardId = x.RewardId,
            GroupingId = x.GroupingId,
            Description = x.Description,
            UserId = x.UserId,
            UserName = x.User.Name,
            DispositionId = x.DispositionId
        });

[m-anthoine]

Hi,
I have the same problem. I reported it on EF github but I wonder if it's not related the provider.
dotnet/efcore#6492

this code :

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

seems to produced :

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 10 OFFSET 10)
ORDER BY "p0"."Produit_ProduitId"

Now with a rapid analyze of this sql code, I understand why it doesn't work...
The SELECT in the EXISTS clause only return 1 row... when an OFFSET occured (more than 0) obviously the result is false.

So, I'm not a expert on SQL performance, but a query like this do the job :

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 (SELECT "p1"."ProduitId" FROM "Produits" as "p1" LIMIT 10 OFFSET 10) AS "p"
    WHERE "p0"."Produit_ProduitId" = "p"."ProduitId"
    )
ORDER BY "p0"."Produit_ProduitId"

Is this problem is related to EF core or to the PGSQL provider ?

[roji]

I don't have time to dive into this right now, but there's little chance this is an Npgsql provider issue - it's most likely an EFCore issue. One easy way to help out would be to simply test this on SqlClient with LocalDB - it's really trivial to get a small model together and run this query (see the EFCore getting started). This will at least tell us exactly where the problem is.

[roji]

Closing for each, likely to have been fixed in EF Core a long time ago.