Hi,

I have an aspx page with an LLBLGenProDataSource using a TypedList as the datasource. The LLBLGenProDataSource is bound to a Telerik grid. The main problem I’m having concerns the TypedList and Paging. All the fields in the TypedList are from entities with one to one relationships. However sometimes I also need to include a one-many relationship with another table so I can query the TypedList with values from that table/entity. Note I don’t always need to query the one-many relationship.

Now when the LLBLGenProDataSource pages, it uses the ROW_NUMBER method of paging which is exactly what we want as we have a large no of records to be searched. However, because of the inner join created by the one-many relationship, the paging is returning duplicates once you leave the first page of results. The AllowDuplicates flag of the LLBLGenProDataSource is set to false but I think it is the paging SQL statement that is not working for me.

On the first page, the SELECT DISTICT TOP X statement (including the one-many join) runs and works fine.

For the second page the ROW_NUMBER function is included in the SELECT DISTINCT X statement However the ROW_NUMBER function in the Select means that a distinct recordset cannot be returned as the ROW_NUMBER column is obviously not distinct.

Example paging code is below. The last join aspnet_UsersInRoles is the one-many relationship.

WITH __actualSet AS 

(SELECT DISTINCT TOP 7 [AdminUser].[UserId], 
[aspnet_Membership].[Email], 
[aspnet_Users].[UserName], 
[AdminUser].[FirstName], 
[AdminUser].[LastName], 
[aspnet_Membership].[Password], 
[AdminUser].[HomeTel], 
[AdminUser].[WorkTel], 
[AdminUser].[MobileTel], 
[aspnet_Membership].[LastLoginDate], 
[aspnet_Users].[LastActivityDate] , 
ROW_NUMBER() OVER(ORDER BY [AdminUser].[UserId] ASC) AS __rowcnt 
FROM  ((( [aspnet_Users]  
INNER JOIN [AdminUser]  ON  [aspnet_Users].[UserId]=[AdminUser].[UserId]) 
INNER JOIN [aspnet_Membership] ON [aspnet_Users].[UserId]=[aspnet_Membership].[UserId]) 
INNER JOIN [aspnet_UsersInRoles] ON [aspnet_Users].[UserId]=[aspnet_UsersInRoles].[UserId])) 

SELECT * FROM __actualSet 
WHERE [__rowcnt] > 3 AND [__rowcnt] <= 8 ORDER BY [__rowcnt] ASC

I came across this related post while searching for advice on the forums. Not sure if that helps explain my problem.

http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=3844&HighLight=1

So can you offer me any advice or help or suggestions on the above problem?

I just wondered if there is any work around now..

Admittedly I realise that the LLBLGenProDataSource object is mainly for two way databinding and I'm only using it for read only TypedLists in this example. However we decided to use it as the paging works well and efficiently and is really simple to use with EntityCollections etc..

I think my only option is to write my own custom paging code but I wanted to check for any other advice on here first..

I have to say I've only been working with LLBLGen for 3 months but everyday it "wows" us. It’s awesome.

Thanks in advance.

ToryBoy wrote:

Hi,

I have an aspx page with an LLBLGenProDataSource using a TypedList as the datasource. The LLBLGenProDataSource is bound to a Telerik grid. The main problem I’m having concerns the TypedList and Paging. All the fields in the TypedList are from entities with one to one relationships. However sometimes I also need to include a one-many relationship with another table so I can query the TypedList with values from that table/entity. Note I don’t always need to query the one-many relationship.

Now when the LLBLGenProDataSource pages, it uses the ROW_NUMBER method of paging which is exactly what we want as we have a large no of records to be searched. However, because of the inner join created by the one-many relationship, the paging is returning duplicates once you leave the first page of results. The AllowDuplicates flag of the LLBLGenProDataSource is set to false but I think it is the paging SQL statement that is not working for me.

On the first page, the SELECT DISTICT TOP X statement (including the one-many join) runs and works fine.

For the second page the ROW_NUMBER function is included in the SELECT DISTINCT X statement However the ROW_NUMBER function in the Select means that a distinct recordset cannot be returned as the ROW_NUMBER column is obviously not distinct.

Correct. This is an issue which was reported to use some time ago, and we searched long and hard to get a workaround for this, but the problem is: there's no workaround possible for all situations. The reason is the awkward syntaxis for ROW_NUMBER() OVER()...

What should be done is another query around the actual query though then ROW_NUMBER doesn't work anymore as it can't refer to a column in a derived table.

So really painful discovery, and I'm really sorry.

The workaround for this is to switch to the temptable approach. The SqlServer DQE has now code build in to switch to temptable paging in the case where distinct is required but it can't be provided (as in your example).

Temptable paging isn't necessarily slower. It doesn't fetch all data into the temptable, just the data of the pages before the page you want to fetch. So if you want to fetch page 5, only 1-5 are in the temptable.

I'd suggest to download hte latest runtime library build and try again. It will (should) switch to temptable paging for this situation.

I came across this related post while searching for advice on the forums. Not sure if that helps explain my problem.

http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=3844&HighLight=1

So can you offer me any advice or help or suggestions on the above problem?

I just wondered if there is any work around now..

We build one in into the DQE for your convenience, as the temptable paging approach does work in this occasion. It's really sad to learn that ROW_COUNT() over simply doesn't work in all situations while a simple statement like MySql has or firebird has are really doing the job nicely without any problems.

Admittedly I realise that the LLBLGenProDataSource object is mainly for two way databinding and I'm only using it for read only TypedLists in this example. However we decided to use it as the paging works well and efficiently and is really simple to use with EntityCollections etc..

I think my only option is to write my own custom paging code but I wanted to check for any other advice on here first..

No, you shouldn't write your own paging code If ever, you could always fall back on temptable paging, which is actually quite fast as well (proven tech and pages great also in tables with millions of rows) due to optimizations in the paging query

I have to say I've only been working with LLBLGen for 3 months but everyday it "wows" us. It’s awesome.

Thanks!