-->

Modify query generated by Linq dynamically

2019-08-23 12:20发布

问题:

I'm working on a project that is using Oracle and DynamicLinq, results that DynamicLinq is building a query that is really slow:

      SELECT /*FIELDS*/ FROM MYTABLE WHERE (("STRT_DT" >= TO_TIMESTAMP('2015-07-09 
00:00:00.000', 'YYYY-MM-DD HH24:MI:SS.FF')) AND 
        ("STRT_DT" <= TO_TIMESTAMP('2018-07-04 00:00:00.000', 'YYYY-MM-DD HH24:MI:SS.FF')))

The issue is that is generating the query using TO_TIMESTAMP('2018-07-04 00:00:00.000', 'YYYY-MM-DD HH24:MI:SS.FF') instead of TO_DATE:

STRT_DT >=  TO_DATE('09-JUL-14','DD-MON-YY') and 
STRT_DT <=  TO_DATE('04-JUL-18','DD-MON-YY')

When using TO_DATE instead of TO_TIMESPAN is way faster.. I've been debugging the code and found out where in the DynamicLinq.cs class is building the query:

 public static IQueryable Where(this IQueryable source, string predicate, params object[] values)
        {
   LambdaExpression lambda = DynamicExpression.ParseLambda(source.ElementType,
 typeof(bool), predicate, values);
var test = source.Provider.CreateQuery(
                Expression.Call(
                    typeof(Queryable), "Where",
                    new Type[] { source.ElementType },
                    source.Expression, Expression.Quote(lambda)));
return test;
}

The value of lambda variable is {Param_0 => (((STRT_DT >= Convert(7/14/2014 12:00:00 AM))) AndAlso (Param_0.STRT_DT <= Convert(7/18/2014 12:00:00 AM)))}

predicate parameter value is : "(VOYAGE_STRT_DT >= @3 and VOYAGE_STRT_DT <= @4)"

and values is an array with the DateTime values : {7/14/2014 12:00:00 AM} and {7/18/2014 12:00:00 AM} How can I modify that query? Any ideas? The query generated at the end is of type IQueryable, is it possible to modify the generated IQueryable query?