Does filtering of data take place in the controller, service or repository layers?

Question

I am using ASP.NET MVC 3. I get my view's data in the following sequence:

Controller -> Service Layer -> Repository

In my repository I have a GetAll method that brings back all the records for a specific object, like Category.

So if I need a list of the all the categories then in my controller I would have something like:

IEnumerable<Category> categories = categoryService.GetAll();

In the service layer I would have something like:

public IEnumerable<Category> GetAll()
{
     return categoryRepository.GetAll();
}

Now this is what I need to know where do I actually start to filter the data? Can it be done anywhere in one of these 3 layers or does it only have to be in the repository layer? Lets say I need all the parent categories. Do I have the .GetAll.Where(x => x.ParentCategoryId == null); in my controller, service layer, or repository layer?

Do I have it like this in my controller:

IEnumerable<Category> categories = categoryService.GetParentCategories();

And in my service layer I can have:

public IEnumerable<Category> GetParentCategories()
{
     return categoryRepository.GetAll.Where(x => x.ParentCategoryId == null);
}

Or does my service layer have to look like this:

public IEnumerable<Category> GetParentCategories()
{
     return categoryRepository.GetParentCategories();
}

And then in my repository layer like this:

public IEnumerable<Category> GetParentCategories()
{
     return GetAll()
          .Where(x => x.ParentCategoryId == null);
}

Please can someone help clarify this confusion that I have. There might be different scenarios. I might bring back all categories that have an active status. I might bring back categories with an inactive status. Then do I need a method for each?

Answer 1

You should filter at the closest you can from the data source, otherwise you'll be retrieving records to upper layers that will just be discarded due to a filtering option. This does not scale well, so you need to expose filtering capabilities at all layers that require it, but make sure that the actual filtering is performed in lowest layer possible, generally it is performed at the database level.

In the example you posted if use GetAll which return an IEnumerable of all the records and only then apply the filtering you'll have problems in the future because you're basically loading an entire table into memory and only then applying a filtering.

Since you're using EF you could take advantage of the deferred execution properties of the IQueryable. Check:

.NET Entity Framework - IEnumerable VS. IQueryable

Should a Repository return IEnumerable , IQueryable or List?

---

Update: Following up on your comment you should also check:

LINQ to entities vs LINQ to objects - Are they the same?