Hi,

When I use FetchEntityCollectionAsync it doesn't use server paging. On the first page there is a LIMIT, but on next page both LIMIT and OFFSET are missing.

I have table A with foreign key BId. I'm sorting by column from B table.

I'm not sure what are the rules for switching between server and in-memory paging. It works for some other tables. Difference are: table A has more columns and it has additional key BId2 to table B.

There is only one other thing I can think of that might be causing this issue, but I'm not sure why it's working on other tables (same case join and order by foreign column). Paging is working when I'm sorting by columns from table A but when I'm sorting by column from table B it needs to remove DISTINCT, is that causing problems for paging?

Is there a way to remove distinct from FetchEntityCollectionAsync when I'm sure there won't be duplicates? I noticed that PostgreSQL is much faster without DISTINCT.

LLBLGen 4.2 PostgreSQL npgsql 3.2.5

Thank you for your help, David.

The query isn't construct-able on the server as a paging query. The reason is that you sort on a field which isn't in the projection and because it needs to include a DISTINCT directive in the query (due to the join), it can't page on the server as sorting on a field not in the projection can't be done with DISTINCT being present (it will give an error in the DB).

If you don't want in-memory paging, you may use Linq or QuerySpec to fetch the set of fields you want including the sort field, and then project the result to the entityCollection.

Walaa wrote:

The query isn't construct-able on the server as a paging query. The reason is that you sort on a field which isn't in the projection and because it needs to include a DISTINCT directive in the query (due to the join), it can't page on the server as sorting on a field not in the projection can't be done with DISTINCT being present (it will give an error in the DB).

Yes, but why does it work on some other tables, same case. Table C with foreign key DId and Table D. Fetching collection from table C, joining to D and ordering by column from table D. There is no DISTINCT, but LIMIT and OFFSET are there.

fetching table C joining D where C is the FK side gives m:1 joins and it likely will then decide there are no duplicates.

Fetching table D joining C where C is the FK side could give duplicates and therefore it will decide to insert a distinct.

The checks are fairly simple though. It checks for ManyToMany and OneToMany. So if you specify the relationship from teh other side (ManyToOne) it will probably miss the duplicate check. So please post code snippets and SQL queries of what you wrote as code and what was generated as SQL to compare. (with Fk/pk side specifications of the entities).

So if a field in the sortclause isn't found in the projection, it won't emit DISTINCT (as that will always cause an error). If the relationships specified (the joins) then are seen as 'potentially duplicate creating', it will switch to client side to be safe.

using (IDataAccessAdapter dataAdapter = DataAccessAdapterFactory.Create())
{
    RelationPredicateBucket bucket = new RelationPredicateBucket();
    bucket.Relations.Add(PracticeEntity.Relations.AddressEntityUsingAddressId, JoinHint.Left);

    var queryParams = new QueryParameters(2, 10, 0);
    queryParams.CollectionToFetch = new EntityCollection<PracticeEntity>();
    queryParams.RelationsToUse = bucket.Relations;
    queryParams.SorterToUse = new SortExpression(new SortClause(AddressFields.City, null, SortOperator.Ascending));
    await dataAdapter.FetchEntityCollectionAsync(queryParams, new System.Threading.CancellationToken());
}

Executed Sql Query: 
Query: SELECT "public"."Practice"."Id", ... FROM ( "public"."Address"  RIGHT JOIN "public"."Practice"  ON  "public"."Address"."Id"="public"."Practice"."AddressId") ORDER BY "public"."Address"."City" ASC

I also tried one to one relationship and inner join, it doesn't work.

It works with page size 1.

Please post the code snippets which work and which don't work, you only posted 1. Also post which entities are the PK side so we know if the relationship you added is m:1 or 1:n, as I said the check is for 1:n.

You can see in generated sql that AddressId is on Practice entity.

Here is summary from relation:

Returns a new IEntityRelation object, between PracticeEntity and AddressEntity over the m:1 relation they have, using the relation between the fields: Practice.AddressId - Address.Id

I also tried one-on-one.

I can't find example that works, I probably had small set so I put page size to 1, in that case it works.

We also tried LLBLGen 4.1 with SQL server, there for same case server side paging works.

The behavior here is that it requires unique rows (an entity collection fetch), and due to the sorting on a field not in the projection, it can't emit distinct. This alone makes it switch to client-side paging (this isn't as lame as it sounds btw, it fetches rows per page from the server, not the whole set and then pages through it).

The reason for this is that there are situations where the original behavior, with 'are there unique rows?' and distinct checks sometimes flagged sets as 'OK' while there weren't unique rows causing pages to have less rows than you'd expect. So, to stay on the safe side we concluded it's better to switch to client side paging in those areas so the right rows were returned than return the wrong data.

You can set the engine to use the original behavior in all cases, which isn't recommended, by setting DynamicQueryEngineBase.DistinctFilteringPreferenceDefault to DistinctFilteringPreferenceType.Legacy.

You can also set this per-call. To do that you have to create a derived class from the DataAccessAdapter class like I have done below:

public class LegacyAdapter : DataAccessAdapter
{
    protected override DynamicQueryEngineBase CreateDynamicQueryEngine()
    {
        var toReturn = base.CreateDynamicQueryEngine();
        ((DynamicQueryEngine)toReturn).DistinctFilteringPreferenceToUse = DistinctFilteringPreferenceType.Legacy;
        return toReturn;
    }
}

If you use the above adapter class instead of the normal DataAccessAdapter class, you'll get the legacy behavior here and it will issue a server-side paging query.

Hope this helps.

ps: I know for this particular situation it could conclude the right thing. We are aware of that and will try to make it do a better job in a future version.