Inside Recipe 7
Posted
Fri, Jul 13 2007 6:26
by
bill
The VB team has been publishing some LINQ recipes lately. If you scour their comments you'll probably notice a few suggestions/corrections from me. The latest sample I think is worth further discussion so I've decided to blog it here.
The sample code they show is a function to select a "page" of data:
Public Function GetProductsPage(ByVal db As NorthwindDataContext, _
ByVal pageNum As Integer, _
ByVal pageSize As Integer)
Return From p In db.Products _
Select p.ProductID, p.ProductName, p.UnitPrice _
Skip (pageNum - 1) * pageSize _
Take pageSize
End Function
The first thing you should notice is this function has no return type declared. That's right it requires Strict Off. I advise you do NOT do this.
If you decide you really do want to factor this query into a separate function, then for fidelity you should consider creating a type to hold the ProductID, ProductName and UnitPrice fields. Without this real type, the anonymous type means you can't pass the type information out of the function, so your only choices for the function return type are As Object, As IEnumerable or As IQueryable. So any code that tried to work with the actual fields would have to do so late bound. Admittedly in the VB teams example this is not an issue because the data binding is late bound via reflection, but be aware this is a major limitation of anonymous types... if used outside of a function it will require late binding.
As to the query itself, it's quite interesting to see the generated SQL. TO do this, you just need to define a stream for the Data Context's log to be written to, e.g :
Dim db As New NorthwindDataContext
db.Log = Console.Out
That will automatically print to the console the SQL when it is executed. Alternatively you can use a stream writer and then write the stream out later:
Dim db As New NorthWindDataContext
Dim stream As New IO.StringWriter()
db.Log = stream
and later print that stream out, such as Debug.Print(stream.ToString())
And this is the SQL generated from a Skip 20 Take 10
SELECT TOP 10 [t1].[ProductID], [t1].[ProductName], [t1].[UnitPrice]
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY [t0].[ProductID], [t0].[ProductName], [t0].[UnitPrice]) AS [ROW_NUMBER], [t0].[ProductID], [t0].[ProductName], [t0].[UnitPrice]
FROM [dbo].[Products] AS [t0]
) AS [t1]
WHERE [t1].[ROW_NUMBER] > @p0
-- @p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) [20]
Somehow the LINQ looks a LOT nicer to me ;)