Comments by Riverama (Top 2 by date)

I think that it comes down to the proper separation of the layers in your business model; first you Should define that query in a store procedure, and as initially stated here, use parameters to pass your filtering data.

You are tempted to do the calculation with your paging parameters beforehand, but that creates dependency between your db and the DAL. If you ever need to change your paging logic, you'll have to do it in both layers since one depends in the calculations of the other, alternatively if you left the calculation to the procedure, your DAL would not care how it's done and its only job is to pass the parameters and expect a response.

It's like asking a carpenter to make you a table and explaining him how to drive the nails.