Hi i am on version 1.0.2004.22 final released on 10 june 2005.

i want to ise paging mechanism with MaxNumberOfItemsToReturn. but i am still getting all the records ?

Sample code snippet :

int maxCount = 10 Collection.MaxNumberOfItemsToReturn = maxCount;

Collection.GetMulti(filterObject, maxCount, sortExpression, relations, prefetchPath);

i am getting all the rows back from the DB matching this criteria. so the filters and prefetchpath and other stuffs are correct as i am getting correct output back from the server.

can anyone solve this issue for me?or guide me if i am doing anything wrong

You are using a very old runtimeLibrary. The latest runtimeLibrary for the 1.0.2004.2 was released on 03-jan-2006. Please download it and use it instead. Make sure your application uses the new RTL as the Visual Studio sometimes keep shadows of old versions of the referenced assemblies and uses them instead of the newer versions.

Report back, if the issue still exists.

P.S. consider migrating to v1.0.2005.1, a trivial minimal effort will need to do the migration.

Walaa wrote:

You are using a very old runtimeLibrary. The latest runtimeLibrary for the 1.0.2004.2 was released on 03-jan-2006. Please download it and use it instead. Make sure your application uses the new RTL as the Visual Studio sometimes keep shadows of old versions of the referenced assemblies and uses them instead of the newer versions.

Report back, if the issue still exists.

P.S. consider migrating to v1.0.2005.1, a trivial minimal effort will need to do the migration.

hey thanx for such quick response... i am really ompressed... i affraid i wont be able to switch to the library version you are suggesting as there are few stuffs in production which i just cant change right now... so the issue i mentioned is a known issue with the version i am using?

i affraid i wont be able to switch to the library version you are suggesting

You'd just need to replace a dll.

You are using a very old dll, and many things have been solved since then, including some issues in the paging mechanism.

Anyway, you are not using the pageSize & pageNumber parameters of the GetMulti() overload.

Can you check the generated query and run it against the database to see the records returned?

Walaa wrote:

i affraid i wont be able to switch to the library version you are suggesting

You'd just need to replace a dll.

You are using a very old dll, and many things have been solved since then, including some issues in the paging mechanism.

Anyway, you are not using the pageSize & pageNumber parameters of the GetMulti() overload.

Can you check the generated query and run it against the database to see the records returned?

ooops my mistake i sent you the wrong code snippet.. believe me i have written something of this sort in my code

int maxCount = 10 Collection.MaxNumberOfItemsToReturn = maxCount;

Collection.GetMulti(filterObject, maxCount, sortExpression, relations, PageNumber, PageSize);

ok i will try replacing the DLL .. one more question..can I still use the LLBLGen Pro GUI if i replace the dll? does if affect autogenerated code?? i think i should and if it affects then do i need to take the latest GUI as well?

No, the RTL won't affect the Designer nor the generated code.

Walaa wrote:

No, the RTL won't affect the Designer nor the generated code.

I found the old issue and want to continue where i left before instead of creating another thread

I migrated to higher version of LLBLGen 2.5 Still my problem is not solved

Please consider following line of code

if there are 100 records in the table and I pass

collection.GetMulti(filter, 50, sortExpression, relations, prefetchPath, excludedFields, 1, 10); then also it returns me all 100 rows..

i have to loop through the collection and seperate unwanted row, but the real problem is my collection usually contain huge amount of data and slows down performance like anything.. DO you have some suggestions to overcome this?

thanks Ammit

could you paste the generated query?

goose wrote:

could you paste the generated query?

Sorry for that i think i have got it working fine now

Though i have 2 questions

1) if there are 200 records in the table and I pass

collection.GetMulti(filter, 10, sortExpression, relations, prefetchPath, excludedFields, 1, 100); then also it returns me all 100 rows.. though i have set MaxRecord count = 10 from the name it apears that MaxCount must always get priority over PageSize

2) Consider this.

I have around 1000 records in my DB. and i fetch only 1 page of it having page size = 10 Everything is working fine but along with this I also want to know the total records in the DB for the given filter. So i have to make another call.

int RecordCount = collection.GetDbCount(filter, relations);

so in reality I am hitting DB twice once for getting the data for mage and second time for the count. Is there any other way i get get the count as OUT parameter of GetMulti() ???

thanks Ammit

1) if there are 200 records in the table and I pass

collection.GetMulti(filter, 10, sortExpression, relations, prefetchPath, excludedFields, 1, 100); then also it returns me all 100 rows.. though i have set MaxRecord count = 10 from the name it apears that MaxCount must always get priority over PageSize

No, maxNumberOfItemsToReturn is ignored if paging is used.

2) Consider this.

I have around 1000 records in my DB. and i fetch only 1 page of it having page size = 10 Everything is working fine but along with this I also want to know the total records in the DB for the given filter. So i have to make another call.

int RecordCount = collection.GetDbCount(filter, relations);

so in reality I am hitting DB twice once for getting the data for mage and second time for the count. Is there any other way i get get the count as OUT parameter of GetMulti() ???

2 database hits are needed. Can you get what you want with only one SQL query?

Walaa wrote:

1) if there are 200 records in the table and I pass

collection.GetMulti(filter, 10, sortExpression, relations, prefetchPath, excludedFields, 1, 100); then also it returns me all 100 rows.. though i have set MaxRecord count = 10 from the name it apears that MaxCount must always get priority over PageSize

No, maxNumberOfItemsToReturn is ignored if paging is used.

2) Consider this.

I have around 1000 records in my DB. and i fetch only 1 page of it having page size = 10 Everything is working fine but along with this I also want to know the total records in the DB for the given filter. So i have to make another call.

int RecordCount = collection.GetDbCount(filter, relations);

so in reality I am hitting DB twice once for getting the data for mage and second time for the count. Is there any other way i get get the count as OUT parameter of GetMulti() ???

2 database hits are needed. Can you get what you want with only one SQL query?

YES Please consider this.

I have a table EMP with following data

ID sname 7 asd 1 asd 2 asd 44 asd 9 asdasd

Now i want paging as well as total number of record in one db hit

select TOP 3 ID, RecordCount = (select count (*) from EMP where sname = 'asd') from EMP where sname = 'asd'

Results

ID RecordCount 2 4 44 4 7 4

this requires only one DB hit.

what i want to say is what if GetMulti Takes two more arguements collection.GetMulti(list of usual param , bool isRecordCountRequired, ref int recordCount );

so i can pass my bunch of params and set isRecordCountRequired = true and pass recordCount by reference and you can fill it with RecoundCount you received

if it is isRecordCountRequired = true then only construct the totalnumber record count querry else it will still behave as it used to behave earlier..

it will atleast help me in the kind of paging mechanism i have.

as for paging one must know how many pages are there.

I want to display a page at a time with 10 records but i also must know total number of records for the given filter to display number of pages link on UI.

will be waiting for your feedback for the same

Thanks Ammit

select TOP 3 ID, RecordCount = (select count (*) from EMP where sname = 'asd') from EMP where sname = 'asd'

There will be a performance issue in the above query, the RecordCount = (select count (*) from EMP where sname = 'asd') will be executed as many as the number of rows returned.

I think using 2 separate queries would be the ideal solution.

If you still believe otherwise, you may formulate the above query using a DynamicList or use an Extended EntityFactory to add that extra field to your entity as shown here: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=6756

Walaa wrote:

select TOP 3 ID, RecordCount = (select count (*) from EMP where sname = 'asd') from EMP where sname = 'asd'

There will be a performance issue in the above query, the RecordCount = (select count (*) from EMP where sname = 'asd') will be executed as many as the number of rows returned.

I think using 2 separate queries would be the ideal solution.

If you still believe otherwise, you may formulate the above query using a DynamicList or use an Extended EntityFactory to add that extra field to your entity as shown here: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=6756

Excuse me for being such a drag on this.

I agree that the above querry I wrote is not an ideal one but what about following one?

select top 3 ID, 0 as RecordCount from EMP where sname='asd' Union All select '' as ID,count (*) as RecordCount from EMP where sname='asd' order by 2 desc

here it won't do full table scan so many times...

I saw the link you sent me but some how i believe how others will be applying pagiing with out going through all these pain?

I think I am missing something very obvious with paging.

But i still believe to know total number of rows is an integral part of implementing paging..

Will be waiting for some valuable comments on this..

But i still believe to know total number of rows is an integral part of implementing paging..

Yes it is, that's why you should call GetDBCount on the entityCollection/table you want to page, to get the total number of records.

Walaa wrote:

But i still believe to know total number of rows is an integral part of implementing paging..

Yes it is, that's why you should call GetDBCount on the entityCollection/table you want to page, to get the total number of records.

Aah did I mention the quesrry i just posted gets the job done in one DB call and it looks quite efficient as well

select top 3 ID, 0 as RecordCount from EMP where sname='asd' Union All select '' as ID,count (*) as RecordCount from EMP where sname='asd' order by 2 desc

ID RecordCount 0 4 2 0 44 0 7 0

It does not perform count(*) for number of records returned from TOP querry

Having the count in the query isn't efficient, as you've to execute it for every page, while with a separate call you have to execute it once, that's why it's separate. Some queries are very performance intense or paging takes place over a lot of rows (unknown to the runtime!) and executing the query TWICE every time for the count isn't efficient.

The maxnumberof... parameter is ignored as you requested a page, so the page is returned.

Otis wrote:

Having the count in the query isn't efficient, as you've to execute it for every page, while with a separate call you have to execute it once, that's why it's separate. Some queries are very performance intense or paging takes place over a lot of rows (unknown to the runtime!) and executing the query TWICE every time for the count isn't efficient.

The maxnumberof... parameter is ignored as you requested a page, so the page is returned.

Not for every page only when i pass that param to True . So it will be applicable in loading first page only.. thought that is particular implementation issue.. but from LLBLgen library perspective It should return me number of records ONLY IF I ASK for while doing getmulti() so that i do not have to make two DB hits... I got little carried away in writting both the things in "ONE DB HIT"

Its not a very big issue but just a thought.. because making connection to DB and data transfer over a wire twoce for same purpose will also take some time..for returning data in one call and getting count in another one.. as against to a mechanism where i can get both the things done in one shot...

I am not Blocked and you can treate this discussion on lower priority over some show stoppers...

thanks

Ammit

because making connection to DB and data transfer over a wire twoce for same purpose will also take some time..for returning data in one call and getting count in another one.. as against to a mechanism where i can get both the things done in one shot...

IMHO that's not an issue at all, because of connection pooling, also using the Adpater you may execute both queries in the same openned connection and then close the connection afterwards.

Also please note that the GetDBCount() will generate a scalar query which returns one value.

In general the Database performance is the only performance hit to worry about not the network/bandwidth transfer and not the process of instantiating/openning a connection.