Proper Pagination with Joins

[dblex]

Hi,
I'm currently trying to do pagination with Parent/Child Entities in FastCrud.

I the way I thought it should work doesn't in 3.0. Am I missing something?

When i use @KKica from #149 example adjusted to 3.0:

var records = _db.FindAsync<User>(x => x
    .WithAlias("u")
    .Include<Roles>(o => o.LeftOuterJoin().WithAlias("r"))
    .Where("query with params")
    .WithParameters(myParamObject)
    .OrderBy("sortByColumn")
    .Skip(0)
    .Top(10)
);

It's still skipping and taking the joined rows, which is usually the intended behaviour for Joins.
However, I want to skip and take only by the parent entity (User), while still applying the Join.

Using Count with a Join in 3.0 does work and returns only the Count of the Parent Entities due to the DISTINCT Clause.

Is there a way to do this? I tried changing the Order of the .Skip, .Top. OrderBy Calls, but still nothing.
Or do I have to do something like #142 ?

---

EDIT:

My (temporary?) solution would be to just use a second query for preselection.

var pagedEntries = await db.FindAsync<User>(
    s => s
    .Where("query with params")
    .Skip(0)
    .Top(10)
    .OrderBy("sortByColumn")
    .WithParameters(
        preSelectionParams
    )
);

var pagedIds = pagedEntries.Select(x => x.Id);

var records = _db.FindAsync<User>(x => x
    .WithAlias("u")
    .Include<Roles>(o => o.LeftOuterJoin().WithAlias("r"))
    .Where($@"{nameof(User.Id):of u} In {nameof(secondParamObject.Ids):P}")
);

[MoonStorm]

Yeah, you have to be careful when you're paginating with Parent/Child relationships. Parents get duplicated when coming as rows in the result set from the database with the children. FastCrud will remove the duplicates, but by that time the SKIP and TOP clauses where already executed by the DB.

Your temporary solution works but could be improved. In situations like these you need to filter and perform the pagination in a subquery:

var records = _db.FindAsync<User>(x => x
    .Include<Roles>(o => o.LeftOuterJoin())
    .Where($@"{nameof(User.Id):TC} IN 
	   (
			SELECT {nameof(User.UserId):C}
			FROM {nameof(User):T}
			WHERE {nameof(User.FirstName):C} = {nameof(myParamObject.FirstName):P}
			ORDER BY {nameof(User.LastName):C}
			-- the paging comes here
			OFFSET 10 ROWS          -- skip 10 rows
			FETCH NEXT 10 ROWS ONLY -- take 10 rows
		)
	")
    .WithParameters(myParamObject)
    // repeat the OrderBy on the main entity here
    .OrderBy($"nameof(User.LastName):TC")
);

I'll add this to the wiki since this is a really common scenario.