Complex, dynamic query in Entity Framework

Question

I need to create a function that receives a list of strings, and returns a list of all the items that match (i.e., SQL "LIKE", case-insensitive and ignoring diacritics):

// Pseudocode example
IEnumerable<Item> Search(List<String> patterns)
{
    var result = new List<Item>();

    foreach (var Item in context.Items)
    {
        bool matches = true;

        foreach (var pattern in patterns)
        {
            if (!Item.Name.Contains(pattern))
            {
                matches = false;
                break;
            }
        }

        if (matches)
        {
            result.Add(Item);
        }
    }

    return result;
}

While something similar to this works, it's less than ideal (it seems horribly inefficient).

Is it possible to create a (possibly LINQ) query that generates something similar to the following SQL?:

SELECT *
FROM items
WHERE items.name LIKE :pattern1
  AND items.name LIKE :pattern2
  ...
  AND items.name LIKE :patternN

Kirk Broadhurst · Accepted Answer · 2012-08-01 02:04:28Z

3

You can simply iterate your patterns and apply a WHERE clause for each.

var patterns = new List<string>();
using (var context = new MyDataContext())
{
    var query = (IQueryable<Area>)context.Areas;

    foreach (var pattern in patterns)
    {
        query = query.Where(a => a.Description.Contains(pattern));
    }

    return query.ToList();
}

As you probably know, the query is lazy-executed, and in this case won't be executed until the ToList call after all patterns are applied.

answered Aug 1, 2012 at 2:04

Kirk Broadhurst

28.9k20 gold badges111 silver badges179 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Tordek Over a year ago

What about the other part of the question? Would Contains handle diacritics, or is there a method that will?

Kirk Broadhurst Over a year ago

@Tordek You can specify a case-insensitive match with StringComparer.CurrentCultureIgnoreCase, but I don't know about diacritics. To be honest I'd not expect that they can be handled easily and you may need a mapping pattern to map (for example) an á to an a.

Collectives™ on Stack Overflow

Complex, dynamic query in Entity Framework

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related