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?

Answer 1

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

Answer 2

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?

Answer 3

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

Answer 4

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

Answer 5

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.

Answer 6

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!