Convert the expression <Func <T, V >> to the expression <Func <T, Nullable <V> >>

Question

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

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

the mass reader is widely used throughout the code to print large volumes of records and process them in batches and is defined as this

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

For optimization, the search call starts with the minimum value found in the table and ends with the maximum value. Since there are many millions of records per month in the database using the Skip () method, the Take () method reduces to about 13 seconds of the page, when you hit the high millions in the table and process the data for the whole month, then it may take many hours.

Given that there are very few records in the set that are marked as complete == false, just select the records> = [Page Start] AND <[End of page] runs very fast at about a million records per minute. In some cases, you process a little less than the passed blockSize, but all entries between min and max are processed.

As progress in months increases, the minimum value increases, so long as 0 at least takes a lot of SQL calls that return nothing.

So I have to get these values

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

What creates SQL that looks like this:

 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 

If I pass in 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 created by SQL

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

The same thing can be done by declaring instead the following:

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

This gives the advantage of 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 an approach to explicitly overriding all selectors and providing them with overrides would mean significant duplication and transcoding throughout the application

How can I take the original selector and do the conversion to the equivalent version with zero equivalent in general, and then explicitly program each of them.

 var selector2 = selector.NullableExpression(); 

I would like it to be like an extension method of NullableExpression () in Expression <Func <T, V →, so that I return ExpressionExpression <Func <T, Nullable <V →>, and so I could use it elsewhere all over my code.

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

Answer 1

Pretty simple, actually. The trick is to play with the body of the original expression, reusing its parameters.

 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); }