Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
486 views
in Technique[技术] by (71.8m points)

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

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
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...