ADO.NET EF and LINQ pagging records & translation to SQL

Question

Hi there

I saw a ADO.NET EF and LINQ pagging structure like this:

var query = ...your normal query here...
int totalRecordCount = query.Count();
var pagedQuery = query.Skip(PageIndex*PageSize).Take(PageSize);

This code seems to query all records setting them into the local memory, so it gets the total count. After that, it pages de recording using the skip function into the variable pagedQuery. Am I wrong? How the comipile translate that to SQL? Is the another what to do that?

Answer 1

If the object you are querying implements IQueryable<T> it will build a different query depending on the Extension methods you use. (Which it must be if you are using Linq-to-Entities)

However logging the SQL is not as simple as with Linq-to-SQL.

Either use a profiler, or try adding this Extension method to a static class in your project:

static string ToTraceString<T>(this IQueryable<T> t)
{
  ObjectQuery<T> oqt = t as ObjectQuery<T>;

  if (oqt != null)
    return oqt.ToTraceString();

  return "";
}

With it you should be able to verify the SQL that is created by the different queries.

var query = ...your normal query here...
Console.WriteLine(query.ToTraceString());

var countQuery = query.Count();
Console.WriteLine(countQuery.ToTraceString());
// Should return something along the lines of SELECT COUNT(...) FROM ...

var pagedQuery = query.Skip(PageIndex*PageSize).Take(PageSize);
Console.WriteLine(pagedQuery.ToTraceString());
// Should return something along the lines of SELECT ... FROM ... SKIP x LIMIT y

Answer 2

You can run SQL Server Profiler to see exactly what SQL it is generating...

I believe that the call to Count() does a SELECT COUNT(*) and the Skip().Take() does a SELECT TOP N, so you aren't actually pulling back all the records.