Time to start returning IQueryable<T> instead of IList<T> to my Web UI / Web API Layer?

Question

I've got a multi-layer application that starts with the repository pattern for all data access and it returns IQueryable to the Services layer. The Services layer, which includes all of the business logic, returns IList to the Controllers (note: I'm using ASP.NET MVC for the UI layer). The benefit of returning IQueryable in the data access layer is that it allows my repositories to be extremely simple and the database queries to be deferred. However, I'm triggering the database queries in my services layer so that my unit tests is more reliable and I don't give flexibility to the Controllers to reshape my queries. However, I've recently encountered several situations where deferring the execution of queries down to the Controllers would have been significantly more performant because the Controllers had to do some projections on the data that was UI specific. Additionally, with the emergence of things like oData, I was starting to wonder if end points (e.g. web UI or web apis) should be working directly with IQueryable. What are your thoughts? Is it time to start returning IQueryable from the services layer to the UI layer? Or stick with IList?

This thread here: http://stackoverflow.com/questions/718624/to-return-iqueryablet-or-not-return-iqueryablet seems to vouch for returning IList to the UI layers, but I was wondering if things are changing because of new emerging technologies and techniques.

Answer 1

I like to stick with the IQueryable Interface when possible, the only problem is when you end up doing complex filtering or re-query on demand at the Controller level, if you have something like:

//DATA ACCESS
    public IQueryable<T> GetStudents()
    {
    return db.Students;
    }

And in your controller you do some re-sharping because your client want to filter some data of that result, surely you will be tempted to do it at the controller level:

var result = obj.GetStudents().Where(d=>d...);

and for me its ok, but just imaging if any other module need to use that same filter, you cant call it because its on the controller level. So for me its a thing of balance between DRY, flexibility, and how scalable is the system. If you need a fully scalable system you will need to do some or several overloads to GetStudents() method and get rid of any re-sharping at the controller level.