Multiple search criteria in a repository

Question

I have a question for how to implement multiple criteria for a repository pattern in ASP.net MVC. Imagine a POCO class in EF4

public class people
{ String Name {get;set;}
float Height {get;set;}
float Weight {get;set;}
int age {get;set;}
....
}

If I build up a repository as IPeopleRepository, what kind of methods should I implement for a multiple criteria search (e.g Age > 30, Height >80). Those criteria would be related to the properties in the class and some of the input could be null. Of course I can write a method like

People SearchPeople (int age, float height.....) 

but I have to judge if every variable would be null and append onto the search queries..

So do you have any good ideas on how to implement this function in EF?

Answer 1

It sounds like your looking for something like the Specification pattern.

There is a great article involving EF4 / POCO / Repository / Specification pattern here.

Although i like the pattern, i find it a bit overkill in simple scenarios.

I ended up using the "pipes and filters" technique - basically IQueryable<T> extension methods on your objects to make your repository code fluent.

For a search criteria however, i would be tempted to allow the consuming code to supply the predicate, then you don't have to worry about the parameters.

So the definition would be like this:

public People SearchPeople(Expression<Func<People,bool>> predicate)
{
   return _context.People.SingleOrDefault(predicate);
}

Then the code simply supplies the predicate.

var person = _repository.SearchPeople(p => p.Age > 30 && p.Height > 80);

Some people don't like this technique, as it gives too much "power" to the consumer, because they might supply a predicate like p.Id > 0 and return all the rows in the database.

To counteract that, provide an optional parameter for maxRows. If it's not supplied, default to 100 rows.

Answer 2

First, you need to think if you really need repository search method.
You might want to do direct queries instead of wrapping them to repository.

However, if you think you need the search method than you will likely use something like this:

private People SearchPeople(int? age, float? height)
{
    var baseQuery = db.People;

    if (age != null)
        baseQuery = baseQuery.Where(arg => arg.Age > age);
    if (height != null)
        baseQuery = baseQuery.Where(arg => arg.Height > height);

    return baseQuery.ToList();
}

Although you didn't want to do this, I can't think of better solution.