Converting SQL to LINQ, Part 10: Like (Bill Horst)

Converting SQL to LINQ, Part 10: Like (Bill Horst)

  • Comments 11

This post assumes you’ve read my previous Converting SQL to LINQ posts.

 

I haven’t had much in the way of specific requests for more LINQ posts, so I’d still welcome any suggestions that people want to offer.  I did get one request about how to mimic the LIKE keyword functionality in VB LINQ.

 

In SQL, the LIKE keyword compares a string field against a pattern and returns a Boolean value representing whether the string matches the pattern.  For example, if I wanted to select every customer in the 206 area code, I could use this SQL query:

 

SQL

SELECT *

FROM CustomerTable

WHERE Phone LIKE ‘206*’

 

 

The LIKE expression can include certain wildcard characters, which may differ from system to system.  In the above example, * matches any string, so ‘206*’ represents any string that begins with 206.

 

VB happens to include a Like operator, so this is an easy conversion to make.  The LINQ version of this above SQL expression is specified below:

 

VB

From Contact In CustomerTable _

Where Contact.Phone Like “206*”

 

 

The VB Like keyword has the following wildcards:

          ?                  Matches any single character

          *                  Matches zero or more characters

          #                 Matches any single digit

          [ charlist ]     Matches any single character in the charlist

          [! charlist ]    Matches any single character not in the charlist

 

More extensive documentation of the Like operator can be found here.

 

-      Bill Horst, VB IDE Test

Leave a Comment
  • Please add 6 and 7 and type the answer here:
  • Post
  • PingBack from http://www.travel-hilarity.com/airline_travel/?p=4392

  • Is the same for C# or is exclusive for VB?

  • Hi, Bill: Here are some LINQ-to-SQL (L2S) scenarios I'd like to see covered in more detail:

    - Using L2S with stored procedures when the developer or users do not have table-level access;

    - Refreshing the L2S Designer when the database schema changes;

    - Canceling pending changes to a record;

    - Using L2S in a disconnected scenario, such as a Web application where it is not practical to maintain state in a long-lived DataContext.

    Appreciate any insight you care to provide, thanks!

  • The SQL example above is incorrect.  The proper syntax for a wildcard is a percent sign, not an asterisk.  

    Your example should be:  SELECT * FROM CustomerTable WHERE Phone LIKE '206%'

    However, the VB example is correct, because in VB the LIKE operator does use an asterisk for the wildcard.

  • @Speednet: I think you'll find it's all a matter of context.  If your database backend is Access, the example *is* correct.  If you're using something like SQL Server, Oracle, or MySQL; your syntax is correct.  Given how many VB examples are shown as written against an Access databse, I assumed this was the case for the original example.  However, considering how prevalent other database systems are, a comment refering to the normal syntax for those systems would have been recommended.

  • @Anthony: The Like operator is exclusive to VB. The specific example using LINQ in C# would be written something akin to*:

    from Contact in CustomerTable

    where Contact.Phone.StartsWith("206")

    select Contact;

    If you wanted to perform more complex pattern matching, I'd recommend using the RegEx capabilities.

    * I've never actually written LINQ in C#, but the examples in the VS2008 documentation indicate this syntax is appropriate.  Testing this out on your own system is highly recommended.  YMMV.  Void where prohibited.

  • @RonO: I guess I assumed that it was SQL server when you said "In SQL, the LIKE keyword..."

    Do people still do Access development, now that SQL Express is so prevalent?  (And free.)

    On the subject of the Visual Basic LIKE operator, it is indeed unique to VB (not available in C#).  

    LIKE is many times quicker than using a regular expression, so if you have a comparison that is going to be done multiple times, or if speed is urgent, then LIKE should be used instead of RegExp.  

    Here is an MSDN article comparing RegExp and LIKE:  http://msdn.microsoft.com/en-us/library/ms235204.aspx

    And here is a terrific little blog entry that deals with LIKE vs. RegExp vs. Char():  http://www.dotnet2themax.com/blogs/fbalena/PermaLink,guid,53da27bf-b980-4abc-a4e8-a452dc6ceb26.aspx

  • @Anthony, RonO:  It actually is possible to use the LikeOperator in C# if you're determined to do so.

    Lutz Roeder's .NET Reflector tool is very handy for figuring out how to move between VB and C# code.  It can be downloaded for free at http://www.aisto.com/roeder/dotnet/

    The C# equivalent for the code above is:

    from Contact in CustomerTable

    where LikeOperator.LikeString(Contact.Phone, "206*", CompareMethod.Binary)

    select Contact;

    This would also require adding a reference to Microsoft.VisualBasic (which I realize is a weird thing to do in C#) and adding the following two using statements:

    using Microsoft.VisualBasic;

    using Microsoft.VisualBasic.CompilerServices;

    Of course, why do all this when you can just use VB? ;)

  • ここでは、これまでの「 SQL から LINQ への変換 」の投稿をお読みになっていることを前提としています。 LINQ の投稿に関する具体的なリクエストはそれほど多くなかったので、ご提案があれば今からでも歓迎します。

  • Hi Bill, a great series post.

    I have a question in SQL i can UPDATE a table with a SELECT from other table(s) i.e.

    UPDATE tableA

         SET field1 = tableC.fieldX

      FROM tableA, tableC

    WHERE tableA.fieldcommon = tableC.fieldcommon

    Its possible en LINQ?

    Greetings,

    Wilmer

  • Gracias Brother me sirvio¡¡

    :)

Page 1 of 1 (11 items)