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

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

Airline Travel » Converting SQL to LINQ, Part 10: Like (Bill Horst) — Thu, 08 May 2008 04:06:35 GMT

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

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

Anthony — Thu, 08 May 2008 04:30:42 GMT

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

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

Phil Weber — Thu, 08 May 2008 18:57:23 GMT

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!

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

Speednet — Thu, 08 May 2008 19:02:08 GMT

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.

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

RonO — Thu, 08 May 2008 20:46:01 GMT

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

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

RonO — Thu, 08 May 2008 21:12:34 GMT

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

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

Speednet — Fri, 09 May 2008 02:39:21 GMT

@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

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

VBTeam — Fri, 16 May 2008 03:50:41 GMT

@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 への変換、パート 10: Like (Bill Horst)

The Visual Basic Team — Wed, 18 Jun 2008 10:26:26 GMT

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