Adding paging and filtering to typical Linq Specification pattern?

I have an ASP.NET MVC2 app that has heavy use of grids. I'd like to see if there is a way to add efficient paging and filtering to the typical Specification pattern.

Basically, the call that starts it all looks like this:

PatientByUserIdSpecification spc = new PatientByUserIdSpecification(userId);
return this.patientRepository.FindAll(spc).ToList();

Now, I know I can do something like this below and get a subset of row as a function of the grid's settings for paging and filtering:

return this.patientRepository.FindAll(spc).OrderBy(a => a.Id).Skip(start).Take(limit).ToList();

But, all the filtering occurs in the middle layer, with a full pull of all records by userId, which can grow to hundreds of records per user over time. This means lots of inefficient network chattiness. So, obviously, one needs to push down the criteria such that the SQL generated by NHibernate and Linq filters better.

The LinqRepository code is basically:

public IQueryable<T> FindAll(ILinqSpecification<T> specification)
{
    return specification.SatisfyingElementsFrom(this.Session.Linq<T>());
}

public virtual IQueryable<TResult> SatisfyingElementsFrom(IQueryable<T> candidates)
{
    if (this.MatchingCriteria != null)
    {
        return can开发者_如何学编程didates.Where(this.MatchingCriteria).ToList().ConvertAll(this.ResultMap).AsQueryable();
    }

    return candidates.ToList().ConvertAll(this.ResultMap).AsQueryable();
}

And the PatientByUserIdSpecification, as an example, has the MatchingCriteria:

public override Expression<Func<Patient, bool>> MatchingCriteria
{
    get { return p => p.Cases.Any(c => c.CaseUsers.Any(x => (x.User.Id == this.userId))); }
}

I was thinking that having the below would do it, but the query is still too broad.

public virtual IQueryable<TResult> SatisfyingElementsFrom(IQueryable<T> candidates, int start, int limit, string sort, string dir)
{
    if (this.MatchingCriteria != null)
    {
    return candidates.Where(this.MatchingCriteria).Skip(start).Take(limit).ToList().ConvertAll(this.ResultMap).AsQueryable();
    }

    return candidates.ToList().ConvertAll(this.ResultMap).AsQueryable();
}

Can I and how do I setup the ability to generate better SQL?

The calls to .ToList() in the following code are causing the query to be executed before the Dynamic LINQ is applied:

public virtual IQueryable<TResult> SatisfyingElementsFrom(IQueryable<T> candidates) 
{
    if (this.MatchingCriteria != null)
    {
        return candidates.Where(this.MatchingCriteria).ToList().ConvertAll(this.ResultMap).AsQueryable();
    }
    return candidates.ToList().ConvertAll(this.ResultMap).AsQueryable(); 
} 

You'll need to refactor to remove them and run the Dynamic LINQ against the SQL objects' schema in order to be able to filter on the server-side.