使用实体框架时，可以在同一数据库中获取总计数和页面行数 [英] Get Total Count and Page Rows in same database trip when using Entity Framework

问题描述

我目前正在使用以下方法获取一个客户页面以及总计数。唯一的问题是我正在进行2个数据库访问 - 一个用于获取总计数，另一个用于获取页面的实际行。

I am currently using the following method to get a page of customers as well as the total count. The only problem is that I am making 2 database trips - one for getting the total count and the other for getting the actual rows for the page.

我的问题是： strong>我可以将总计查询与实际行查询相结合，以便Entity Framework在单个数据库访问中发送查询

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 trip?

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(name)
    select new {
        c.CustomerID, c.NAME, c.CITY, c.STATE, c.COUNTRY
    })
        .Distinct()
        .Count();

    return query.Skip(skipCount).Take(pageSize).ToList();
     }//END of  USING
   }//END of  METHOD

推荐答案

根据数据库往返的成本和返回的项目数量，执行基本查询一次可能会更快，更容易，并对c＃服务器进行分页/计数操作。即

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

这将只执行一个数据库调用，但不会在sql服务器上执行分页操作。

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

编辑：

另外看一下这个更好地查询一个数据页面并获得实体框架中的总计数4.1？

这篇关于使用实体框架时，可以在同一数据库中获取总计数和页面行数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！