EF Query Object Pattern over Repository Example

Question

I have built a repository which only exposes IEnumerable based mostly on the examples in "Professional ASP.NET Design Patterns" by Scott Millett.

However because he mostly uses NHibernate his example of how to implement the Query Object Pattern, or rather how to best translate the query into something useful in EF, is a bit lacking.

I am looking for a good example of an implementation of the Query Object Pattern using EF4.

Edit: The main issues with the trivial example in the book are that CreateQueryAndObjectParameters only handles 2 cases, Equal & LesserThanOrEqual - not exactly a complete query solution. And it uses a string to build the criterion - which is a very crude way to handle this when compared to NHibernate. He says he'll provide the EF code for the chapter 10 example, but its not in the download. Hence looking for a real world example.

Answer 1

According to the book ( "Professional ASP.NET Design Patterns" by Scott Millett) you can use this codes [I have improved some lines]:

• Infrastructure layer :

• Criterion class : (each Query can contain some Criterion)

  public class Criterion
  {
  private string _propertyName;
  private object _value;
  private CriteriaOperator _criteriaOperator;
  
  public Criterion(string propertyName, object value,
                   CriteriaOperator criteriaOperator)
  {
      _propertyName = propertyName;
      _value = value;
      _criteriaOperator = criteriaOperator;
  }
  
  public string PropertyName
  {
      get { return _propertyName; }
  }
  
  public object Value
  {
      get { return _value; }
  }
  
  public CriteriaOperator criteriaOperator
  {
      get { return _criteriaOperator; }
  }
  
  public static Criterion Create<T>(Expression<Func<T, object>> expression, object value, CriteriaOperator criteriaOperator)
  {
      string propertyName = PropertyNameHelper.ResolvePropertyName<T>(expression);
      Criterion myCriterion = new Criterion(propertyName, value, criteriaOperator);
      return myCriterion;
  }
  }
  

• CriteriaOperator Class:

  public enum CriteriaOperator
  {
      Equal,
      LesserThanOrEqual,
      NotApplicable
      // TODO: Add the remainder of the criteria operators as required.
  }
  

• OrderByClause Class:

  public class OrderByClause
  {
      public string PropertyName { get; set; }
      public bool Desc { get; set; }
  }
  

• Query class:

  public class Query
  {
  private QueryName _name;
  private IList<Query> _subQueries = new List<Query>();
  private IList<Criterion> _criteria = new List<Criterion>();
  
  public Query()
      : this(QueryName.Dynamic, new List<Criterion>())
  { }
  
  public Query(QueryName name, IList<Criterion> criteria)
  { 
      _name = name;
      _criteria = criteria;
  }
  
  public QueryName Name
  {
      get { return _name; }
  }
  
  public bool IsNamedQuery()
  {
      return Name != QueryName.Dynamic;
  }
  
  public IEnumerable<Criterion> Criteria
  {
      get {return _criteria ;}
  }          
  
  public void Add(Criterion criterion)
  {
      if (!IsNamedQuery())
          _criteria.Add(criterion);
      else
          throw new ApplicationException("You cannot add additional criteria to named queries");
  }
  
  public IEnumerable<Query> SubQueries
  {
      get { return _subQueries; }
  }
  
  public void AddSubQuery(Query subQuery)
  {
      _subQueries.Add(subQuery);
  }
  
  public QueryOperator QueryOperator { get; set; }
  
  public OrderByClause OrderByProperty { get; set; }
  }
  

• PropertyNameHelper class:

  public static class PropertyNameHelper
  {
      public static string ResolvePropertyName<T>(
                         Expression<Func<T, object>> expression)
      {
          var expr = expression.Body as MemberExpression;
          if (expr == null)
          {
              var u = expression.Body as UnaryExpression;
              expr = u.Operand as MemberExpression;
          }
          return expr.ToString().Substring(expr.ToString().IndexOf(".") + 1);
      }
  }
  

  You need a QueryTranslator class too, to translate query (in Repository layer):

  public class TimelogQueryTranslator : QueryTranslator
  {
  public ObjectQuery<Timelog> Translate(Query query)
  {
      ObjectQuery<Timelog> timelogQuery;
  
      if (query.IsNamedQuery())
      {
          timelogQuery = FindEFQueryFor(query);
      }
      else
      {
          StringBuilder queryBuilder = new StringBuilder();
          IList<ObjectParameter> paraColl = new List<ObjectParameter>();
          CreateQueryAndObjectParameters(query, queryBuilder, paraColl);
  
          //[Edited By= Iman] :
          if (query.OrderByProperty == null)
          {
              timelogQuery = DataContextFactory.GetDataContext().Timelogs
              .Where(queryBuilder.ToString(), paraColl.ToArray());
          }
          else if (query.OrderByProperty.Desc == true)
          {
              timelogQuery = DataContextFactory.GetDataContext().Timelogs
              .Where(queryBuilder.ToString(), paraColl.ToArray()).OrderBy(String.Format("it.{0} desc", query.OrderByProperty.PropertyName));
          }
          else
          {
              timelogQuery = DataContextFactory.GetDataContext().Timelogs
                  .Where(queryBuilder.ToString(), paraColl.ToArray()).OrderBy(String.Format("it.{0} asc", query.OrderByProperty.PropertyName));
          }
          //[Edited By= Iman] .
  
      }
  
      return timelogQuery;
  }
  //-------------------------------------------------------------
      public abstract class QueryTranslator
  {
      public void CreateQueryAndObjectParameters(Query query, StringBuilder queryBuilder, IList<ObjectParameter> paraColl)
      {
          bool _isNotFirstFilterClause = false;
  
          foreach (Criterion criterion in query.Criteria)
          {
              if (_isNotFirstFilterClause)
              {
                  queryBuilder.Append(" AND "); //TODO: select depending on query.QueryOperator
              }
              switch (criterion.criteriaOperator)
              {
                  case CriteriaOperator.Equal:
                      queryBuilder.Append(String.Format("it.{0} = @{0}", criterion.PropertyName));
                      break;
                  case CriteriaOperator.LesserThanOrEqual:
                      queryBuilder.Append(String.Format("it.{0} <= @{0}", criterion.PropertyName));
                      break;
                  default:
                      throw new ApplicationException("No operator defined");
              }
  
              paraColl.Add(new ObjectParameter(criterion.PropertyName, criterion.Value));
  
              _isNotFirstFilterClause = true;
          }
      }
  }
  

Now in your service layer:

    public IEnumerable<Timelog> GetAllTimelogsFor(int iadcId, byte workShift)
    {
        Query query = new Query(QueryName.Dynamic,new List<Criterion>());
        query.Add(Criterion.Create<Timelog>(t=>t.IadcId, iadcId, CriteriaOperator.Equal));
        query.QueryOperator = QueryOperator.And;
        query.Add(Criterion.Create<Timelog>(t=>t.Shift, workShift, CriteriaOperator.Equal));
        query.OrderByProperty = new OrderByClause { PropertyName = "FromTime", Desc = false };

        IEnumerable<Timelog> timelogs = _timelogRepository.FindBy(query);

        return timelogs;
    }

Answer 2

You're probably sick of hearing from me at this point, but the natively supported IQueryable is the only Query Object Pattern implementation you need for EF.