CODEWITHSUNDEEP
c#linqoracle-databasenhibernatelinq-to-nhibernate
Kronos
Kronos

Reputation: 482

Get element page number

I have 10,000 items in a table that displays 20 items per page on client side( items can be additionally filtered)

I need to find item among all elements. Then I need to get the page number, where element located and go to that page on client and select it

My previous question is Get item index (rownum) .

But I got no answer on it.

Probably there is another way to find the page number where item located without getting all table data?

I am using Linq to Nhibernate, Database - Oracle.

Edit: Thanks for replies, but it's not so easy

Example:

In table db, i have

 ID          NAME 
 1           Item1 
 2           
 3
 4
 5
 20
 31
 .....        ....
 5000

In client side, i filtered data 

query = query.Where(...).Where(...).Where(...).OrderBy(...)

and got result

 ID                                         ROW_NUM_IN_QUERY
 4                                          1
 300                                        2
 2                                          3
 31                                         4
 .....        ....
 402                                        50
 800                                        51

I can't link to field ID or another in query, because they may be in a different order

I can get rownum at simple SQL. But Linq to NHibernate can't do it. It havn't function something like IndexOf or RowNumber without full getting data from table.

this variant :

var indexed = myDataSource.Select( (x,n) => new { Value = x, RowNumber = n } );

Not suported exception during execute query

Upvotes: 4

Views: 963

Answers (3)

Kronos
Kronos

Reputation: 482

I abandoned this idea, because i could not implement it with Nhibernate Linq.

But I use easier variant.

I use search as quick filter - display all found data in grid, without going to the page of first found element.

Thx for all replies and comments.

Upvotes: 1

Ethan Brown
Ethan Brown

Reputation: 27282

You could use the indexed overload of select (http://msdn.microsoft.com/en-us/library/bb534869.aspx):

var indexed = myDataSource.Select( (x,n) => new { Value = x, RowNumber = n } );

This gives you an enumeration of anonymous classes that contain the row number. From there, you can get a specific record:

var specificRecord = indexed.FirstOrDefault( x => x.Value.Key == "SomeKey" );

Assuming that doesn't return null (i.e., your key was found), you can then get the page it's on with some simple math:

var page = (int)Math.Floor( (double)specificRecord.RowNumber / pageSize );

Note: you will have to test this method to see if it gets handled in the database, or has to pull your ten thousand records into memory; I've only tested it without a database, so it may not be very efficient. If this does not work, then I recommend creating a stored procedure in your database that returns the row and page number.

Upvotes: 2

Servy
Servy

Reputation: 203814

First off, if you don't provide any ordering then the entire concept of an index is meaningless. Next, there is no simple, easy way to get what you want; it will need to be somewhat dependent on the specific query.

If, say, you're ordering the data based on created by date you could do something like this:

var someItem = queryToFetchIndividualItem;

var indexOfItem = table.OrderBy(item => item.CreatedDate)
//any other filtering provided on your query goes here, i.e. other where statements
.Where(item => item.CreateDate < someItem.CreatedDate)
.Count()

if you're sorting on a "Title" then you could do something like this:

var indexOfItem = table.OrderBy(item => item.Title)
//any other filtering provided on your query goes here, i.e. other where statements
.Where(item => item.Title < someItem.Title)
.Count()

I assume you see the pattern here. If we get the count of items that would come before the interesting item in the query we get the index. The database can optimize the query for the fact that we're only interested in the count.

Upvotes: 4

Related Questions