SqlMethods Stealth Class Enables LIKE and DATEDIFF for LINQ to SQL

Anders Hejlsberg took the wraps off LINQ to SQL's SqlMethods class hidden in the System.Linq.Data.SqlClient namespace with this ADO.NET Orcas forum post. Google and Windows Live searches for linq sqlmethods return only one unique hit. Yahoo! Search returns 3 hits, one of which is the result of a typo and the other two duplicate the primary Google hit with omitted results included.

Orcas Beta 1's online help documents the SqlMethods class and its Like and DateDiff methods but doesn't provide sample use cases.

The Like Method and T-SQL LIKE Operator

The SqlMethods class includes two overloads of the Like method that the Canonical Query Tree (CQT) sends as T-SQL directly as the LIKE operator:

Dim matchExpression As String
Dim pattern As String
Dim returnValue As Boolean

returnValue = SqlMethods.Like(matchExpression, pattern)

And

Dim matchExpression As String
Dim pattern As String
Dim escapeCharacter As Char
Dim returnValue As Boolean

returnValue = SqlMethods.Like(matchExpression, pattern, escapeCharacter)

The .NET String methods provide Contains(chars), StartsWith(chars), and EndsWith(chars), which the CQT translates to LIKE '%chars%', LIKE 'chars%', and LIKE '%chars' operators, respectively. Note that Contains(chars)isn't the same as the Contains(Of T) standard query operator that determines whether an ICollection(Of T) contains a specified value.

However Contains(chars) doesn't support multiple character sets separated by wildcards, such as LIKE '%abcd%efgh%', which are valid in T-SQL. Here's a LINQ to SQL VB query that uses the SqlMethods.Like method:

Dim Query = From c In dcNwind.Customers, o In c.Orders, _ 
            d In o.Order_Details _
Where SqlMethods.Like(c.CompanyName, "%fred%kist%") _
Order By o.OrderID Descending _
Select c.CustomerID, c.CompanyName, o.OrderID, _
   o.OrderDate, d.Product.ProductName, d.Quantity 

The preceding query sends the following parameterized T-SQL query to an updated version of the the Northwind sample database:

SELECT [t0].[CompanyName], [t0].[CustomerID], [t1].[OrderDate], [t1].[OrderID], [t3].[ProductName], [t2].[Quantity]
FROM [dbo].[Customers] AS [t0], [dbo].[Orders] AS [t1], [dbo].[Order Details] AS [t2], [dbo].[Products] AS [t3]
WHERE ([t3].[ProductID] = [t2].[ProductID]) AND ([t0].[CompanyName] LIKE @p0) AND ([t1].[CustomerID] = [t0].[CustomerID]) AND ([t2].[OrderID] = [t1].[OrderID])
ORDER BY [t1].[OrderID] DESC
-- @p0: Input NVarChar (Size = 11; Prec = 0; Scale = 0) NOT NULL [%fred%kist%]

 which returns the following rows (formatting added):

CustID	OrderID	OrderDate	Quan	ProductName
ALFKI	11011	04/09/2007	40	Escargots de Bourgogne
ALFKI	11011	04/09/2007	20	Flotemysost
ALFKI	10952	03/16/2007	16	Grandma's Boysenberry Spread
ALFKI	10952	03/16/2007	2	Rössle Sauerkraut
ALFKI	10835	01/15/2007	15	Raclette Courdavault
ALFKI	10835	01/15/2007	2	Original Frankfurter grüne Soße
ALFKI	10702	10/13/2006	6	Aniseed Syrup
ALFKI	10702	10/13/2006	15	Lakkalikööri
ALFKI	10692	10/03/2006	20	Vegie-spread
ALFKI	10643	08/25/2006	15	Rössle Sauerkraut
ALFKI	10643	08/25/2006	21	Chartreuse verte
ALFKI	10643	08/25/2006	2	Spegesild

The DateDiff Methods and T-SQL DATEDIFF Function

The SqlMethods class includes seven DateDiffInterval methods, where Interval is Year, Month, Day, Hour, Minute, Second, and Millisecond. (Surprisingly, Quarter and Week intervals are missing.) Each interval method has two overloads, one that accepts a pair of DateTime and one that accepts a pair of Nullable(Of DateTime) datatypes, as shown here:

Dim startDate As DateTime
Dim endDate As DateTime
Dim returnValue As Integer

returnValue = SqlMethods.DateDiffYear(startDate, endDate)

And

Dim startDate As Nullable(Of DateTime)
Dim endDate As Nullable(Of DateTime)
Dim returnValue As Nullable(Of Integer)

returnValue = SqlMethods.DateDiffYear(startDate, endDate)

The query to return rows for all products shipped in the past month to Brazil is:

Dim Query = From c In dcNwind.Customers, o In c.Orders, _ 
    d In o.Order_Details _
Where SqlMethods.DateDiffMonth(o.OrderDate.Value, Today) = 1 And _
    c.Country = "Brazil" _
Order By o.OrderID Descending _
Select c.CustomerID, c.CompanyName, o.OrderID, _
    o.OrderDate, d.Product.ProductName, d.Quantity

which sends the following T-SQL statement:

SELECT [t0].[CompanyName], [t0].[CustomerID], [t1].[OrderDate], [t1].[OrderID], [t3].[ProductName], [t2].[Quantity]
FROM [dbo].[Customers] AS [t0], [dbo].[Orders] AS [t1], [dbo].[Order Details] AS [t2], [dbo].[Products] AS [t3]
WHERE ([t3].[ProductID] = [t2].[ProductID]) AND (DATEDIFF(Month, [t1].[OrderDate], @p0) = @p1) AND ([t0].[Country] = @p2) AND ([t1].[CustomerID] = [t0].[CustomerID]) AND ([t2].[OrderID] = [t1].[OrderID])
ORDER BY [t1].[OrderID] DESC
-- @p0: Input DateTime (Size = 0; Prec = 0; Scale = 0) NOT NULL [5/30/2007 12:00:00 AM]
-- @p1: Input Int (Size = 0; Prec = 0; Scale = 0) NOT NULL [1]
-- @p2: Input NVarChar (Size = 6; Prec = 0; Scale = 0) NOT NULL [Brazil]

and returns the following rows:

CustID	OrderID	OrderDate	Quan	SkuName
RICAR	11059	04/29/2007	30	Konbu
RICAR	11059	04/29/2007	12	Alice Mutton
RICAR	11059	04/29/2007	35	Camembert Pierrot
HANAR	11052	04/27/2007	30	Ipoh Coffee
HANAR	11052	04/27/2007	10	Sirop d'érable
GOURL	11049	04/24/2007	10	Chang
GOURL	11049	04/24/2007	4	Queso Manchego La Pastora
COMMI	11042	04/22/2007	15	Gula Malacca
COMMI	11042	04/22/2007	4	Sirop d'érable
HANAR	11022	04/14/2007	35	Teatime Chocolate Biscuits
HANAR	11022	04/14/2007	30	Gudbrandsdalsost

There undoubtedly are additional T-SQL-specific operators and functions that would be useful if the ADO.NET team decides to expand the methods exposed by the SqlMethods class. Any suggestions?

Technorati tags: LINQ to SQL, LINQ, T-SQL, SqlMethods