使用实体框架时,得到同一个数据库中的行程总数和页面的行 [英] Get Total Count and Page Rows in same database trip when using Entity Framework
问题描述
我目前使用下面的方法来获得客户的一页,也为总数。 。唯一的问题是,我做2数据库旅行 - 一个用于获取总数和其他获取的实际行的页
我的问题是:我用实际行查询结合TOTALCOUNT查询,使实体框架在一个数据库中旅同时发送查询?
公共IList的GetPageOfCustomers(字符串名称,诠释SKIPCOUNT,
INT的pageSize,OUT INT TOTALCOUNT){使用
(CustomerEntities E =新CustomerEntities()){
//第一个查询
VAR的查询=(从C在e.Customers
,其中c.NAME.Contains(名称)
选择新{
c.CustomerID,C .name和c.CITY,c.STATE,c.COUNTRY
})
.Distinct()
.OrderBy(S = GT; s.NAME)
.ThenBy( S => s.CITY)
.ThenBy(S = GT; s.CustomerID);
//第二查询(在单独的数据库行程执行)
INT TOTALCOUNT =(从C中e.Customers
其中c.NAME.Contains(名)
选择新{
c.CustomerID,c.NAME,c.CITY,c.STATE,c.COUNTRY
})
.Distinct()
。计数();
返回query.Skip(SKIPCOUNT)。取(pageSize的).ToList();
} //使用
} //方法
的终结论
根据数据库往返和回来项目的数量的成本,可能会更快/更容易执行一次的基本查询,做C#服务器上的分页/计数操作。即
VAR的结果=(从C在e.Customers
,其中m.Name.Contains(名称)
选择新的{c.CustomerId,c.NAME,c.CITY,c.STATE,c.COUNTRY})
.Distinct()
.OrderBy(S = GT; s.NAME)
.ThenBy(S = GT; s.CITY)
.ThenBy(S = GT; s.CustomerId)
.ToList();
= TOTALCOUNT results.Count;
返回results.Skip(SKIPCOUNT)。取(pageSize的).ToList();
这将只执行一个数据库调用,但不会在SQL Server上执行分页操作。
编辑:
也看看这个的查询数据的页面,并获得实体框架4.1总数更好的办法?
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.
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
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?
这篇关于使用实体框架时,得到同一个数据库中的行程总数和页面的行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!