Entity Framework Query Translator

sharks · January 24th, 2012, 05:35 PM

The book is really great, especially chapter 7 helped me organize my applications a lot. But since I'm using Entity Framework as my OM I'm struggling with the QueryTranslator class introduced in chapter 7. What I'd really like to see is a QueryTranslator class for Entity Framework for the case study. The class in chapter 7 works only with one criteria and subqueries are not handled either. I came up with something like a list of strings and then folding the operator into it. But I'm not really confident enough in C# to say that is a good solution. Also, if I want to have two predicates for the same property, I get some troubles with the ObjectParameter name. Let's say I want to find all product from category a and from subcategory b, so I will get a query similar to p.CategoryId = @CategoryId or p.CategoryId = @CategoryId. In this case I obviously get the error that the name for the ObjectParameter is not unique. Can anyone help or give me a hint?!

sharks · January 25th, 2012, 05:03 AM

Here is my code:

Code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Objects;

using Infrastructure.Querying;

namespace Repositories.EF.QueryTranslators
{
    public class QueryTranslator
    {
        private int ParameterCounter = 1;

        public string CurrentParameterNr { get { return "p" + ParameterCounter++.ToString(); } }

        public void CreateQueryAndObjectParameters(Query query, StringBuilder queryBuilder, IList<ObjectParameter> paraColl)
        {
            IList<string> criteriaStringList = new List<string>();

            if (query.Criteria != null)
            {
                foreach (Criterion criterion in query.Criteria)
                {
                    string currentParameterNr = CurrentParameterNr;
                    switch (criterion.criteriaOperator)
                    {
                        case CriteriaOperator.Equal:
                            criteriaStringList.Add(String.Format("it.{0} = @{1}", criterion.PropertyName, currentParameterNr));
                            break;

                        case CriteriaOperator.LessThanOrEqual:
                            criteriaStringList.Add(String.Format("it.{0} <= @{1}", criterion.PropertyName, currentParameterNr));
                            break;

                        case CriteriaOperator.LessThan:
                            criteriaStringList.Add(String.Format("it.{0} < @{1}", criterion.PropertyName, currentParameterNr));
                            break;

                        case CriteriaOperator.HigherThanOrEqual:
                            criteriaStringList.Add(String.Format("it.{0} >= @{1}", criterion.PropertyName, currentParameterNr));
                            break;

                        case CriteriaOperator.HigherThan:
                            criteriaStringList.Add(String.Format("it.{0} > @{1}", criterion.PropertyName, currentParameterNr));
                            break;

                        default:
                            throw new ApplicationException("No operator defined");
                    }

                    paraColl.Add(new ObjectParameter(currentParameterNr, criterion.Value));
                }

                string queryOperator;
                if (query.QueryOperator == QueryOperator.And) queryOperator = " and ";
                else queryOperator = " or ";

                queryBuilder.Append(String.Join(queryOperator, criteriaStringList));

                foreach (Query subQuery in query.SubQueries)
                {
                    queryBuilder.Append(queryOperator);
                    queryBuilder.Append(" (");
                    CreateQueryAndObjectParameters(subQuery, queryBuilder, paraColl);
                    queryBuilder.Append(") ");
                }
            }
        }
    }
}

Obviously not the best solution, but it works. Since I'm not satisfied with a solution that just works I would be glad if anyone could help me find a more elegant way ;-)

CoolKiwiBloke · August 14th, 2012, 07:45 AM

I've also hit this problem, from briefly looking at your code it seems a reasonable extension of the solution Scott offers.

However I recon it must be possible to write a query object that revolves around Lambda expressions, which can then be used directly by Linq-to-Entities and save all that pain.

I'm thinking along the lines of Query<T> so you'd create var query = new Query<Product>() for example, then you'd add a criteria to to along the lines of query.Add(new Criteria(m => m.ProductType == "Books")). Then in the query translator you'd extract these criteria and add them as .Where(Critera[0]).Where(Criteria[1]).

I don't know how practical it is yet as I am fairly new to all the Lambda stuff, but it will be my preferred way forward if I can get it to work.