CODEWITHSUNDEEP
linqlinq-to-sql
Matt Roberts
Matt Roberts

Reputation: 26917

Linq to sql - Optimal way to return sub-set of records AND the total number of records

I was looking at some linq to SQL code that is used for a paged table. In it, it needs to return a sub-set of records, and the total number of records in the database. The code looks like this:

var query = (from p in MyTable select new {p.HostCable, p.PatchingSet});
int total = query.ToList().Count;
query = query.Skip(5).Take(10);

I wanted to dig a bit into what happens when this executes, I see that 2 queries happen - one to get ALL rows from the db, and one to get the subset. Needless to say the performance implications of getting all the records is not good. I guess that the "ToList" forces the query to be executed, then the Count method runs against the entire List.

In re-factoring the statement to be more efficient - this is my improved version:

int total = MyTable.Count();
var query = (from p in MyTable select new {p.HostCable, p.PatchingSet}).Skip(5).Take(10);

This results in a SQL hit for a "Select count.." and then a SQL hit for the actual select of records.

Is this optimal, are there better solutions?

Thanks!

Upvotes: 0

Views: 570

Answers (2)

Donn R
Donn R

Reputation: 118

From what I've seen, this is not possible in LINQ to SQL without a bit of hacking.

I've found that this method works. A quick summary of this method: I convert the IQueryable object to a command object and modify the command text to include the total count in the result set. The original LINQ to SQL converter SQL query uses the ROW_NUMBER() OVER() syntax to page the rows, I just add COUNT(*) OVER() to get the total count.

Add this method to your DataContext class.

public IEnumerable<TWithTotal> ExecutePagedQuery<T, TWithTotal>(IQueryable<T> query, int pageSize, int pageNumber, out int count)
    where TWithTotal : IWithTotal
{
    var cmd = this.GetCommand(query.Skip(pageSize * pageNumber).Take(pageSize));
    var commandText = cmd.CommandText.Replace("SELECT ROW_NUMBER() OVER", "SELECT COUNT(*) OVER() AS TOTALROWS, ROW_NUMBER() OVER");
    commandText = "SELECT TOTALROWS AS TotalCount," + commandText.Remove(0, 6);
    cmd.CommandText = commandText;

    var reader = cmd.ExecuteReader();

    var list = this.Translate<TWithTotal>(reader).ToList();

    if (list.Count > 0)
        count = list[0].TotalCount;
    else
        count = 0;

    return list;
}

You'll have to create a new class that contains all the properties of the original object implementing the IWithTotal interface.

UPDATE: You can't mix mapped and unmapped columns in LINQ to SQL.

public interface IWithTotal
{
    int TotalCount { get; set; }
}

public class Project : IWithTotal
{
    public int TotalCount { get; set; }
    public int ProjectID { get; set; }
    public string Name { get; set; }
}

The DataContext.Translate has some requirements so ensure that your query satisfies those requirements if you have any issues.

Depending on the complexity of the query, you should be able to see an increase in performance. Below are metrics from a testing I did with a count query, a paged select query, and a paged select with count query. The percentages are the query cost relative to batch.

Count - 6%
Select with paging - 47%
Select with paging + Count - 48%

Upvotes: 2

Dave D
Dave D

Reputation: 9007

int total = query.Count();

Rather than selecting everything then doing a count of the objects in memory this will perform the count via a query and just return that number which should be a lot faster.

In response to your update, I don't think you'll get a much better way of doing it. It ultimately boils down into two queries, one to get the total number of records and another to get a subset of them.

Upvotes: 1

Related Questions