Select 'N' rows and get number of rows using Entity Framework

Question

Currently we have a page where we need pagination so for that i need 2 info
1. Get total number of rows
2. Fetch 'N' number of rows

Currently i am doing it with 2 query, for step 1 something like

 count = db.Transactions
           .AsNoTracking()
           .Where(whereClause
           .Count();

And then

db.Transactions
           .AsNoTracking()
           .Where(whereClause
           .Skip(skipRows)
           .Take(pagesize)
           .ToList();

Is there is any way to optimize it?

rexcfnghk · Accepted Answer · 2015-05-19 08:18:25Z

2

You can try using Local Data:

// Load all Transactions with filtering criteria into the context
db.Transactions.AsNoTracking().Where(whereClause).Load();

// Get Count
var transcationsCount = db.Transactions.Local.Count;

// Paging
var pagedTranscations = db.Transactions.Local.Skip(skipRows).Take(pageSize).ToList();

This should only result in one database query being fired to the database in the initial Load() call.

edited May 19, 2015 at 8:18

answered May 18, 2015 at 5:55

rexcfnghk

15.7k1 gold badge34 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Anshul Nigam Over a year ago

i guess it would be more costly operation, loading all data in memory is not an option, row count is very large

rexcfnghk Over a year ago

I think it depends on where do you want to put your cost, your code will hit the database twice (which is network bound). If you do it my way, you will be memory/CPU bound. You should do a more detailed profiling to see where the bottleneck is and decide on where to optimise.

Janne Matikainen · Accepted Answer · 2015-05-18 06:00:54Z

1

This will return you an IQueryable and you can do queries against that, each will be executed to the db. This way you don't have to rewrite the query each time you want to query something.

var query = (from t in db.Transactions
            where whereClause
            select t);

var count = query.Count();

var items = query
            .Skip(skipRows)
            .Take(pagesize)
            .ToList();

answered May 18, 2015 at 6:00

Janne Matikainen

5,13118 silver badges22 bronze badges

3 Comments

Anshul Nigam Over a year ago

That i am already doing in my project,i am more concerned towards performance.

Janne Matikainen Over a year ago

Performance of what? Obviously if you have just 100 or so items in the transactions, loading them once in memory and doing what ever will be faster than accessing db twice depending on latency and db performance. How ever when the row count reaches certain point it will be more cost efficient to execute 2 queries.

Anshul Nigam Over a year ago

Query is expected to fetch millions of rows and that is why it needs pagination if it would have been in thousands this question would not have been here.

Collectives™ on Stack Overflow

Select 'N' rows and get number of rows using Entity Framework

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related