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 ;)

Comments

# re: Inside Recipe 7

Thursday, July 12, 2007 2:45 PM by Jonathan Aneja

For a fun exercise try it on SQL2000 (which doesn't support ROW_NUMBER(), the SQL looks even worse :)

# re: Inside Recipe 7

Monday, July 16, 2007 8:50 AM by Joshua Thomas

foreachdev.net/.../linq-paging

Why not generalize like this. Why not make it work with any IQueryable or in my example IEnumberable.

# re: Inside Recipe 7

Monday, July 16, 2007 9:19 AM by bill

Hi Joshua,

I doubt your example compiles, and this highlights the issue why you wouldn't want to use IEnumerable.. because you loose type information fidelity.  If it's a non generic interface you no longer ahve type information at desing time, instead it must be determined at runtime... aka late binding.