将表达式>转换为表达式>>(Convert Expression> to Expression>>)

我有一个方法，它采用IOrderedQueryable和Expression <Func <T，V >>，它用作SQL数据库中的过滤器和页面记录。

var query = contexBills.AsNoTracking().Where(x => x.Complete==true).OrderBy(x => x.BillID); var reader = new BulkReader<Bill>(query, x => x.BillId, 10000);

批量阅读器在整个代码中被广泛使用，用于分页大量记录并批量处理它们，并按此定义

public BulkReader(IOrderedQueryable<T> queryable, Expression<Func<T, Object>> selector, int blockSize = 1000)

对于优化，分页从表中找到的最小值开始，并以最大值结束。由于使用Skip（）每月在数据库中有数百万条记录。当你达到表中数百万的数据时，Take（）方法会降低到每页13秒左右，然后处理整个月的数据可能需要很多小时。

假设集合中的记录很少被标记为完整== false，那么只选择记录> = [Page Start] AND <[Page End]可以非常快速地运行，每分钟大约有一百万条记录。在某些情况下，您处理的块数略小于传入的blockSize，但处理了最小值和最大值之间的所有记录。

随着月份的进展，最小值增加，因此假设0最小浪费很多SQL调用根本不返回任何内容。

所以我必须得到这些价值观

var min = queryable.Select(selector).DefaultIfEmpty(0).Min(); var max = queryable.Select(selector).DefaultIfEmpty(0).Max();

哪个产生看起来像这样的SQL

SELECT [GroupBy1].[A1] AS [C1] FROM ( SELECT MIN([Join1].[A1]) AS [A1] FROM ( SELECT CASE WHEN ([Project1].[C1] IS NULL) THEN 0 ELSE [Project1].[PrintSummaryID] END AS [A1] FROM ( SELECT 1 AS X ) AS [SingleRowTable1] LEFT OUTER JOIN (SELECT [Extent1].[PrintSummaryID] AS [PrintSummaryID], cast(1 as tinyint) AS [C1] FROM [dbo].[tblPrintSummary] AS [Extent1] ) AS [Project1] ON 1 = 1 ) AS [Join1] ) AS [GroupBy1] GO

如果我手动代码（作为测试）来进行这样的调用

var min = queryable.Min(x =>(int?)x.BillID) ?? 0; var max = queryable.Max(x =>(int?)x.BillID) ?? 0;

然后生成的SQL是

SELECT [GroupBy1].[A1] AS [C1] FROM ( SELECT MIN([Extent1].[PrintSummaryID]) AS [A1] FROM [dbo].[tblPrintSummary] AS [Extent1] ) AS [GroupBy1] GO

通过声明以下内容可以实现同样的目的：

Expression<Func<Bill, int?>> selector2 = x => x.BillID;

这样可以实现更简单，更快速的SQL执行，并使代码成为：

var min = queryable.Select(selector2).Min() ?? 0; var max = queryable.Select(selector2).Max() ?? 0;

采用明确重新定义所有选择器并为其提供替代的方法将意味着在整个应用程序中进行重大复制和重新编码

我怎样才能获取原始选择器并转换为可以为空的等效版本，而不是必须明确地编写每个选择器。

var selector2 = selector.NullableExpression();

我想将此作为表达式<Func <T，V >>的扩展方法NullableExpression（），以便我返回一个ExpressionExpression <Func <T，Nullable <V >>>这样我就可以在其他方面使用它我的代码中的位置。

我正在努力如何将V转换为Nullable或V？在表达中。

I have a method that takes an IOrderedQueryable and Expression<Func<T, V>> which uses as a filter and page records from a SQL database.

var query = contexBills.AsNoTracking().Where(x => x.Complete==true).OrderBy(x => x.BillID); var reader = new BulkReader<Bill>(query, x => x.BillId, 10000);

the bulk reader is used extensively throughout the code to page large volumes of records and process them in batches and is defined like this

public BulkReader(IOrderedQueryable<T> queryable, Expression<Func<T, Object>> selector, int blockSize = 1000)

For optimisation paging starts at the minimum value found in the table and ends at the maximum value. As there are many millions of records per month in the database using a Skip().Take() approach degrades to around 13 seconds a page when you get to to the high millions in the table and processing the whole months data can then take many hours.

Given that there a very few records in the set that are marked as complete == false then just selecting records >= [Page Start] AND < [Page End] works very quickly at about a million records a minute. In some cases you process slightly less than the blockSize passed in but all records between min and max get processed.

As the months progress the minimum value increases so assuming 0 as minimum wastes a lot of SQL calls that return nothing at all.

So what I have to get these values is

var min = queryable.Select(selector).DefaultIfEmpty(0).Min(); var max = queryable.Select(selector).DefaultIfEmpty(0).Max();

Which produces SQL that looks like this

If I hand code (as a test) to make calls like this

var min = queryable.Min(x =>(int?)x.BillID) ?? 0; var max = queryable.Max(x =>(int?)x.BillID) ?? 0;

then the SQL produced is

SELECT [GroupBy1].[A1] AS [C1] FROM ( SELECT MIN([Extent1].[PrintSummaryID]) AS [A1] FROM [dbo].[tblPrintSummary] AS [Extent1] ) AS [GroupBy1] GO

The same can be achieved by declaring the following instead:

Expression<Func<Bill, int?>> selector2 = x => x.BillID;

Which gives the benefit of the simpler and faster SQL execution and allows the code to become:

var min = queryable.Select(selector2).Min() ?? 0; var max = queryable.Select(selector2).Max() ?? 0;

Taking the approach of explicitly redefining all the selectors and providing overrides for them would mean significant duplication and recoding across the entire application

How could I take a the original selector and do a conversion to the nullable version equivalent generically rather then having to explicitly code each one.

var selector2 = selector.NullableExpression();

I'd like to to this as an extension method NullableExpression() on Expression<Func<T, V>> so that I return a ExpressionExpression<Func<T, Nullable<V>>> and that way I could use it in other locations throughout my code to.

I'm struggling with how I can convert the V to a Nullable or V? in an expression.

最满意答案

很简单，真的。诀窍是使用源表达式的主体，同时重用其参数。

public static Expression<Func<T, V?>> ToNullableExpression<T, V> (this Expression<Func<T, V>> source) where V : struct { if(source == null) throw new ArgumentNullException("source"); var body = Expression.Convert(source.Body, typeof(V?)); var parameters = source.Parameters; return Expression.Lambda<Func<T, V?>>(body, parameters); }

Quite simple, really. The trick is to play with the body of the source expression, while reusing its parameters.

更多推荐