[Source Code]-Differences between VB TryCast and C# "as" operator when using LINQ-LINQ,C#

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

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?

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

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

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.

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!