A group blog from members of the VB team
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:
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:
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
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
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:
where LikeOperator.LikeString(Contact.Phone, "206*", CompareMethod.Binary)
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:
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.
SET field1 = tableC.fieldX
FROM tableA, tableC
WHERE tableA.fieldcommon = tableC.fieldcommon
Its possible en LINQ?