Differences between VB TryCast and C# "as" operator when using LINQ

Question

I have a LINQ query to retrieve the maximum value of an integer column. The column is defined as NOT NULL in the database. However, when using the MAX aggregate function in SQL you will get a NULL result if no rows are returned by the query.

Here is a sample LINQ query I am using against the Northwind database to demonstrate what I am doing.

var maxValue = (from p in nw.Products 
                where p.ProductID < 0 
                select p.ProductID as int?).Max();

C# correctly parses this query and maxValue has a type of int?. Furthermore, the SQL that is generated is perfect:

SELECT MAX([t0].[ProductID]) AS [value]
FROM [Products] AS [t0]
WHERE [t0].[ProductID] < @p0

The question is, how do I code this using VB.NET and get identical results? If I do a straight translation:

dim maxValue = (from p in Products 
                where p.ProductID < 0 
                select TryCast(p.ProductID, integer?)).Max()

I get a compile error. TryCast will only work with reference types, not value types. TryCast & "as" are slightly different in this respect. C# does a little extra work with boxing to handle value types. So, my next solution is to use CType instead of TryCast:

dim maxValue = (from p in Products 
                where p.ProductID > 0 
                select CType(p.ProductID, integer?)).Max()

This works, but it generates the following SQL:

SELECT MAX([t1].[value]) AS [value]
FROM (
    SELECT [t0].[ProductID] AS [value], [t0].[ProductID]
    FROM [Products] AS [t0]
    ) AS [t1]
WHERE [t1].[ProductID] > @p0

While this is correct, it is not very clean. Granted, in this particular case SQL Server would probably optimize the query it to be the same as the C# version, I can envisage situations where this might not be the case. Interestingly, in the C# version, if I use a normal cast (i.e. (int?)p.ProductID) instead of using the "as" operator I get the same SQL as the VB version.

Does anyone know if there is a way to generate the optimal SQL in VB for this type of query?

Yes, this is LINQ to SQL. If I use DirectCast I get a compile error. Only CType works. — Mike Thompson
– Mike Thompson, Commented Aug 19, 2009 at 1:33

Ruben · Accepted Answer · 2009-08-21 18:55:10Z

Short answer: you can.

And then the long answer:

The only way that I can see that you can do this, is to create the lambda containing the TypeAs conversion explicitly. You can use the following extension methods to help you here:

<Extension()> _
Public Module TypeAsExtensions
    <Extension()> _
    Public Function SelectAs(Of TElement, TOriginalType, TTargetType)( _
        ByVal source As IQueryable(Of TElement), _
        ByVal selector As Expression(Of Func(Of TElement, TOriginalType))) _
        As IQueryable(Of TTargetType)

        Return Queryable.Select(source, _
            Expression.Lambda(Of Func(Of TElement, TTargetType))( _
                Expression.TypeAs(selector.Body, GetType(TTargetType)), _
                selector.Parameters(0)))
    End Function

    <Extension()> _
    Public Function SelectAsNullable(Of TElement, TType As Structure)( _
        ByVal source As IQueryable(Of TElement), _
        ByVal selector As Expression(Of Func(Of TElement, TType))) _
        As IQueryable(Of TType?)
        Return SelectAs(Of TElement, TType, TType?)(source, selector)
    End Function
End Module

SelectAs will in result in a TryCast(value, T) for any T, including Integer?.

To use this, you would say

Dim maxValue = Products _
               .Where(Function(p) p.ProductID < 0) _
               .SelectAsNullable(Function(p) p.ProductID) _
               .Max()

It ain't pretty, but it works. (This generates the same query as C# does.) As long as you don't call SelectAsNullable within a sub-query you're fine.

Another option could be to use

Dim maxValue = (From p In Products _
                Where p.ProductID < 0 
                Select p.ProductID) _
               .SelectAsNullable(Function(id) id) _
               .Max()

The problem with this is that you get a double select, i.e.,

from p in Products 
where p.ProductID < 0 
select p.ProductID 
select p.ProductID as int?

in C# parlance. It's quote possible LINQ to SQL still generate a subquery for this too.

Anyway, for this you can create an additional extension method

<Extension()> _
Public Function SelectAsNullable(Of TType As Structure)( _
    ByVal source As IQueryable(Of TType)) _
    As IQueryable(Of TType?)
    Return SelectAs(Of TType, TType, TType?)(source, Function(x) x)
End Function

simplifying the LINQ query further

Dim maxValue = (From p In Products _
                Where p.ProductID < 0 
                Select p.ProductID) _
               .SelectAsNullable() _
               .Max()

But as I said, this depends on the LINQ provider.

Rob · Accepted Answer · 2009-08-19 01:14:41Z

0

        Dim maxValue = ctype((From p In db.Products _
      Where p.ProductID > 0 _
      Select p.ProductID).Max(),Integer?)

answered Aug 19, 2009 at 1:14

Rob

1,3281 gold badge9 silver badges14 bronze badges

2 Comments

Mike Thompson Over a year ago

So simple! I thought I played around with doing an outer cast, but I didn't quite hit upon the right syntax.

Mike Thompson Over a year ago

Correction. Unfortunately, this does not work. It thinks the result is Integer, not Integer? When the query returns no rows I get an InvalidCast exception.

Rob · Accepted Answer · 2009-08-19 01:47:49Z

0

HOLY crap what a PITA

    Dim maxValue = (From p In db.Products _
      Where p.ProductID > 300 _ 
      Select new With {.id=CType(p.ProductID, Integer?)}).Max(Function(p) p.id)

There HAS to be a better way, right?

This has the desired Query plan and no error with null values, but can someone take a saw to it and clean it up?

answered Aug 19, 2009 at 1:47

Rob

1,3281 gold badge9 silver badges14 bronze badges

1 Comment

Mike Thompson Over a year ago

Unfortunately, this does not have the correct query plan (according to LINQPad). It retrieves all the rows and performs the Max aggregation in memory.

Joe Chung · Accepted Answer · 2009-08-19 02:09:16Z

0

C#

var maxValue = nw.Products
    .Where(p => p.ProductID < 0)
    .Select(p => p.ProductID)
    .DefaultIfEmpty(int.MinValue)
    .Max();

VB

Dim maxValue = nw.Products _
    .Where(Function(p) p.ProductID < 0) _
    .Select(Function(p) p.ProductID) _
    .DefaultIfEmpty(Integer.MinValue) _
    .Max()

answered Aug 19, 2009 at 2:09

Joe Chung

12.2k1 gold badge26 silver badges33 bronze badges

2 Comments

Mike Thompson Over a year ago

Both of these give me a "Unsupported overload used for query operator 'DefaultIfEmpty'" error. Is something missing?

Joe Chung Over a year ago

Ah sorry, it seems that LINQ to SQL does not support DefaultIfEmpty. This works fine in a LINQ to Objects query.

John Fisher · Accepted Answer · 2009-08-19 17:30:47Z

0

How about a function that returns Nullable? (Sorry if the syntax isn't quite right.)

Function GetNullable(Of T)(val as Object)
    If (val Is Nothing) Then 
        Return new Nullable(Of T)()
    Else
        Return new Nullable(Of T)(DirectCast(val, T))
    End If
End Function

dim maxValue = (from p in Products 
            where p.ProductID < 0 
            select GetNullable(Of Integer)(p.ProductID)).Max()

answered Aug 19, 2009 at 17:30

John Fisher

22.8k2 gold badges43 silver badges67 bronze badges

Comments

Andras Zoltan · Accepted Answer · 2009-08-22 22:16:15Z

0

why not build the equivalent of an isnull check into the query?

dim maxValue = (from p in Products
                 where IIf(p.ProductID=Null, 0, p.ProductID) < 0
                 select p.ProductID)).Max()

Sorry if this doesn't work - I'm not actually testing it at this end, just throwing spaghetti on the wall!

answered Aug 22, 2009 at 22:16

Andras Zoltan

42.4k13 gold badges109 silver badges160 bronze badges

Collectives™ on Stack Overflow

Differences between VB TryCast and C# "as" operator when using LINQ

6 Answers 6

Comments

2 Comments

1 Comment

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

2 Comments

1 Comment

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related