Hi,
I am having issues when doing queries with joined predicates. I am not sure if it's because lightspeed does not support how we've formed the expressions, or that I'm not using it correctly.
Help!
Thank you very much.
My code snippets are as below:
The query:
Repository.Find<Scholarship>(ScholarshipPredicates.FirstNameMatches("firstname")
.And(ScholarshipPredicates.LastNameMatches("lastname")));
This query will only be fine if the firstname and lastname are not empty.
When i call this query (firstname is empty), i get a sqlerror - An expression of non-boolean type specified in a context where a condition is expected, near 'AND' [generated sql is pasted somewhere below]
When i call it with an empty last name, i get a NotImplementedException - The method or operation is not implemented.
public static class ScholarshipPredicates
{
public static Expression<Func<Scholarship, bool>> FirstNameMatches(string value)
{
Expression<Func<Scholarship, bool>> predicate = ExpressionExtensions.True<Scholarship>();
if (!string.IsNullOrEmpty(value))
predicate = predicate.And(scholarship =>
scholarship.Person.FirstName.StartsWith(value,
StringComparison.
CurrentCultureIgnoreCase));
return predicate;
}
public static Expression<Func<Scholarship, bool>> LastNameMatches(string value)
{
Expression<Func<Scholarship, bool>> predicate = ExpressionExtensions.True<Scholarship>();
if (!string.IsNullOrEmpty(value))
predicate = predicate.And(scholarship =>
scholarship.Person.LastName.StartsWith(value,
StringComparison.
CurrentCultureIgnoreCase));
return predicate;
}
}
Extensions for the expression:
public static Expression<Func<T, bool>> True<T>()
{
return f => true;
}
public static Expression<Func<T, bool>> False<T>()
{
return f => false;
}
public static Expression<Func<T, bool>> Or<T>(this Expression<Func<T, bool>> expr1,
Expression<Func<T, bool>> expr2)
{
InvocationExpression invokedExpr = Expression.Invoke(expr2, expr1.Parameters.Cast<Expression>());
return Expression.Lambda<Func<T, bool>>
(Expression.OrElse(expr1.Body, invokedExpr), expr1.Parameters).Expand();
}
public static Expression<Func<T, bool>> And<T>(this Expression<Func<T, bool>> expr1,
Expression<Func<T, bool>> expr2)
{
InvocationExpression invokedExpr = Expression.Invoke(expr2, expr1.Parameters.Cast<Expression>());
return Expression.Lambda<Func<T, bool>>
(Expression.AndAlso(expr1.Body, invokedExpr), expr1.Parameters).Expand();
}
Generated sql:
SELECT
[Scholarship].[ScholarshipId] AS [Scholarship.ScholarshipId],
[Scholarship].[PersonId] AS [Scholarship.PersonId],
[Scholarship].[ScholarshipPeriodId] AS [Scholarship.ScholarshipPeriodId],
[Scholarship].[ScholarshipRegionId] AS [Scholarship.ScholarshipRegionId],
[Scholarship].[ScholarshipTypeId] AS [Scholarship.ScholarshipTypeId],
[Scholarship].[ScholarshipWorkflowStatusId] AS [Scholarship.ScholarshipWorkflowStatusId]
FROM
[Scholarship].[Scholarship]
INNER JOIN
[Community].[Person]
ON
[Scholarship].[PersonId] = [Person].[PersonId]
WHERE
(True AND [Person].[LastName] LIKE 'phu%' ESCAPE '\')