CODEWITHSUNDEEP
asp.net-mvcentity-frameworksearchrepository-patternentity-framework-4.1
Kassem
Kassem

Reputation: 8276

Search Functionality Using GenericRepository Pattern and a SearchService

I am using the Generic Repository pattern to abstract my EF 4.1 DbContext. I am also using a service layer to query and commit changes to the repository(s). Anyway, what I'm trying to achieve here is allow the user to enter a search phrase into the search bar. Then the SearchService has to query the database to find any TopLevel categories, sub-categories or items that contain the search phrase in their names. On a first glance, I thought that would be a simple thing to do, but apparently things are a little bit more complicated. Here's what I tried to do:

    public IList<Item> Search(string searchPhrase)
    {
        var result = new List<Item>();

        var tmp = from c in _repository.GetQuery<TopLevelCategory>(c=>c.Children)
                  where c.Name.Contains(searchPhrase)
                  select c;
        if(tmp.Count() > 0)
        {
            foreach (var c in tmp)
            {
                var children = c.Children;
                foreach (var childCategory in children)
                {
                    result.Concat(childCategory.Items);
                }
            }
        }

        var tmp2 = from c in _repository.GetQuery<ChildCategory>(c=>c.Items)
              where c.Name.Contains(searchPhrase)
              select c;
        if(tmp.Count() > 0)
        {
            foreach (var childCategory in tmp2)
            {
                result.Concat(childCategory.Items);
            }
        }

        var tmp3 = from c in _repository.GetQuery<Item>()
                   where c.Title.Contains(searchPhrase)
                   select c;
        if(tmp3.Count() > 0)
        {
            result.Concat(tmp3);
        }
        return result;
    }
}

I know that looks ugly and messed up, but I was just giving it a shot to see if it returns the correct results. Well it didn't, it threw the following exception:

Exception Details: System.InvalidOperationException: There is already an open DataReader associated with this Command which must be closed first.

Source:

Line 31:          foreach (var childCategory in children)
Line 32:          {
Line 33:                 result.Concat(childCategory.Items);
Line 34:          }

So anyway, there has to be a smarter way of doing this... Any suggestions?

Upvotes: 0

Views: 585

Answers (2)

Chandermani
Chandermani

Reputation: 42669

The error you are getting may be due to MARS. You just need to enable Multiple Active Result Sets (MARS), just add 'MultipleActiveResultSets=True' into your connection string. Check here


  1. You can create a view that joins names in TopLevel, Childlevel and items. Then expose this view as an entity. Remember that EF will have issues with views as its designer tried to determine ID field.
  2. You can look at lucene in case you think you require better\robust queries capabilities.

Upvotes: 0

Ladislav Mrnka
Ladislav Mrnka

Reputation: 364369

The exception says you that you have openned DataReader to load top level categories with their children but you are triggering lazy loading in the inner foreach loop. That reaquires another DataReader to open and read lazy loaded items:

// Iterate top level categories => fist openned DataReader
foreach (var c in tmp)
{
    // Child category is eager loaded
    var children = c.Children;
    foreach (var childCategory in children)
    {
        // Items are not eager loaded => trigger lazy loading and open new DataReader
        result.Concat(childCategory.Items);
    }
}

To solve this you must modify your connection string and add MARS support MultipleActiveResultSets=true. MARS is supported at least by SQL Server 2005 and 2008. Another way to avoid this is eager load items as well.

This looks more like task for full text search on database level.

Upvotes: 2

Related Questions