Wrong Query Generation for Skip() and Take about efcore.pg HOT 7 CLOSED

From @SimonTouchtech on November 20, 2015 16:6

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

from efcore.pg.

@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?

from efcore.pg.

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.

from efcore.pg.

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
        });

from efcore.pg.

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 ?

from efcore.pg.

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.

from efcore.pg.

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

from efcore.pg.