Add a where clause specific to an included Entity

Question

My data model has a number of tables with a common parent. I am using Entity Framework and have a navigation property from the child object back to the parent, and not the other way. The reason for this is there will be 100's of children of this common parent. The where criteria for every report will be the same, as an example:

 crudEngine.Read<ChildEntity>()
   .Include(parameters => parameters.ParentEntity)
   .Where(parameter => parameter.ParentEntity.LastUser.Contains(searchText) ||
         (isInt && SqlFunctions.DatePart("year", parameter.ParentEntity.CreationDate) == intSearchText) ||
         (isInt && SqlFunctions.DatePart("month", parameter.ParentEntity.CreationDate) == intSearchText) ||
         (isInt && SqlFunctions.DatePart("day", parameter.ParentEntity.CreationDate) == intSearchText) ||
         (isDate && SqlFunctions.DateAdd("dy", SqlFunctions.DateDiff("dy", "1900-01-01", parameter.ParentEntity.CreationDate), "1900-01-01") == dateSearchText) ||
         (isInt && SqlFunctions.DatePart("year", parameter.ParentEntity.LastModifiedDate) == intSearchText) ||
         (isInt && SqlFunctions.DatePart("month", parameter.ParentEntity.LastModifiedDate) == intSearchText) ||
         (isInt && SqlFunctions.DatePart("day", parameter.ParentEntity.LastModifiedDate) == intSearchText) ||
         (isDate && SqlFunctions.DateAdd("dy", SqlFunctions.DateDiff("dy", "1900-01-01", parameter.ParentEntity.LastModifiedDate), "1900-01-01") == dateSearchText) ||
          parameter.ParentEntity.CreationUser.Contains(searchText) ||
          parameter.ParentEntity.Description.Contains(searchText)...

This same criteria will be used for every child entity that references ParentEntity. If possible, I would like to somehow separate this where section into its own function like this:

private Predicate<ParentEntity> GetParentWhere(string searchText)
    {
        int intSearchText;
        bool isInt = int.TryParse(searchText, out intSearchText);

        DateTime dateSearchText;
        bool isDate = DateTime.TryParse(searchText, out dateSearchText);

        SubmissionStatus submissionStatus;
        bool isStatus = Enum.TryParse(searchText, true, out submissionStatus);

        return parent =>                
               (isInt && SqlFunctions.DatePart("year", parent.CreationDate) == intSearchText) ||
               (isInt && SqlFunctions.DatePart("month", parent.CreationDate) == intSearchText) ||
               (isInt && SqlFunctions.DatePart("day", parent.CreationDate) == intSearchText) ||
               (isDate && SqlFunctions.DateAdd("dy", SqlFunctions.DateDiff("dy", "1900-01-01", parent.CreationDate), "1900-01-01") == dateSearchText) ||
               (isInt && SqlFunctions.DatePart("year", parent.LastModifiedDate) == intSearchText) ||
               (isInt && SqlFunctions.DatePart("month", parent.LastModifiedDate) == intSearchText) ||
               (isInt && SqlFunctions.DatePart("day", parent.LastModifiedDate) == intSearchText) ||
               (isDate && SqlFunctions.DateAdd("dy", SqlFunctions.DateDiff("dy", "1900-01-01", parent.LastModifiedDate), "1900-01-01") == dateSearchText) ||
               parent.CreationUser.Contains(searchText) ||
               parent.Description.Contains(searchText) ||
               parent.LastUser.Contains(searchText) ||
               (isStatus && parent.Status == (int) submissionStatus);

    }

Then call this from my code, something like this:

        return crudEngine.Read<ChildEntity>().Include(parameters => parameters.ParentEntity)
                         .Where(GetParentWhere(searchText));

This doesn't work however because the Where expression is typed with the child type, not the parent. Any thoughts on how to accomplish this?

Thanks!

Answer 1

The simplest option would be for your child entities to implement an interface which exposes the ParentEntity property:

public interface IHaveParentEntity
{
   ParentEntity ParentEntity { get; }
}

private Expression<Func<TChildEntity, bool>> GetParentWhere<TChildEntity>(string searchText) 
   where TChildEntity : IHaveParentEntity
{
   int intSearchText;
   bool isInt = int.TryParse(searchText, out intSearchText);

   DateTime dateSearchText;
   bool isDate = DateTime.TryParse(searchText, out dateSearchText);

   SubmissionStatus submissionStatus;
   bool isStatus = Enum.TryParse(searchText, true, out submissionStatus);

   return child => 
      child.ParentEntity.CreationUser.Contains(searchText) ||
      ...
   ;
}

...

return crudEngine.Read<ChildEntity>()
    .Include(parameters => parameters.ParentEntity)
    .Where(GetParentWhere<ChildEntity>(searchText));

If you can't modify your child entities, you'll probably need to look at building an expression tree. Due to the complexity of your query, it would probably be easier to use expression re-writing rather than building the entire expression by hand:

private Expression<Func<TChildEntity, bool>> GetParentWhere<TChildEntity>(
   Expression<Func<TChildEntity, ParentEntity>> parentSelector, 
   string searchText)
{
   int intSearchText;
   bool isInt = int.TryParse(searchText, out intSearchText);

   DateTime dateSearchText;
   bool isDate = DateTime.TryParse(searchText, out dateSearchText);

   SubmissionStatus submissionStatus;
   bool isStatus = Enum.TryParse(searchText, true, out submissionStatus);

   Expression<Func<ParentEntity, bool>> predicate = parent => 
      parent.CreationUser.Contains(searchText) ||
      ...
   ;

   Expression body = ReplacementVisitor.Replace(
      predicate, 
      predicate.Parameters[0], 
      parentSelector.Body);

   return Expression.Lambda<Func<TChildEntity, bool>>(body, 
      parentSelector.Parameters[0]);
}

private sealed class ReplacementVisitor : ExpressionVisitor
{
  private IList<ParameterExpression> SourceParameters { get; set; }
  private Expression ToFind { get; set; }
  private Expression ToReplace { get; set; }

  public static Expression Replace(
      LambdaExpression source, 
      Expression toFind, 
      Expression toReplace)
  {
     var visitor = new ReplacementVisitor
     {
        SourceParameters = source.Parameters,
        ToFind = toFind,
        ToReplace = toReplace,
     };

     return visitor.Visit(source.Body);
  }

  private Expression ReplaceNode(Expression node)
  {
     return (node == ToFind) ? ToReplace : node;
  }

  protected override Expression VisitParameter(ParameterExpression node)
  {
     if (SourceParameters.Contains(node)) return ReplaceNode(node);
     return SourceParameters.FirstOrDefault(p => p.Name == node.Name) ?? node;
  }
}

...

return crudEngine.Read<ChildEntity>()
    .Include(parameters => parameters.ParentEntity)
    .Where(GetParentWhere<ChildEntity>(child => child.ParentEntity, searchText));