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

Comments

# Airline Travel » Converting SQL to LINQ, Part 10: Like (Bill Horst) said on May 7, 2008 9:06 PM:

PingBack from http://www.travel-hilarity.com/airline_travel/?p=4392

# Anthony said on May 7, 2008 9:30 PM:

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

# Phil Weber said on May 8, 2008 11:57 AM:

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!

# Speednet said on May 8, 2008 12:02 PM:

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.

# RonO said on May 8, 2008 1:46 PM:

@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.

# RonO said on May 8, 2008 2:12 PM:

@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.

# Speednet said on May 8, 2008 7:39 PM:

@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

# VBTeam said on May 15, 2008 8:50 PM:

@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? ;)

Leave a Comment

Name (required) 

Your URL (optional)

Comments (required) 

Remember Me?