我有一个LINQ查询，以获得一个整数列的最大值。所述列定义为在数据库NOT NULL。但是，如果没有行由查询返回的SQL中使用MAX聚合函数时，你会得到一个空的结果。

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.

下面是我使用对Northwind数据库来证明我做的一个样本LINQ查询。

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＃正确分析这个查询和maxValue分别有int类型的？此外，所生成的SQL是完美的：

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

现在的问题是，我怎么code此使用VB.NET，并得到相同的结果？如果我做了直译：

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

我得到一个编译错误。 TryCast只能与引用类型，不是值类型的工作。 TryCast和放大器; 作为在这方面是略有不同。 C＃做一些额外的工作与拳击办理值类型。所以，我的下一个解决方案是使用CTYPE代替TryCast：

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

这工作，但它生成的SQL语句：

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

虽然这是正确的，它不是很干净。当然，在这种特殊情况下的SQL Server将可能优化查询它是相同的C＃版本，我可以设想其中这可能不是这样的情况。有趣的是，在C＃版本，如果我用一个正常的投（即（INT？）p.ProductID）而不是使用为操作符，我得到了相同的SQL作为VB版。

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.

有谁知道是否有一种方法来生成在VB中最佳的SQL对于这种类型的查询？

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

推荐答案

简短的回答：你能

和那么长的回答：

这是我可以看到你可以做到这一点的唯一方法，就是创建一个包含TypeAs转换明确拉姆达。您可以使用下面的扩展方法来帮助你在这里：

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 将在结果中 TryCast（价值，T）任何 T ，其中整数？。

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

要使用它，你会说

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

这不是pretty的，但它的作品。 （这会产生如C＃不相同的查询。）只要你没有一个子查询你的罚款中调用SelectAsNullable。

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.

另一种方法可以是使用

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?

在C＃中的说法。它的报价有可能的LINQ to SQL仍然会产生一个子查询这一点。

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

进一步简化了LINQ查询

simplifying the LINQ query further

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

但正如我所说，这依赖于LINQ提供程序。

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