Using Entity Framework 4 to search inside two comma separated lists

Question

I have an entity that has a property called YearsAvailible, this is a comma separated list of financial years e.g. 05,09,10 I have an API that is passes a string[] of year names and I need to search for all of the entity that have a financial year that is in the passed array.

The best I can come up with is this:

var hash = new Hashtable();
foreach(var year in financialYears)
{
    var categories = from expCat in All()
                     where expCat.YearsAvailable.Contains(year)
                     select expCat;
    foreach (var category in categories)
    {
        if (!hash.ContainsKey(category.Id))
        {
            hash.Add(category.Id, category);
        }
    }
}

return hash.Values;

Whilst this works, it produces multiple database queries and doesn't express its intent very well. Is there a tidier way to do this?

Answer 1

Fundamentally, if you want to query based on individual fields, you will need to fix the underlying data structure issues. In this case, that CSV field really needs to be exploded to do this search on the Database's side of the street.

If you can't fix the field, but you can limit the initial results to a reasonable number of entities before filtering, you will probably get better performance grabbing the results then filtering the preliminary results in memory to handle this permutation.

If the list is huge and you can't handle it that way, another idea would be to use Lucene to index the field outside the database then search against that index.