Doing a Count in a Linq to SQL Query (created as IQueryable) gets all the rows

Question

Class Customer has the following method, which returns an IQueryable with the linq query to get the active sales for a customer:

    public IQueryable<SalesHeader> QueryCurrentSales()
    {
        // this.SalesHeaders is an association defined in the DBML between
        // the Customer and SalesHeader tables (SalesHeader.CustomerId <-> Customer.Id).
        return this.SalesHeaders
            .Where(sh => sh.Status == 1).AsQueryable();
    }

The idea is to centralise the query definition in a single point, so that later we can get the SalesHeader rows, do a count, paginate using the PaginatedList class (from NerdsDinner), and add further Where conditions when searching Sales within active SalesHeaders.

However, after running the SQL Sever profiler, I've discovered that doing the following, gets all the rows and then does the Count in memory.

    // cust is an object of type Customer.
    int rows = cust.QueryCurrentSales().count();

I guess that the problem lies in the fact that we have used the AsQueryable() method, but I don't know what would be the correct way to accomplish what we intended.

Answer 1

You haven't shown what SalesHeaders is. The fact that you feel you need to do AsQueryable suggests that it's returning an IEnumerable<T> rather than an IQueryable<T>... which is probably the problem. If you could show SalesHeaders, we may be able to help.

It's possible that instead of using AsQueryable you may be able to just cast to IQueryable<SalesHaeder> if the SalesHeaders property is returning an expression which could actually be returned as IQueryable<SalesHeaders> in the first place - but in that case I'd suggest changing the property type instead.

EDIT: Okay, if it's an entity set then that's probably tricky to fix directly.

I know it's ugly, but how about something like:

public IQueryable<SalesHeader> QueryCurrentSales()
{
    // You *may* be able to get away with this in the query; I'm not sure
    // what LINQ to SQL would do with it.
    int id = this.Id;

    return context.SalesHeaders
                  .Where(sh => sh.Status == 1 && sh.CustomerId == id);
}

Here I'm assuming you have some way of getting to the data context in question - I can't remember offhand whether there's an easy way of doing this in LINQ to SQL, but I'd expect so.