c# mysql count_.net c#使用实体框架时,在同一数据库行程中获取总计数和页行

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

Talk1:

I would like to get the distinct count of the same result set that is being fetched for page rows, else I might get an inaccurate total count.

Talk2:

where m.NAME.Contains(name) What is m?, shouldn't that be c?

Talk3:

, Thanks for pointing that out. I have changed m to c.

Talk4:

, You mean create a stored procedure which will execute both the queries?

Talk5:

With Entity Framework Extended you can send multiple queries in one package. (See "Future Queries").

Solutions1

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.

Talk1:

This will work but I was looking for executing 2 queries in a single database trip, else I could end up with huge number of rows being returned by database, which I am avoiding.

Talk3:

Can you put this in your post, so I can mark this as an answer?

Talk4:

Sure, sorry about that. I'm just getting into StackOverflow

Talk5:

The solution is not scalable. If you need 1page, don't load all table to the server

Solutions2

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:

(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.

Talk1:

I looked into the Future feature of EF+, but it lacks async for EF Core. Therefor I prefer to have 2 roundtrips to the database with async support.

Talk3:

Yes I did, why?

Talk4:

- huh, I thought it has async method.