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?