Performace of LINQ in case of Skip, Take & count

Question

I have the following code which works fine and I am getting the required details as expected. This data is passed on to the front end to be displayed in a grid.

public object Test(SearchGridParam obj)
{
    List<GridModel> res = 
        (from data in _entity.TableNoTracking
        where (string.IsNullOrWhiteSpace(obj.Criticality) || data.CRITICALITY.Equals(obj.Criticality))
        && (string.IsNullOrWhiteSpace(obj.Search)
                || (!string.IsNullOrWhiteSpace(data.DESCRIPTION) && data.DESCRIPTION.ToUpper().Contains(obj.Search.ToUpper()))
                || (!string.IsNullOrWhiteSpace(data.NUMBER) && data.NUMBER.ToUpper().Contains(obj.Search.ToUpper()))
                )
        select new GridModel
        {
            ID = data.ID,
            NUMBER = data.NUMBER,
            CRITICALITY = data.CRITICALITY,
            WO_STATUS = data.WO_STATUS,
            ASSET_NUMBER = data.ASSET_NUMBER,
            DESCRIPTION = data.DESCRIPTION,
            HOURS = data.HOURS,
            START_DATE = data.START_DATE
        })
    .OrderByDescending(ord => ord.START_DATE)
    .ToList(); //Gives 300+ records depending on the filters

    int count = res.Count();

    return new Result
    {
        Result = res.Skip(obj.Skip).Take(obj.Take), // fetching only 10
        Count = count
    };
}

Context

As per requirement:

• I need the Count after the "where" condition is applied. (I have achieved it using: int count = res.Count();)
• In the final result set, I need only 10 records at a time. (Achieved using: res.Skip(obj.Skip).Take(obj.Take))
• I am getting the data from the database (dbContext).

Question

As you can see, I just need 10 records using skip & take. But, just to get the count, I am having to use ToList() (which loads all data to the memory). Is there any other optimized way of doing it? OR is there any alternative approach to skip, take & count?

What I have tried

• I have tried the following to improve the performance, but it takes the same time (or sometimes more that .ToList())

// *****code above is same*****
.OrderByDescending(ord => ord.START_DATE)
.AsQueryable();

int count = res.Count();

return new Result
{
    Result = res.Skip(obj.Skip).Take(obj.Take).ToList(),
    Count = count
};

Edit 1

I am fetching the data from Oracle DB

Answer 1

The result of using Linq differs depending upon whether your using a Data connection like EntityFramework or similar or not. In your case, it may seem as though you're using EntityFramework because you are querying from _entity.TableNoTracking, but as soon as you use a terminal operation, such as ToList(), then the rest of the Linq expression uses Linq to Objects which operates in memory.

So, when you do your query (all of it before ToList), LINQ will build a database query. After ToList, it does that in memory, so .Count is done in memory.

This is important, especially with Skip, Take and count. In fact, if you use Skip and Take to query 10 records using the database, EntityFramewokr will build an SQL statement that does that - it's the database that selects the 10 records, and LINQ never iterates the whole record set. When you come to count, that's done in memory and you always get 10 or less.

I would suggest that the right way to do this (if you want the actual recordset size) is to do the following:

var corequery = (from data in _entity.TableNoTracking where ...  select ... ).AsQueryable();

var records = corequery.OrderBy().Skip().Take().ToList();

var countofrecords = corequery.Count();

var countofrecordsreturned = records.Count();

Considering the above, Skip and Take form a part of the database query due to appearing before the terminal. A second Count query is also sent to the server, but that constructs a "SELECT Count(*)" kind of query and only returns an integer. You then have the ability to count the number of records in the recordset and seperately count how many records were returned. This obviously allows you to properly calculate how many pages of data you have.

If you're really bothered about roundtrip time of the second count query, I would suggest that you despatch the count query asyncronously. e.g.

    var tcount = query.CountAsync();
    var data = await query.ToListAsync();
    var count await tcount;

The longer data query entirely masks the interconnect overhead of the count query, effectively, barring scheduling and calculation in the database engine, you would get the count for free.