TotalRowCount with paging in Linq2SQL

Question

Im getting a paged datasource from a fairly complex linq query. My problem is that is takes twice as long to execute since I need to get the total row count before paging is applied in order to calculate the nr. of pages to dispaly. (the query will be executed twice)

Is there somehow I can do this in a more optimal way? Like using SQL's @@rowcount somehow?

This is roughly what the query looks like right now. (using Dynamic linq)

    public IList<User> GetPagedUsers(string filter, string sort, int skip, 
       int take, out int totalRows)
    {
            using(var dbContext = new DataContext())
            {
               IQueryable<User> q = GetVeryComplexQuery(dbContext);

               //Apply filter if exists
               if (!string.IsNullOrEmpty(filter))
                   q = q.Where(filter);

               //Set total rows to the out parameter
               totalRows = q.Count(); //Takes 4 sec to run

               //Apply sort if exists
               if (!string.IsNullOrEmpty(sort))
                   q = q.OrderBy(sort);

               //Apply paging and return
               return q.Skip(skip).Take(take).ToList(); //Takes 4 sec to run
            }
    }

Why wont this for example work?

TblCompanies.Dump(); //150 rows
ExecuteQuery<int>("select @@ROWCOUNT").Dump(); //returns 0

Answer 1

Linq2Sql will actually translate the use of Skip & Take into the SQL Statement so even if you could get @@RowCount the value will not be great than your take parameter.

If we take the following simple example (lifted from MSDN http://msdn.microsoft.com/en-us/library/bb386988.aspx).

IQueryable<Customer> custQuery3 =
    (from custs in db.Customers
     where custs.City == "London"
     orderby custs.CustomerID
     select custs)
    .Skip(1).Take(1);

foreach (var custObj in custQuery3)
{
    Console.WriteLine(custObj.CustomerID);
}

the following SQL is generated

SELECT TOP 1 [t0].[CustomerID], [t0].[CompanyName],
FROM [Customers] AS [t0]
WHERE (NOT (EXISTS(
    SELECT NULL AS [EMPTY]
    FROM (
        SELECT TOP 1 [t1].[CustomerID]
        FROM [Customers] AS [t1]
        WHERE [t1].[City] = @p0
        ORDER BY [t1].[CustomerID]
        ) AS [t2]
    WHERE [t0].[CustomerID] = [t2].[CustomerID]
    ))) AND ([t0].[City] = @p1)
ORDER BY [t0].[CustomerID]

So you can see that the skip is actually occurring inside the SQL Statement and therefore @@RowCount is going to equal the amount of rows returned by the query and not the entire result set.