CODEWITHSUNDEEP
c#.netlinq.net-coreentity-framework-core
Vitaliy Zayarniy
Vitaliy Zayarniy

Reputation: 88

How to take count from DB by LINQ and where

I have a problem. I want to make my application faster. My problem is: 

_context.Products.Where(m => m.Name.Contains(part)).Take(20);

I don't want to take the whole collection, I want to take only part of it. I want to take elements, until I have enough elements that satisfy my conditions, and I do not go through my entire collection to get my result. Can I get that with standard LINQ and EF Core methods?

Upvotes: 0

Views: 108

Answers (2)

Wokuo
Wokuo

Reputation: 206

Mostly yes. Until you invoke ToList method or start iterating on this collection all LINQ and EF methods are converted into SQL. It works little bit like a query builder, iteration is a trigger to execute query in this case. Please read about IQueryable interface.

For example above code will be converted into 'SELECT TOP 20 * FROM Products WHERE '

Upvotes: 1

Azaz ul Haq
Azaz ul Haq

Reputation: 1747

Below my answer is based on assumption that you want to get data in chunks instead of taking whole collection at once to make your application run faster.

"I don't want to take the whole collection, I want to take only part of it."

You need to play with both the Skip and Take methods.

For example you can assume a static page size e.g. 20 in your case. And then while querying for the results you need to specify the page index against which you want to get data. To get how many pages are there against your query result, you can first take the count of the records and store it somewhere on clientside/in-memory/cache.

return _context.Products.Where(m => m.Name.Contains(part)).Count(); // to get the total number of records e.g. 74

Then to fetch the chunk of data you specify the page size and page index.

_context.Products.Where(m => m.Name.Contains(part)).Skip(PAGE_INDEX*PAGE_SIZE).Take(PAGE_SIZE);

PAGE_INDEX and PAGE_SIZE are input parameters for your function.

Upvotes: 0

Related Questions