CODEWITHSUNDEEP
sql-serverentity-framework
Anshul Nigam
Anshul Nigam

Reputation: 1628

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

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?

Upvotes: 2

Views: 1241

Answers (2)

rexcfnghk
rexcfnghk

Reputation: 15452

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.

Upvotes: 2

Janne Matikainen
Janne Matikainen

Reputation: 5121

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();

Upvotes: 1

Related Questions