The following code does do paging...

SomeCollection collection - new SomeCollection();
...setup prefetch paths, sorts and filters.....

collection.GetMulti(filter, 0, sortexpression, null, prefetchPath, CurrentPageIndex + 1, PageSize);

and the following code does NOT do paging....

SomeCollection collection - new SomeCollection();
...setup prefetch paths, sorts and filters.....
RelationCollection relationCollection = new RelationCollection();
relationCollection.Add(SomeEntity.Relations.SomeRelation);

collection.GetMulti(filter, 0, sortexpression, relations, prefetchPath, CurrentPageIndex + 1, PageSize);

I looked at the sql generated/executed in the output window to verify it does not generate the correct paged query.

ORMSupportClassesl version 2.5.8.118, SQL Server dll version 2.5.7.1129. Self servicing.

Any advice?

what does 'not correct' mean? You get no paging at all? Beware that with prefetch paths, the parameterizedthreshold has to be > than the page size

Sorry for not being clear.

There is no paging information at all. The query is just a normal query as if I had not passed paging information at all.

I comment out the relations - run the code - paging is generated in the SQL. I uncomment out the relations - run the code - paging is NOT generated in the SQL.

Obviously, the parameters are exactly to the same each time it is run....dataset is 7000 records and paging size is 25.

Could you please your real code? Just to be sure everything is ok with the relationCollection construction.

Daelmo......thanks for your response and I will post the code, but seriously, kind of hard to screw up a relationCollection isn't it?

RelationCollection relationCollection = new RelationCollection();
relationCollection.Add(ClaimEntity.Relations.CompanyEntityUsingCompanyId);

This is a real problem. Just do a simple test of it, you will see. I hope Frans can look at this, since this needs to be solved for other users - but I switched it over to a TypedList for now as the paging does work on that.

I couldn't reproduce it.

I used SQL Server Express 2005. And the Northwind database, with a SelfServicing Windows application. Paging is always done, with and without relations/joins, and with and without setting the DQE compatability level.

Also please note that for the first page, a TOP pageSize is executed instead of the normal paging query. This is true for all of the above combinations.

Wayne does your entity/table contain a field that isn't compatible with a Distinct statement (eg. Text or nText or Image)? I found that when adding a join via a FetchEntityCollection that LLBL Gen will attempt to use a Distinct statement but if such a field exists then it can't so it reverts to returning all records (ie. no paging).

Related post: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=13342

Yes, the entity contains a text field.....so paging works...then once I put in a relation, (which causes a join), the paging stops.

Ugh....that seems pretty bad.....and seems easier if LLBLGen would just leave out the distinct clause.

Worse thing about this, is it 'silently' degrades the performance of your app!

Is there any solution on the horizon for this situation?

In v2.6, we added extra logic so 'Distinct' is only emitted in the query if the query really results in duplicates. This still could be the case in situations where you know there aren't but that's unavoidable, as it's not always detectable. In v2.5 and earlier, it was ALWAYS emitted, but that's not always necessary.

In the case where it is necessary to emit distinct, and you have a distinct violating type, paging happens on the client. This is unfortunate, but there's no other way. Paging on the client means: the datareader reads rows till the page is read. So it is slightly less efficient due to the data transportation to the client.

The reason duplicates can't be used, is that if you fetch entities, and you have duplicates in your resultset, paging will result in wrong results, as there aren't duplicate entities fetched, just 1. So if a page in the resultset exists of solely duplicates, you'd get a page of 1 entity, which is of course not what you want, hence the requirement of having distinct rows, and the only way to get distinct rows in situations where duplicate rows exist is by using distinct. If that can't be done due to types which can't be used with distinct, paging is switched to the client (with distinct filtering there as well, this isn't ideal, but there's no other way).

Sounds better than what is there today.

What 'general' situations have to be there to get duplicates? I believe 99% of all my queries/joins/relations would not ever result in duplicate rows....

WayneBrantley wrote:

Sounds better than what is there today.

The situation 'today' isn't bad either. It's also not popping up in the majority of queries. Only in queries where filtering on related entities is performed AND the entity fetched has distinct violating types.

What 'general' situations have to be there to get duplicates? I believe 99% of all my queries/joins/relations would not ever result in duplicate rows....

all 1:n joins result in duplicates. So if you use m:1/1:1 joins, it won't result in duplicates. With 1:n joins, one could say: 'yes, but in practise they're 1:1', but that's not detectable.

My situation is that many tables do have a text/notes field in them. So, now that I know this (just discovered it because of speed issues, otherwise it would have been totally a mystery) - I would actually like to KNOW which queries did not result in proper paging....somehow....

In cases where it does not, I could potentially exclude the fields that cause the problem or created typedlists or something. However, I really do not 'know' without looking at how each entity is used in the application - which of course is not optimal.

Not saying, I have a solution - just a bit of a 'potential problem' that is not optimal.

I agree it's not optimal, but it's the only solution that's possible. You as a developer call a method to fetch data and you specify paging parameters. So what you expect is getting a page back. The framework then will try to get that page as optimal as possible, however it sometimes can't use that code due to for example Distinct issues. So it has to use a fallback scenario, as the page has to be delivered. So it falls back to client-side paging over a datareader, so it's not as dramatic as it sounds, for example if you fetch page 3, and the page is 10 entities, it will fetch at most 30 rows, as it uses a paging mechanism over the open datareader. So you don't have to worry it fetches the complete set to the client.

(edit) could you try with v2.6 beta to see if it is better there?

For me, I think this would help - as this is a simply 1:1 relationship that is failing for me. My only thought is, I use your product blindly and let it do the magic in the background. In this case, the magic does not produce what I thought - which I might be able to correct with an excluded field, typed list, etc.....I would like to KNOW that paging was NOT done even though it was requested! ?

As far as 2.6 - I am so far behind right now, I have not even looked at 2.6 beta yet.

WayneBrantley wrote:

For me, I think this would help - as this is a simply 1:1 relationship that is failing for me. My only thought is, I use your product blindly and let it do the magic in the background. In this case, the magic does not produce what I thought - which I might be able to correct with an excluded field, typed list, etc.....I would like to KNOW that paging was NOT done even though it was requested! ?

erm... paging IS done, Wayne. Just not in the way you expect it to be, as I described above. Paging is done at a different place in the pipeline. You can page in several ways through a resultset for example.

erm... paging IS done, Wayne. Just not in the way you expect it to be, as I described above. Paging is done at a different place in the pipeline. You can page in several ways through a resultset for example.

Sorry - when I refer to paging, I am referring to a 'only bringing the subset of data down from the sql server that you need'. That is the optimal way for our sites to work of course - and when that functionality automatically 'degrades' to a less than optimal way of paging, would be nice to know.

On one hand, nice your product takes care of this for me, but can be not nice, because I might have been able to make it optimal by removing some fields, etc.

Again, I am not sayiing I have any answers - just making some 'observations'.

WayneBrantley wrote:

erm... paging IS done, Wayne. Just not in the way you expect it to be, as I described above. Paging is done at a different place in the pipeline. You can page in several ways through a resultset for example.

Sorry - when I refer to paging, I am referring to a 'only bringing the subset of data down from the sql server that you need'. That is the optimal way for our sites to work of course - and when that functionality automatically 'degrades' to a less than optimal way of paging, would be nice to know.

On one hand, nice your product takes care of this for me, but can be not nice, because I might have been able to make it optimal by removing some fields, etc.

Again, I am not sayiing I have any answers - just making some 'observations'.

The only alternative is NOT to page then, as paging on the server is simply not possible in all situations. You have to live with that fact, Wayne, as we all have to, how sad it might sound.

I too wished it was a different story, but unfortunately, it's not. Luckily you as a developer don't have to worry about all that: when it's not possible to do on the server, the client side pager takes over so you do get the page of data

I wouldn't say it's impossble. Why not use a paged result of the the distinct set of keys to return the actual objects. This definetly has to be more efficient in real scenarios where databases may contain millions of recods.

I'm thinking I'm having this same problem when I sort on a related entities field. Is there anyway I would be able to do the above?

Kyle

Do you have a blob field inside your main entity? If yes, do you need to fetch it in the paged resultSet?

I don't actually. My problem is more like this User: Id, Name Phone: UserId, TypeId, PhoneNumber Email: UserId, TypeId, Email

I want to fetch a list of users, sorted by the phone number or email with a prefetch of phone number and email.

This trys to retrieve a list of all the users and it times out. It doesn't page the query it sends to the db.

BaileyK83 wrote:

I want to fetch a list of users, sorted by the phone number or email with a prefetch of phone number and email.

This trys to retrieve a list of all the users and it times out.

Please provide the relevant code where you are doing such thing.

BaileyK83 wrote:

It doesn't page the query it sends to the db.

Are you providing paging parameters to the fetch routine?

        Public Overrides Sub OnBindGrid()

            With GetDataAccessAdapter()
                .FetchEntityCollection(Entities, GetBucket, 0, GetSort, GetPrefetch, Nothing,   Me.ListingGridView.PageIndex + 1, Me.ListingGridView.PageSize)
                Me.ListingGridView.VirtualItemCount = .GetDbCount(Entities, GetBucket)
            End With

            Me.ListingGridView.DataSource = Me.Entities
        End Sub

We need the code for: Entities, GetBucket, GetSort, GetPrefetch.