Hi,

I'm upgrading my application to SQL 2008 and LLBLGen Pro 3.1 and I have encountered an issue with paging as it won't add TOP and the rest of the paging functionality to my query (V3.1.11.221). Here are the profiler results:

[b]2.6.8.624[/b]


                EntityCollection<OcevidnikEntity> oc = new EntityCollection<OcevidnikEntity>();
                RelationPredicateBucket bucketOC = new RelationPredicateBucket();
                bucketOC.Relations.Add(OcevidnikEntity.Relations.ObjektEntityUsingObjektId);
                bucketOC.PredicateExpression.AddWithAnd(new FieldCompareValuePredicate(OcevidnikFields.DatumNadzora, null, ComparisonOperator.GreaterEqual, startDate.Value));
                bucketOC.PredicateExpression.AddWithAnd(new FieldCompareValuePredicate(OcevidnikFields.DatumNadzora, null, ComparisonOperator.LessEqual, endDate.Value));

                adapter.FetchEntityCollection(oc, bucketOC, 0, new SortExpression(new SortClause(ObjektFields.Naziv, null, SortOperator.Ascending)), null, null, pageNumber, pageSize);
                

                [b]SQL 2005[/b]
                exec sp_executesql N'WITH __actualSet AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) AS __rowcnt FROM (SELECT TOP 40 [DirhSmartClient].[dbo].[Ocevidnik].[Id], [DirhSmartClient].[dbo].[Ocevidnik].[Godina], [DirhSmartClient].[dbo].[Ocevidnik].[UserId], [DirhSmartClient].[dbo].[Ocevidnik].[RedniBroj], [DirhSmartClient].[dbo].[Ocevidnik].[ObjektId], [DirhSmartClient].[dbo].[Ocevidnik].[FizickaOsobaId], [DirhSmartClient].[dbo].[Ocevidnik].[AdresaNadzora], [DirhSmartClient].[dbo].[Ocevidnik].[VrstaObjektaId], [DirhSmartClient].[dbo].[Ocevidnik].[PodvrstaObjektaId], [DirhSmartClient].[dbo].[Ocevidnik].[DjelatnostId], [DirhSmartClient].[dbo].[Ocevidnik].[Klasa], [DirhSmartClient].[dbo].[Ocevidnik].[URBROJ] AS [Urbroj], [DirhSmartClient].[dbo].[Ocevidnik].[DatumNadzora], [DirhSmartClient].[dbo].[Ocevidnik].[DatumIzvjestaja], [DirhSmartClient].[dbo].[Ocevidnik].[PravoNaIzmjenuDatumaIzvjestaja], [DirhSmartClient].[dbo].[Ocevidnik].[VrstaNadzoraAbrv], [DirhSmartClient].[dbo].[Ocevidnik].[Primjedbe], [DirhSmartClient].[dbo].[Ocevidnik].[InspekcijaId], [DirhSmartClient].[dbo].[Ocevidnik].[LokacijaId], [DirhSmartClient].[dbo].[Ocevidnik].[ZabranaPoduzimanjaOdređenePoslovneAktivnosti], [DirhSmartClient].[dbo].[Ocevidnik].[BrojDanauNadzoru], [DirhSmartClient].[dbo].[Ocevidnik].[VrstaSuradnjeAbrv], [DirhSmartClient].[dbo].[Ocevidnik].[UgovoroSuradnjiId] FROM ( [DirhSmartClient].[dbo].[Objekt]  INNER JOIN [DirhSmartClient].[dbo].[Ocevidnik]  ON  [DirhSmartClient].[dbo].[Objekt].[Id]=[DirhSmartClient].[dbo].[Ocevidnik].[ObjektId]) WHERE ( ( [DirhSmartClient].[dbo].[Ocevidnik].[DatumNadzora] >= @DatumNadzora1 AND [DirhSmartClient].[dbo].[Ocevidnik].[DatumNadzora] <= @DatumNadzora2)) ORDER BY [DirhSmartClient].[dbo].[Objekt].[Naziv] ASC) AS _tmpSet) SELECT [Id], [Godina], [UserId], [RedniBroj], [ObjektId], [FizickaOsobaId], [AdresaNadzora], [VrstaObjektaId], [PodvrstaObjektaId], [DjelatnostId], [Klasa], [Urbroj], [DatumNadzora], [DatumIzvjestaja], [PravoNaIzmjenuDatumaIzvjestaja], [VrstaNadzoraAbrv], [Primjedbe], [InspekcijaId], [LokacijaId], [ZabranaPoduzimanjaOdređenePoslovneAktivnosti], [BrojDanauNadzoru], [VrstaSuradnjeAbrv], [UgovoroSuradnjiId] FROM __actualSet WHERE [__rowcnt] > @__rownoStart AND [__rowcnt] <= @__rownoEnd ORDER BY [__rowcnt] ASC',N'@DatumNadzora1 datetime,@DatumNadzora2 datetime,@__rownoStart int,@__rownoEnd int',@DatumNadzora1='2011-01-01 00:00:00',@DatumNadzora2='2011-12-31 23:59:59',@__rownoStart=35,@__rownoEnd=40


                [b]SQL 2008[/b]
                exec sp_executesql N'WITH __actualSet AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) AS __rowcnt FROM (SELECT TOP 40 [dbo].[Ocevidnik].[Id], [dbo].[Ocevidnik].[Godina], [dbo].[Ocevidnik].[UserId], [dbo].[Ocevidnik].[RedniBroj], [dbo].[Ocevidnik].[ObjektId], [dbo].[Ocevidnik].[FizickaOsobaId], [dbo].[Ocevidnik].[AdresaNadzora], [dbo].[Ocevidnik].[VrstaObjektaId], [dbo].[Ocevidnik].[PodvrstaObjektaId], [dbo].[Ocevidnik].[DjelatnostId], [dbo].[Ocevidnik].[Klasa], [dbo].[Ocevidnik].[URBROJ] AS [Urbroj], [dbo].[Ocevidnik].[DatumNadzora], [dbo].[Ocevidnik].[DatumIzvjestaja], [dbo].[Ocevidnik].[PravoNaIzmjenuDatumaIzvjestaja], [dbo].[Ocevidnik].[VrstaNadzoraAbrv], [dbo].[Ocevidnik].[Primjedbe], [dbo].[Ocevidnik].[InspekcijaId], [dbo].[Ocevidnik].[LokacijaId], [dbo].[Ocevidnik].[ZabranaPoduzimanjaOdređenePoslovneAktivnosti], [dbo].[Ocevidnik].[BrojDanauNadzoru], [dbo].[Ocevidnik].[VrstaSuradnjeAbrv], [dbo].[Ocevidnik].[UgovoroSuradnjiId] FROM ( [dbo].[Objekt]  INNER JOIN [dbo].[Ocevidnik]  ON  [dbo].[Objekt].[Id]=[dbo].[Ocevidnik].[ObjektId]) WHERE ( ( [dbo].[Ocevidnik].[DatumNadzora] >= @DatumNadzora1 AND [dbo].[Ocevidnik].[DatumNadzora] <= @DatumNadzora2)) ORDER BY [dbo].[Objekt].[Naziv] ASC) AS _tmpSet) SELECT [Id], [Godina], [UserId], [RedniBroj], [ObjektId], [FizickaOsobaId], [AdresaNadzora], [VrstaObjektaId], [PodvrstaObjektaId], [DjelatnostId], [Klasa], [Urbroj], [DatumNadzora], [DatumIzvjestaja], [PravoNaIzmjenuDatumaIzvjestaja], [VrstaNadzoraAbrv], [Primjedbe], [InspekcijaId], [LokacijaId], [ZabranaPoduzimanjaOdređenePoslovneAktivnosti], [BrojDanauNadzoru], [VrstaSuradnjeAbrv], [UgovoroSuradnjiId] FROM __actualSet WHERE [__rowcnt] > @__rownoStart AND [__rowcnt] <= @__rownoEnd ORDER BY [__rowcnt] ASC',N'@DatumNadzora1 datetime,@DatumNadzora2 datetime,@__rownoStart int,@__rownoEnd int',@DatumNadzora1='2011-01-01 00:00:00',@DatumNadzora2='2011-12-31 23:59:59',@__rownoStart=35,@__rownoEnd=40
            

    
[b]3.1.11.221[/b]
                EntityCollection<OcevidnikEntity> oc = new EntityCollection<OcevidnikEntity>();
                RelationPredicateBucket bucketOC = new RelationPredicateBucket();
                bucketOC.Relations.Add(OcevidnikEntity.Relations.ObjektEntityUsingObjektId);
                bucketOC.PredicateExpression.AddWithAnd(new FieldCompareValuePredicate(OcevidnikFields.DatumNadzora, null, ComparisonOperator.GreaterEqual, startDate.Value));
                bucketOC.PredicateExpression.AddWithAnd(new FieldCompareValuePredicate(OcevidnikFields.DatumNadzora, null, ComparisonOperator.LessEqual, endDate.Value));

                adapter.FetchEntityCollection(oc, bucketOC, 0, new SortExpression(new SortClause(ObjektFields.Naziv, null, SortOperator.Ascending)), null, null, pageNumber, pageSize);


[b]SQL 2005[/b]
exec sp_executesql N'SELECT [dbo].[Ocevidnik].[BrojDanauNadzoru], [dbo].[Ocevidnik].[DatumIzvjestaja], [dbo].[Ocevidnik].[DatumNadzora], [dbo].[Ocevidnik].[DjelatnostId], [dbo].[Ocevidnik].[Godina], [dbo].[Ocevidnik].[InspekcijaId], [dbo].[Ocevidnik].[ObjektId], [dbo].[Ocevidnik].[PodvrstaObjektaId], [dbo].[Ocevidnik].[PravoNaIzmjenuDatumaIzvjestaja], [dbo].[Ocevidnik].[RedniBroj], [dbo].[Ocevidnik].[UgovoroSuradnjiId], [dbo].[Ocevidnik].[VrstaNadzoraAbrv], [dbo].[Ocevidnik].[VrstaSuradnjeAbrv] FROM ( [dbo].[Objekt] (nolock) INNER JOIN [dbo].[Ocevidnik] (nolock) ON  [dbo].[Objekt].[Id]=[dbo].[Ocevidnik].[ObjektId]) WHERE ( ( [dbo].[Ocevidnik].[DatumNadzora] >= @p1 AND [dbo].[Ocevidnik].[DatumNadzora] <= @p2)) ORDER BY [dbo].[Objekt].[Naziv] ASC',N'@p1 datetime,@p2 datetime',@p1='2011-01-01 00:00:00',@p2='2011-12-31 23:59:59'


                [b]SQL 2008[/b]
                exec sp_executesql N'SELECT [dbo].[Ocevidnik].[BrojDanauNadzoru], [dbo].[Ocevidnik].[DatumIzvjestaja], [dbo].[Ocevidnik].[DatumNadzora], [dbo].[Ocevidnik].[DjelatnostId], [dbo].[Ocevidnik].[Godina], [dbo].[Ocevidnik].[InspekcijaId], [dbo].[Ocevidnik].[ObjektId], [dbo].[Ocevidnik].[PodvrstaObjektaId], [dbo].[Ocevidnik].[PravoNaIzmjenuDatumaIzvjestaja], [dbo].[Ocevidnik].[RedniBroj], [dbo].[Ocevidnik].[UgovoroSuradnjiId], [dbo].[Ocevidnik].[VrstaNadzoraAbrv], [dbo].[Ocevidnik].[VrstaSuradnjeAbrv] FROM ( [dbo].[Objekt] (nolock) INNER JOIN [dbo].[Ocevidnik] (nolock) ON  [dbo].[Objekt].[Id]=[dbo].[Ocevidnik].[ObjektId]) WHERE ( ( [dbo].[Ocevidnik].[DatumNadzora] >= @p1 AND [dbo].[Ocevidnik].[DatumNadzora] <= @p2)) ORDER BY [dbo].[Objekt].[Naziv] ASC',N'@p1 datetime,@p2 datetime',@p1='2011-01-01 00:00:00',@p2='2011-12-31 23:59:59'                

It seems that there is an issue inside the LLBLGen so can you help me track it down so we can fix it ?

Thanks

• Are the two catalogs exactly the same (2005 vs 2008 )?
• Do you have any SQLServerCompatibilityLevel set in the cofig file for both tests?
• In your v31 tests, Is the collection's result count actually what is expected?

Hi,

1. The only difference in the catalogs is that 2008 version don't have 3 fields in the "OcevidnikEntity" but they aren't relevant.
2. I'll get back to you on this I'm out of the office
3. If you are talking about the results in the application they are paged but in-memory, the problem resides on the SQL side where LLBLGen differently generates the query

So I used 2.6 version on both databases and it generates proper SQL select query, while 3.1 version generates the SQL query without the paging involved.

Do you have any other suggestion ?

Thanks

It must be something in your fields or joins what make LLBLGen framework decide to use client paging. Do you have any ntext/image/clob/blob fields in those tables? (Anyway is weird this worked in your v2.6 code.)

Yes, it is weird ... I don't have any of those filed types, I have some nvarchar(max) fields but I have excluded them from the query because I don't need them.

As for SQLServerCompatibilityLevel it is set to default.

Do you have any other suggestion ?

Regards

Just to rule out this possibility, would you please use the latest release of v.3.1.

Hi,

I have downloaded the latest version 3.1.11.7xx and I still don't get the paged query. When I remove the sorting by "ObjektEntity" or use sorting on "OcevidnikEntity" I get the paging ... I hope this helps you in any way.

Regards

Please check the following message : http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=18489&StartAtMessage=0&#103746

I have checked this message earlier, and my point is that my query is paged on client side but that doesn't suit me at all (I have at least 500k records that are pulled to the client).

I see that Frans is referring to the sorting issue, but as I can see this is fixed / sorted in LLBLGen 2.6. In my older application which is using LLBLGen Pro 2.6 same query is paged properly on server side and now I need to upgrade to 3.1 in which paging is not working on server side for some reason.

Can you please check the LLBLGen rules by which engine decided not to page on server side, so we can understand what is the core problem ?

If I don't find the solution to this issue I'd have to downgrade all my work back to old LLBLGen 2.6. I simple can't afford to loose paging.

Thanks

The problem is that you sort on a field which is not in the Select List. As explained by Frans in the other linked thread.

As a workaround, you can fetch a DynamicList of all fields of OcevidnikEntity plus the extra field you sort on ObjektFields.Naziv.

Then you can project the resultSet to an EntityCollection<OcevidnikEntity>. So paging should be done at server side, and only a simple projection will take place on client side.

Yes I know that sorting is causing the issue in 3.1 but can you tell me why this isn't happening in 2.6.

So you are telling me that I have to port all of our code to projections so I can properly sort ). I'd expect that new version will bring some benefits (performance, features, etc.) to us and not force us to change the whole application just to get it working as is was.

Can you please tell me why paging isn't working as in V2.6 ?

Regards

khorvat wrote:

I have checked this message earlier, and my point is that my query is paged on client side but that doesn't suit me at all (I have at least 500k records that are pulled to the client).

No, it reads (pagesize * (pagenumber -1)) + pagesize rows and then closes the datareader.

I see that Frans is referring to the sorting issue, but as I can see this is fixed / sorted in LLBLGen 2.6. In my older application which is using LLBLGen Pro 2.6 same query is paged properly on server side and now I need to upgrade to 3.1 in which paging is not working on server side for some reason.

Can you please check the LLBLGen rules by which engine decided not to page on server side, so we can understand what is the core problem ?

If I don't find the solution to this issue I'd have to downgrade all my work back to old LLBLGen 2.6. I simple can't afford to loose paging. Thanks

Looking at your SQL queries, they're completely different, so there's something else going on. For example there's no difference between 2005 and 2008: they both will generate a CTE. Looking at the query, your v3.1 query is smaller. I'll see where the difference is in.

The main thing is that when you sort on a field not in the projection, you can't specify DISTINCT. With relationships, distinct is required, however neither query has distinct specified, and in v2.6 the check was not totally correct (or better: often decided not to do things on the server because it needed distinct, while in many cases this wasn't necessary).

(edit). In the 2005 variant, you don't use catalog name overwriting, but in the 2008 variant you do?

The v3.1 query misses more than '3' fields compared to the v2.6 one: v3.1:

SELECT          [dbo].[Ocevidnik].[BrojDanauNadzoru], [dbo].[Ocevidnik].[DatumIzvjestaja], [dbo].[Ocevidnik].[DatumNadzora], 
                [dbo].[Ocevidnik].[DjelatnostId], [dbo].[Ocevidnik].[Godina], [dbo].[Ocevidnik].[InspekcijaId], [dbo].[Ocevidnik].[ObjektId], 
                [dbo].[Ocevidnik].[PodvrstaObjektaId], [dbo].[Ocevidnik].[PravoNaIzmjenuDatumaIzvjestaja], [dbo].[Ocevidnik].[RedniBroj], 
                [dbo].[Ocevidnik].[UgovoroSuradnjiId], [dbo].[Ocevidnik].[VrstaNadzoraAbrv], [dbo].[Ocevidnik].[VrstaSuradnjeAbrv] 
FROM ( [dbo].[Objekt] (nolock) INNER JOIN [dbo].[Ocevidnik] (nolock) 
        ON [dbo].[Objekt].[Id]=[dbo].[Ocevidnik].[ObjektId]) 
WHERE ( ( [dbo].[Ocevidnik].[DatumNadzora] >= @p1 AND [dbo].[Ocevidnik].[DatumNadzora] <= @p2)) 
ORDER BY [dbo].[Objekt].[Naziv] ASC

v2.6, 2008:

SELECT TOP 40   [dbo].[Ocevidnik].[Id], [dbo].[Ocevidnik].[Godina], [dbo].[Ocevidnik].[UserId], [dbo].[Ocevidnik].[RedniBroj], 
                [dbo].[Ocevidnik].[ObjektId], [dbo].[Ocevidnik].[FizickaOsobaId], [dbo].[Ocevidnik].[AdresaNadzora], 
                [dbo].[Ocevidnik].[VrstaObjektaId], [dbo].[Ocevidnik].[PodvrstaObjektaId], [dbo].[Ocevidnik].[DjelatnostId], 
                [dbo].[Ocevidnik].[Klasa], [dbo].[Ocevidnik].[URBROJ] AS [Urbroj], [dbo].[Ocevidnik].[DatumNadzora], [dbo].[Ocevidnik].[DatumIzvjestaja], 
                [dbo].[Ocevidnik].[PravoNaIzmjenuDatumaIzvjestaja], [dbo].[Ocevidnik].[VrstaNadzoraAbrv], [dbo].[Ocevidnik].[Primjedbe], 
                [dbo].[Ocevidnik].[InspekcijaId], [dbo].[Ocevidnik].[LokacijaId], [dbo].[Ocevidnik].[ZabranaPoduzimanjaOdredenePoslovneAktivnosti], 
                [dbo].[Ocevidnik].[BrojDanauNadzoru], [dbo].[Ocevidnik].[VrstaSuradnjeAbrv], [dbo].[Ocevidnik].[UgovoroSuradnjiId] 
FROM ( [dbo].[Objekt] INNER JOIN [dbo].[Ocevidnik] 
        ON [dbo].[Objekt].[Id]=[dbo].[Ocevidnik].[ObjektId]) 
WHERE ( ( [dbo].[Ocevidnik].[DatumNadzora] >= @DatumNadzora1 
      AND [dbo].[Ocevidnik].[DatumNadzora] <= @DatumNadzora2)) 
ORDER BY [dbo].[Objekt].[Naziv] ASC

The problem is, whether duplicates will occur or not in this query. in v2.6, the logic to check for this was actually flawed, and sometimes decided 'yes' while it should have been 'no' and 'no' when it actually should have been 'yes'.

As you fetch the entity from a joined resultset, in theory duplicates can occur. The engine then tries to emit 'Distinct' into the query, if it thinks that this is the case. As it's not possible to determine in all cases whether duplicates will occur or not, it tries to determine whether duplicates occur and if it thinks it might, it orders 'distinct' to be present. we updated this logic in 3.1 to be more precise so it gave less false positives (so it actually does less paging on the client).

I don't know whether duplicates can occur in your resultset. So please describe the relationship you're using in the code. When duplicates CAN occur, paging on the server is actually useless, as you could run into a page with just duplicate rows, which results in a single entity.

This particular client-side paging only occurs when distinct is needed AND it can't be emitted into the target query, so not in many cases. In general your queries should be fine. As I said, the engine doesn't pull all rows to the client, just the ones it needs.

If you could describe more why there are differences between the queries (# of fields) and what the type of the relationship is, whether you have Image/ntext/text fields in the resultset or not, we can look into the engine's rules and setup a repro case in v2.6 and 3.1.

(edit) in v3.1, it decides to need distinct, if a relationship in the relationcollection is 1:n. my guess is the relationship you're using is a 1:n relationship, correct?. The thing is... in v2.6, it did too. In fact, the logic in v2.6 should with a 1:n relationship definitely require distinct and as it's not emitted, it will flag the query for 'client side distinct filtering'. As distinct isn't emit-able, the query is flagged for client-side limiting (no TOP). However your v2.6 query does have TOP. So that's a total surprise, to be honest.

Could you check whether the 'OcevidnikEntity.Relations.ObjektEntityUsingObjektId' property returns a 1:n relationship in v3.1 and a m:1 relations in in v2.6?

Hi,

sorry for the delay, as for

If you could describe more why there are differences between the queries (# of fields) and what the type of the relationship is, whether you have Image/ntext/text fields in the resultset or not, we can look into the engine's rules and setup a repro case in v2.6 and 3.1.

In a 3.1 version of DB we have removed 3-4 fields from the OcevidnikEntity but they are not relevant to this issue. In both cases 2.6 and 3.1 Ocevidnik to Objekt relation is m:1, and we don't have any of the problematic (paging) fields types.

I'll try to get the code that will generate the exactly same query (Plain generated adapters not our wrappers, exactly the same settings in 2.6 and 3.1) so we can better test this issue.

Do you need anything else to setup a repro case ?

Thanks

Only info about whether inheritance is used or not. E.g. if you could reproduce it on adventureworks or northwind, we can easily use that as a repro case, as we don't need a schema to test it on.

Hi,

sorry for the delay I didn't get a chance to create a sample project until my colleague steped in a created a project.

So In the attachment you have sample solution for LLBLGen 2.6 and 3.1 with adventureworks (any you have commented out the "same" code for our live project). In the text file you have the generated queries and the profiler results (you can see the SQL impact on our live project in 3.1 version versus 2.6 version).

Please take a look at the sample at get back to us.

Thanks

Reproduced with Visual Studio 2010 (.NET 3.5) and Northwind database. I'll attach the repro shortly for reference.

(repro attached).

Ok, great. So is there any progress in solving this issue ?

Regards

We are looking into it, hang on.

In both DQEs (v2.6 and v3.x) a check is made to see whether the resultset creates unique rows. This check is mainly a simple 'is there a join, and if so, is it a m:1 or 1:1)'. In v2.6, this result is used in the outcome of the logic which decides whether DISTINCT is really necessary. In v3.1 this isn't done, due to the following: (the variable is 'uniqueRows')

uniqueRows isn't added to this clause as it can be 'true' but still distinct could be required: in the case of non-entity projection fetches with a few fields from a bigger entity: these could cause duplicates still, so uniqueRows can't overrule allowDuplicates here, and by adding it to this clause it will overrule it. Caller therefore should set allowDuplicates to the desired value before creating a query.

I.o.w.: v2.6 works in your situation because it has a subtle bug which makes some queries fail in other situations. In v3, we first added a better distinct detection method but we ran into a situation after release which made it fail so we disabled it and went back to the less precise one described above (relation type testing).

So although we know why, we can't fix it by looking at what was done in v2.6 and add that to v3, because that would make some queries fail. We can't change the API as that makes other queries fail too.

So there's not much we can do to fix it without any effort from your part.

Details: If you look into the sourcecode of the runtime (it's in the sourcecode archive from the customer area), the class DynamicQueryEngineBase in Persistence\DynamicQueryEngineBase.cs, method HandleDistinctEmit, line 1379. If you change

selectQuery.RequiresClientSideDistinctFiltering = (!allowDuplicates && !toReturn);

into

selectQuery.RequiresClientSideDistinctFiltering = (!allowDuplicates && !toReturn && !uniqueRows);

you'll get v2.6 behavior.

You can fix this yourself by downloading the sourcecode from the customer area and do the following: you have to sign the ORM support classes dll and the sql server DQE dll with your own strong key and build them together (so just keep ormsupportclasses and sqlserver dqe in the solution If you're using linq as well, keep that project in the solution as well).

If this doesn't work for you, we can add a flag to the DynamicQueryEngineBase class, which drives the addition of uniqueRows to the expression to get v2.6 behavior back. We really don't want to do this, as it is a flag which will be there forever but if you can't proceed with building the code yourself, it's the one option we have to look into to help you.

Update: there's an option for us which is of little harm, and that's extending DistinctFilteringPreferenceType (DynamicQueryEngineBase.DistinctFilteringPreferenceDefault). This is an enum which is already used in the DQEs to determine where distinct filtering has to be done. It was primarily added for databases which don't have solid distinct filtering engines, to speed up the queries on those databases.

We could add an extra option to that, Legacy ( ) which would make the engine do v2.6 style distinct emitting and it would solve your problem.

Would that work?

Hi,

thanks for the detailed explanation, but can you help me with one thing:

Scenario that we are talking about is:

Fetch records from Table A inner join (m:1) to Table B - paging is working as expected After we add sorting on a Table B - paging fails due to distinct rules (we don't have column from Table B in a select)

In almost all (if not all) cases primary key is included in a select, if you put a sort columns in a select you can get distinct in SQL query and it won't fail so we have uniqueness for the paging ?

I know that probably there is more to it that the above but I'm just curious.

As for the Legacy fix that you have mentioned we are ok with it if there is not other solution.

Thanks

khorvat wrote:

Hi,

thanks for the detailed explanation, but can you help me with one thing:

Scenario that we are talking about is:

Fetch records from Table A inner join (m:1) to Table B - paging is working as expected After we add sorting on a Table B - paging fails due to distinct rules (we don't have column from Table B in a select)

True, because the error:

Server: Msg 145, Level 15, State 1, Line 1 ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

will otherwise be thrown by sql server:

select DISTINCT TOP 10 o.*
from customers c inner join orders o on c.CustomerId = o.Customerid
order by c.Country ASC

In almost all (if not all) cases primary key is included in a select, if you put a sort columns in a select you can get distinct in SQL query and it won't fail so we have uniqueness for the paging ?

Not necessarily. If I have a PK with 2 fields (Id and LineNo), and I have just 'Id' in the select list, it will result in duplicates. The info about whether the fields form a complete entity or that the PK fields in the select list are indeed the all pk fields of an entity and the fetch is an entity fetch isn't available to the DQE when it produces the query. We worked on adding this info till we ran into a situation where it's not determinable if the resultset will return duplicates or not (1:n join and projection from right side which is a derived table for example), and further research is required for that to solve that situation (and others), so we put the enhancements on hold for now in this area.

As for the Legacy fix that you have mentioned we are ok with it if there is not other solution.

We'll have a fixed dll for v3.1 for you tomorrow (wednesday).

Fixed.

See attached dll. Usage: as it's a static field, you have to set it once. DynamicQueryEngineBase.DistinctFilteringPreferenceDefault = DistinctFilteringPreferenceType.Legacy;

After that, TOP emitting (which is really the problem) and distinct emitting are done as in v2.6. What the query really need is TOP and it will only emit that into the query if unique rows are guaranteed. This can only be guaranteed if distinct is there OR if it knows if the resultset has only unique rows. In v2.6 it thinks it knows this and flags the resultset as 'unique rows', and emits TOP and pages on server side: distinct can't be emitted due to the error this will cause. On v3, it knows about the queries which don't have unique rows so it uses stricter rules: this leads to false positives on the other side of the equation (like in your case) where queries with unique rows are flagged as 'potentially not with unique rows' and are paged on the client because the row limit couldn't be emitted and distinct can't be emitted as well.

'Legacy' adds the uniquerows flag into the logic and makes the logic work as in v2.6.

This is a global setting, however you can set it per-call if you use adapter. It requires you to do a simple thing: create a derived class of DataAccessAdapter and override CreateDynamicQueryEngine. In your override, first call the base, then set its DistinctFilteringPreferenceToUse property to the setting you'd want to use. This way you can for example create a DataAccessAdapterv26 class which pages like v2.6 but if you need v3 behavior simply use the original one.

Implemented

Thanks for everything, we have tested the new assembly and everything is working as expected in Legacy mode.

I hope you will find a final solution to this problem, and when you do please let us know so we can upgrade our projects.

Thanks again.