Get Total Count and Page Rows in same database trip when using Entity Framework

I thought and research a lot on this issue. Right now and with EF 6, there are 2 good practices:

(1) The first solution is to have a Stored Procedure (I know, I Know, you usually want to avoid Stored Procedures when you work with EF, go to solution 2 then!), which returns multiple results. This article explained it:

Entity Framework Sprocs with Multiple Result Sets

(2) The second best practice is to use "Query Future" feature of Entity Framework Plus package. This is a very cool extension to Entity Framework and can run multiple queries in one database trip.


Depending on the cost of the database roundtrip and number of items coming back, it might be faster/easier to perform the base query once and do the paging/count operations on the c# server. i.e.

var results = (from c in e.Customers
               where m.Name.Contains(name)
               select new { c.CustomerId, c.NAME, c.CITY, c.STATE, c.COUNTRY })
              .Distinct()
              .OrderBy(s => s.NAME)
              .ThenBy(s => s.CITY)
              .ThenBy(s => s.CustomerId)
              .ToList();
totalCount = results.Count;
return results.Skip(skipCount).Take(pageSize).ToList();

This will only perform one database call, but won't perform the paging operations on the sql server.

Edit: Also take a look at this Better way to query a page of data and get total count in entity framework 4.1?


Comments

  1. Yehuda

    • 2021/3/31

    My question is: Can I combine the totalcount query with the actual rows query so Entity Framework sends both the queries in a single database 

  2. Joe

    • 2016/4/27

    public IList GetPageOfCustomers(string name, int skipCount, int pageSize, out int totalCount) { using(CustomerEntities e = new CustomerEntities()) { //FIRST QUERY var query = (from c in e.Customers where c.NAME.Contains(name) select new { c.CustomerID, c.NAME, c.CITY, c.STATE, c.COUNTRY }) .Distinct() .OrderBy(s = > s.NAME) .ThenBy(s = > s.CITY) .ThenBy(s = > s.CustomerID); //SECOND QUERY ( executed in a separate database trip) int totalCount = (from c in e.Customers where c.NAME.Contains

  3. Eden

    • 2017/6/13

    The following query will get the count and page results in one trip to the database var query = context.Set<EventTable>(); var page = query.

  4. Alistair

    • 2015/11/22

    public IList GetPageOfCustomers(string name, int skipCount, int pageSize, out int totalCount) { using(CustomerEntities e = new CustomerEntities()) { //FIRST QUERY var query = (from c in e.Customers where c.NAME.Contains(name) select new { c.CustomerID, c.NAME, c.CITY, c.STATE, c.COUNTRY }) .Distinct() .OrderBy(s = > s.NAME) .ThenBy(s = > s.CITY) .ThenBy(s = > s.CustomerID); //SECOND QUERY ( executed in a separate database trip) int totalCount = (from c in e.Customers where c.NAME.Contains

  5. Flores

    • 2019/1/5

    The following query will get the count and page results in one trip to the database, but if you check the SQL in LINQPad, you'll see that it's not very 

  6. Terry

    • 2018/6/17

    var selectSql = " SELECT TotalCount = COUNT (*) OVER (), E.* FROM [table] E "; var rows = context.Set<EventTable> ().FromSql<EventTable> (selectSql, parameters.Select (p => p.Value).ToArray ()).ToArray (); This select works, but i don't have TotalCount property in my EventTable class, because i don't want that property in database.

  7. Kelmendi

    • 2020/12/7

    c# - I have a data call in a Linq to Entities powered data access layer that is designed to make paged calls. In doing so, I need to select 

  8. Finnegan

    • 2021/1/3

    Get total row count in Entity Framework, Query syntax: var count = (from o in context.MyContainer where o.ID == '1' from t in o.MyTable select t).Count ();. Method syntax: var count Accepted Answer That is the way to get your row count using Entity Framework. You will probably see faster performance on the second+ queries as there is an initialization cost the first time that you run it.

  9. Cory

    • 2018/6/16

    Yes, it takes two queries to database. Count() gives back the number of rows that match query and second query takes just requested page of 

  10. Simeon

    • 2021/1/25

    EF core paging. Select total count in same query, The following query will get the count and page results in one trip to the database var query = context.Set<EventTable>(); var page = query. Select total count in same query entity-framework-core. English (en) I have paging and i want to select count in the same query using simple sql (EF7):

  11. Hayes

    • 2017/8/24

    Let's say we have a context db with an entity db.Schools. The 'N+1 Select' problem: Minimising the trips to the database.

  12. Atreus

    • 2016/9/17

    Entity Framework uses these attributes when creating and modifying the database. The maximum length specified in the entity class defines the maximum size of the database field. In the view model, the StringLength attribute is used to identify the maximum size of the field input.

  13. Conner

    • 2015/9/20

    If you are calling the database using Entity Framework, you may write this a query is run to retrieve the number of pupils at that school, so in total 

  14. Mac

    • 2019/6/5

    This metadata is cached globally and is available to other instances of ObjectContext in the same application domain. Opening the database connection: Moderate 1: As needed. Because an open connection to the database consumes a valuable resource, the Entity Framework opens and closes the database connection only as needed.

  15. Armando

    • 2017/11/3

    However, it's not as heavy as LINQ to SQL or Entity Framework. Once to find out the total number of records, something that is required by many paging 

  16. Calvin

    • 2016/3/13

    Database servers generally offer alternative methods of counting data, about the database, like this: select sum (spart.rows) from sys.partitions 

Comments are closed.

Recent Posts