I've got a query passed to my service serialised into a set of classes. this object defines conditions in a tree like structure to support AND/ORing data to an infinite depth. I'm then using LinqToSQL to convert this class into a SQL query however my conditions (defined using PredicateBuilder) are ignored!
The PredicateBuilder seems like an obvious solution, my recursive functions build off Expression<Func<Error,bool>> instead of IQueryable<Error> to support this, I iterate over the tree recursively and append AND/OR conditions appropriately.
I call the recursive filter as follows, when debugging I can see the recursive function returning filters correctly - my issue is that these conditions are being ignored and don't surface in the output SQL (please see below) can anyone suggest why this might be?
Please let me know if any additional information is needed or if you believe this approach should work.
if ( hasConditions )
{
results.Where( RecursiveHandleFilterExpression( query.Criteria ) );
}
This is the function that appends the predicates
private Expression<Func<Error, bool>> RecursiveHandleFilterExpression( FilterExpression filterExpression )
{
// if anding, start with true Ors start with false
Expression<Func<Error, bool>> predicate;
if ( filterExpression.FilterOperator == LogicalOperator.And )
{
predicate = PredicateBuilder.True<Error>();
}
else
{
predicate = PredicateBuilder.False<Error>();
}
// apply conditions
foreach ( ConditionExpression condition in filterExpression.Conditions )
{
if ( filterExpression.FilterOperator == LogicalOperator.And )
{
predicate.And( ApplyCondition( condition ) );
}
else
{
predicate.Or( ApplyCondition( condition ) );
}
}
// apply child filters
foreach ( FilterExpression expression in filterExpression.Filters )
{
if ( filterExpression.FilterOperator == LogicalOperator.And )
{
predicate.And( RecursiveHandleFilterExpression( expression ) );
}
else
{
predicate.Or( RecursiveHandleFilterExpression( expression ) );
}
}
return predicate;
}
Generated SQL, obtained through DataContext.Log property, missing the 2 queries passed for the LoggedOn column
SELECT [t2].[ErrorId], [t2].[OrganisationId], [t2].[Severity], [t2].[Source], [t2].[ExceptionMessage], [t2].[InnerExceptionMessage], [t2].[Details], [t2].[LoggedOn]
FROM (
SELECT [t1].[ErrorId], [t1].[OrganisationId], [t1].[Severity], [t1].[Source], [t1].[ExceptionMessage], [t1].[InnerExceptionMessage], [t1].[Details], [t1].[LoggedOn], [t1].[ROW_NUMBER]
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY [t0].[ErrorId], [t0].[OrganisationId], [t0].[Severity], [t0].[Source], [t0].[ExceptionMessage], [t0].[InnerExceptionMessage], [t0].[Details], [t0].[LoggedOn]) AS [ROW_NUMBER], [t0].[ErrorId], [t0].[OrganisationId], [t0].[Severity], [t0].[Source], [t0].[ExceptionMessage], [t0].[InnerExceptionMessage], [t0].[Details], [t0].[LoggedOn]
FROM [dbo].[Errors] AS [t0]
WHERE [t0].[OrganisationId] = @p0
) AS [t1]
WHERE [t1].[ROW_NUMBER] BETWEEN @p1 + 1 AND @p1 + @p2
) AS [t2]
ORDER BY [t2].[ROW_NUMBER]
-- @p0: Input UniqueIdentifier (Size = -1; Prec = 0; Scale = 0) [f311d7f3-3755-e411-940e-00155d0c0c4b]
-- @p1: Input Int (Size = -1; Prec = 0; Scale = 0) [0]
-- @p2: Input Int (Size = -1; Prec = 0; Scale = 0) [51]
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 4.0.30319.17929
