none
Linq to entities and LIKE

    Question

  •  

    I spent some time last night trying to implement a query having a LIKE expression as well as SKIP and TAKE expressions with parameter for Sql server 2000.

     

    I could not have Linq work for the LIKE expression, so I tried an Object query with LIKE, SKIP and LIMIT to find that LIMIT did not work with parameters on Sql 2000.

     

    So I ended up mixing Object query and Linq in the same expression, it seems to work but produces some rather complex SQL.

     

    I thought Linq to Entities turned the Linq expression tree into e SQL, so how could it solve a problem that Object query could not solve ?

     

    Code Block

     

    List<User> users = db.Users.Where("it.UserName LIKE @usernameToMatch",

    new ObjectParameter("usernameToMatch", usernameToMatch))

    .OrderBy(u => u.UserName).Skip(pageIndex * pageSize).Take(pageSize).ToList();

     

     

     

    Is there a better way to do this ?

     

    Will this work with other providers when they are availble ?

    Thursday, October 25, 2007 4:22 PM

Answers

  • JPAK;

     

    In LINQ, you can use "Contains", "StartsWith", or "EndsWith" similar to "LIKE".  For example, against Northwind you could do the following:

     

    Code Block

    using (NorthwindEntities db = new NorthwindEntities())

    {

      string name = "Restaurant";

      int skip = 0;

      int take = 10;

      var q = (from cus in db.Customers

                where cus.CompanyName.Contains(name)

                select cus).OrderBy(c => c.CompanyName).Skip(skip).Take(take);

      foreach (Customer c in q)

      Console.WriteLine("{0} {1}", c.CompanyName, c.ContactName);

    }

    Console.ReadLine();

     

    Yes, as you discovered, going from an eSQL string/builder-based ObjectQuery<T> to a LINQ expression is supported (in fact, db.Customers does a CreateQuery<Customer>("[Customers]") under the covers, which is simply an eSQL shortcut for "SELECT VALUE customer FROM [Customers] AS customer").  However, it is not possible to call additional query builder methods once you've built a LINQ expression.

     

    Note that LINQ to Entities does not create an eSQL string, but directly builds a canonical command tree which is passed to the provider, just as eSQL is parsed and passed to the provider as a canonical command tree.  LINQ is generally more restrictive in what is supports than eSQL, although in some cases LINQ exposes nice "sugar" functions to simplify generation of more complex SQL strings.

     

    HTH,

     

    -Mike

    Thursday, October 25, 2007 11:55 PM
  • Pluralization (and "Singularization") is one of the features we looked hard at for the designer (and underlying API) but had to cut for version 1.  The problem is not with the code (we actually have a couple of routines that are pretty advanced that we could borrow from) but with testing all of the different variations.

     

    There is also the issue that the pluralization rules would be english-language based (so we'd need a way to disable them for other languages), but we still believe even an english-only version would be useful. 

     

    The only saving grace is that the designer does make it relatively easy to rename entities, but this is still one of my top 5 features that I wish we could have gotten in for version 1 (but leaves us something to do for V2... :-)

    Friday, October 26, 2007 5:26 PM

All replies

  • JPAK;

     

    In LINQ, you can use "Contains", "StartsWith", or "EndsWith" similar to "LIKE".  For example, against Northwind you could do the following:

     

    Code Block

    using (NorthwindEntities db = new NorthwindEntities())

    {

      string name = "Restaurant";

      int skip = 0;

      int take = 10;

      var q = (from cus in db.Customers

                where cus.CompanyName.Contains(name)

                select cus).OrderBy(c => c.CompanyName).Skip(skip).Take(take);

      foreach (Customer c in q)

      Console.WriteLine("{0} {1}", c.CompanyName, c.ContactName);

    }

    Console.ReadLine();

     

    Yes, as you discovered, going from an eSQL string/builder-based ObjectQuery<T> to a LINQ expression is supported (in fact, db.Customers does a CreateQuery<Customer>("[Customers]") under the covers, which is simply an eSQL shortcut for "SELECT VALUE customer FROM [Customers] AS customer").  However, it is not possible to call additional query builder methods once you've built a LINQ expression.

     

    Note that LINQ to Entities does not create an eSQL string, but directly builds a canonical command tree which is passed to the provider, just as eSQL is parsed and passed to the provider as a canonical command tree.  LINQ is generally more restrictive in what is supports than eSQL, although in some cases LINQ exposes nice "sugar" functions to simplify generation of more complex SQL strings.

     

    HTH,

     

    -Mike

    Thursday, October 25, 2007 11:55 PM
  • Adding to Mike’s respond.

    Your observation that Skip and Limit with parameters are not supported over SQL Server 2000 is correct. This is because SQL Server 2000 does not support TOP (@parameter) and we rely on TOP when translating Skip and Limit.

    The reason your Linq query works is that the expressions  “pageIndex * pageSize” and “pageSize” given as arguments to Skip and Take are evaluated on the client, not sent as parameters.

    Thanks,

    Kati

    Friday, October 26, 2007 4:42 PM
  • Thank you Mike,

     

    I know I could have used StartWith or Contains, but I am working on a membership provider and the methods pass the %wildcard as part of the parameter, so it was not an option.

     

    One more question:

    Will the next versions of the designer be using pluralization or sigularization to name Entities and EntitySets ?

    Right now, if I have a table named Users, both the entity and the EntitySet are named Users which is quite confusing at first.

    So we have to go manually rename all Entities to the singular form.

    Even if it did not take into account the irregular forms of pluralization at first, it would be nice to have somthing like:

     

    If Table Name ends with 's'

    If Table Name ends with 'ies'

    use Table Name for EntitySet and remove the ''ies' and add a 'y' for Entity

    Else

    use Table Name for EntitySet and remove the 's' for Entity

    Else

    If Table Name ends with 'y'

    remove 'y' and add 'ies' for EntitySet and use Table Name for Entity

    Else

    add 's' for EntitySet and use Table Name for Entity

     

    All we would have to deal with manually is the Person, Octopus, Caribou.... tables

    Friday, October 26, 2007 4:44 PM
  • Pluralization (and "Singularization") is one of the features we looked hard at for the designer (and underlying API) but had to cut for version 1.  The problem is not with the code (we actually have a couple of routines that are pretty advanced that we could borrow from) but with testing all of the different variations.

     

    There is also the issue that the pluralization rules would be english-language based (so we'd need a way to disable them for other languages), but we still believe even an english-only version would be useful. 

     

    The only saving grace is that the designer does make it relatively easy to rename entities, but this is still one of my top 5 features that I wish we could have gotten in for version 1 (but leaves us something to do for V2... :-)

    Friday, October 26, 2007 5:26 PM
  • You can add an extension method to the String class and tie it to a Model Defined Function in the .edmx.

     

    See http://jendaperl.blogspot.com/2011/02/like-in-linq-to-entities.html

    Friday, February 04, 2011 4:14 PM
  • Dear JendaPerl
    Linksoft.cz

    I read your paragraph:

    -----------------------------------------------------------------------------------------------------------------

    You can add an extension method to the String class and tie it to a Model Defined Function in the .edmx.

    See http://jendaperl.blogspot.com/2011/02/like-in-linq-to-entities.html

    -----------------------------------------------------------------------------------------------------------------

    I removed the following attribute and tag to get rid of compile error:

    ef4ex:RenameTo="Like"


    <ef4ex:CodeBlock>
         throw new Exception("Not implemented");
    </ef4ex:CodeBlock>

    The program compiles and runs. But the query returned no result (might be null) although the expected return result should be multiple rows.

    I don't what's wrong? Maybe the function is not mapped to SQL server.

    I hope to get your help.

     

    Monday, October 31, 2011 7:32 PM
  • I guess you have to add xmlns:ef4ex="http://jenda.krynicky.cz/schemas/EF4ex" into the root tag of the .edmx for the attribute and tag to be properly ignored. I'll amend the post.

     

    Try to run the SQL Profiler to see what query was sent to the database.


    ----------------------------------
    http://jendaperl.blogspot.com
    A Perl developer in the world of C#
    Tuesday, November 01, 2011 9:47 AM
  • I have this extension method

     public static IQueryable<T> Like<T>(this IQueryable<T> source, string propertyName, string keyword)
            {
               var type = typeof(T);
               var property = type.GetProperty(propertyName);
               var parameter = Expression.Parameter(type, "p");
               var propertyAccess = Expression.MakeMemberAccess(parameter, property);
               var constant = Expression.Constant("%" + keyword + "%");
               
               var like = typeof(SqlMethods).GetMethod("Like",
                          new Type[] { typeof(string), typeof(string) });
               MethodCallExpression methodExp =
                     Expression.Call(null, like, propertyAccess, constant);
               Expression<Func<T, bool>> lambda =
                     Expression.Lambda<Func<T, bool>>(methodExp, parameter);
               return source.Where(lambda);
            }

    What changes should I make into it to let it work for EF? How should I change it to use Contains instead?

    Here is how I call it:

     if (!string.IsNullOrEmpty(qtype) && !string.IsNullOrEmpty(query))
                {
                    clients = clients.Like(qtype, query);
                }
    
                int Total = clients.Count();

    qtype contains the column's name and query contains the keyword typed.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Friday, March 29, 2013 9:04 PM
  • Contains only generate a Like when the parameter is constant, e.g. entity.StringProperty.Contains("ABC") . For a search that takes a variable like entity.StringProperty.Contains(parameter1), L2E will generate a IndexOf > 0 expression instead.

    If your L2E provider is SQL Server, you can use a SqlFunction in the where method.



    Visual C++ MVP

    Saturday, March 30, 2013 9:26 PM
  • StartWith won't use index.

    something Like 'search_string%' will use any created string index in your database.

    StartWith will be transformed in CHAR_INDEX(something, 'search_string') = 1 which is catastrophic in terms of performances.


    Olivier MARTY

    Thursday, October 10, 2013 1:21 PM
  • And you have found another reason not to use plural table names.  For me, I do not like "to deal with" plural names, where they should be singular in the first place.  To me, and I would think to anyone who writes classes as well as table definitions, those things are descriptions of one record structure or one prototype, not names of collections.  For things that ARE collections, such as arrays or lists of objects or sets of records, I suffix the single name of the table definition or class with another singular noun for the type of collection.  So, I never have to deal with plurals.  If you don't see the logic of this, and still insist on using plurals, keep in mind that English is not the only language with irregular plurals, and some languages don't even alter a word to a plural form, but use other language constructs to indicate plurality.  It is hard enough to get English speaking programmers to spell singular nouns correctly, let alone getting those who do not speak English as a primary language to pluralize correctly. I have in the past backed down on the table name Users, because User is a reserved word in SQL, but lately, I simply quote or bracket [User] to avoid that problem.


    • Edited by bpatin Thursday, March 06, 2014 8:06 PM
    Thursday, March 06, 2014 7:54 PM
  • SELECT Id, Title, Author FROM Book WHERE Published >= '2014-1-1'

    just doesn't read right. The table is a collection so according to what you wrote it should be

    SELECT Id, Title, Author FROM BookTable WHERE Published >= '2014-1-1'

    Reads just as bad.

    I'd rather fix an occasional ArticleSery (as an incorrect singularization of ArticleSeries) than have my SQL and LINQ read wrong.

    We don't work on the same project so we don't have to agree.


    http://jendaperl.blogspot.com<br/> A Perl developer in the world of C#

    Thursday, March 06, 2014 10:45 PM