Wednesday, May 30, 2007

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: , , ,