none
How to implement extension for Linq to HQL generator in Entity Framework (needed SQL 'LIKE' with LINQ to Entities) RRS feed

  • Question

  • We need the possibility to use directly SQL 'LIKE' command with LINQ to Entities in Entity Framework. In our legacy application users can enter search wildcards characters %,? into fields, which then translate into sql statement: SELECT * FROM table WHERE name LIKE criteriaString, if criteriaString contains those wildcards. We use Oracle database.

    The only EF possibility what I have found is solution-using-StartsWith-Contains-EndsWith
    Usage of Contains, StartWith and EndWith is, however, not satisfactory because it always puts '%' character in the beginning or at the end, which change meaning of the user entered query.

    Also this solution does not solve the case with wildcards in the middle of criteriaString
    e.g. SELECT * FROM table WHERE name LIKE 'AA%1?1'.

    In NHibernate there was possible to solve it by extension of Linq to HQL genetator.
    available here: nhibernate-linq-provider-extension

     

    My question is how to solve it in Entity Framework.

    Pavel

    Tuesday, June 11, 2013 5:52 AM

Answers

  • On 6/11/2013 7:05 AM, gresspav wrote:

    Hi,
    and thank you for response.

    We used similar approach in our legacy product.
    We had definition of columns names, table names and we constructed sql statements as one big string.
    Problem we have with this is that we are dependant on database (queries must be done in format Oracle/MSSQL recognize) and it complicates refactoring.

      In our new project we want to get rid of this approach (no strings in code) and use type safe coding/linq queries.
    eg:

    var query = from t in GetDatabaseContext().Set<DbCountry>() select t;
    if (!string.IsNullOrEmpty(countryCodeSearchText))
    {
             query = query.IsLike(p => p.CNTRYCODE, countryCodeSearchText);
    }
    var countries = query.ToList();

    You can't do it, and you need to change gears.

    http://msdn.microsoft.com/en-us/library/bb738684.aspx
     You can query against any entity or entities you want, new-up an entity, populate it from the datareader and return an entity/object or List<T> of entities/objects.

    You can't beat a dead horse if it's not beatable.

    Tuesday, June 11, 2013 1:37 PM

All replies