Querying LINQ to SQL: Basics - Implementing LIKE and IS NULL operators using LINQ
(Page 6 of 6 )
“LIKE” is another important operator in SQL that filters rows on string patterns. The LINQ syntax of VB.NET 2008 has great flexibility in terms of using the LIKE operator. If you are familiar with SQL LIKE, you should easily understand the following examples:
Dim q = From p In db.Customers _
Where p.CompanyName Like "A*" _
Select p.CustomerID, p.ContactName, p.CompanyName
Dim q = From p In db.Customers _
Where p.CompanyName Like "?A*" _
Select p.CustomerID, p.ContactName, p.CompanyName
Dim q = From p In db.Customers _
Where Not (p.CompanyName Like "?A*") _
Select p.CustomerID, p.ContactName, p.CompanyName
C# does not directly support the LIKE operator in LINQ. Instead, we have to work as follows:
var q = from p in db.Customers
where System.Data.Linq.SqlClient.SqlMethods.Like(p.CompanyName, "_A%")
select new { p.CustomerID, p.ContactName, p.CompanyName };
Make sure that SqlMethods is not compulsory and just for flexibility. If you can achieve the same thing using built-in .NET Framework functions (like string functions), you can also use them in the conditional expressions.
Now, let us see how we can work with NULLs using LINQ. Consider the following VB.NET statement:
Dim q = From p In db.Customers _
Where p.Region Is Nothing _
Select p.CustomerID, p.ContactName, p.CompanyName, p.Region
The above returns the customer details of the customers whose region is NULL. We can negate the above statement as follows:
Dim q = From p In db.Customers _
Where p.Region IsNot Nothing _
Select p.CustomerID, p.ContactName, p.CompanyName, p.Region
The same can be written in C# as follows:
var q = from p in db.Customers
where p.Region == null
select new { p.CustomerID, p.ContactName, p.Region };
var q = from p in db.Customers
where p.Region != null
select new { p.CustomerID, p.ContactName, p.Region };
We will go further in-depth in my upcoming articles. I hope you enjoyed this article and any suggestions, bugs, errors, enhancements etc. are highly appreciated at http://jagchat.spaces.live.com.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
