Linq-to-entity, get results + number of rows in one query

Question

I saw several questions on this subject, however they were 2 years (or more) old, so I would like to know if something has changed about this.

The main idea is to populate the gridview and create custom paging. So I need results and row count.

In SQL, it will be something like:

SELECT COUNT(id), Id, Name... FROM ... WHERE ... 

Getting the whole simple simple request. However, I would like to be consistent and use Linq2Entities.

So far I have been using a two-query approach (against sql server) because it just works. I would like to optimize it and use only one query.

I tried this:

 var query = from o in _db.Products select o; var prods = from o in query select new { Count = query.Count(), Products = query }; 

This creates a very nasty and long request with really unnecessary cross-connections and other things that I really don't need or don't need.

Is there a way to get paginated results + counting all objects in one simple query? What is the recommended approach here?

UPDATE:

Just tried FutureQueries, and either I'm doing something wrong, or actually doing two queries. This shows my sql profiler:

 -- Query #1 SELECT [GroupBy1].[A1] AS [C1] FROM ( SELECT COUNT(1) AS [A1] FROM [dbo].[Products] AS [Extent1] WHERE 1 = [Extent1].[CategoryID] ) AS [GroupBy1]; 

And the following line:

 -- Query #1 SELECT [Extent1].[ID] AS [ID], [Extent1].[Name] AS [Name], [Extent1].[Price] AS [Price], [Extent1].[CategoryID] AS [CategoryID] FROM [dbo].[Products] AS [Extent1] WHERE 1 = [Extent1].[CategoryID]; 

C # code:

 internal static List<Product> GetProducts(out int _count) { DatabaseEntities _db = new DatabaseEntities(); var query = from o in _db.Products where o.CategoryID == 1 select o; var count = query.FutureCount(); _count = count.Value; return query.Future().ToList(); } 

Did I miss something? According to my profiler, it does the same thing except for the added line in the request (- Request No. 1).

Answer 1

Look at future queries to do this in EntityFramework.Extended . The second example on this linked page uses FutureCount() to accomplish exactly what you want. Adapted here:

 var q = db.Products.Where(p => ...); var qCount = q.FutureCount(); var qPage = q.Skip((pageNumber-1)*pageSize).Take(pageSize).Future(); int total = qCount.Value; // Both queries are sent to the DB here. var tasks = qPage.ToList(); 

Answer 2

this library 'EntityFramework.Extended' is no longer supported, use instead: entityframework-plus and go here: https://entityframework-plus.net/query-future to see how you can get the number and records in one query.