Can I pass in T.Property? Also, ideas for improving this method?

Question

Or possibly there is a better way.

I am building a dynamic query builder for NHibernate, we don't want to put HQL directly into the application, we want it as ORM agnostic as possible. It looks like this currently:

  public override IEnumerable<T> SelectQuery(Dictionary<string, string> dictionary)
    {
        string t = Convert.ToString(typeof (T).Name);
        string criteria = string.Empty;
        foreach (KeyValuePair<string, string> item in dictionary)
        {
            if (criteria != string.Empty)
                  criteria += " and ";


            criteria += item.Key + " = '" + item.Value + "'"; 
        }

        string query = " from " + t;

        if (criteria != string.Empty)
            query += " where " + criteria;

        return FindByHql(query);
    }

ok, great, however.... there are two things in here that pose a problem:

1. This query only handles "and," my initial thought is to pass is to build a method to dynamically build the dictionary that takes the property name, value, and an operator "and" or "or" and builds the dictionary along with an array of operators. Does that sound like the right thing to do?

2. Ok, so, this works GREAT, however, when there is an integer it fails because of the single quotes. What I think would be the BEST way is have the dictionary accept <T.Property, string> and then reflect into T.Property to find the datatype and behave accordingly. Am I over complicating this?

Thank you.

Answer 1

I would suggest possibly creating a class that has all the properties you need:

Name,
Value,
Type,
JoinType (possibly an enum with Or / And)

then, have your method take a collection of these types as opposed to a Dictionary. This way, you can easily check if you need to do and / or, as well as check if you need quotes...

Answer 2

What about something like this.

Where you have an enum for the operation. Instead of passing a string for the dictionary you pass a type of QueryObject that has the type of the value and an operation for the value. You can see below.

public enum Operation
{
    And,
    Or
}

public class QueryObject
{
    public string Value { get; set; }
    public Type Type { get; set; }
    public Operation Operation { get; set; }
}

public override IEnumerable<T> SelectQuery(Dictionary<string, QueryObject> dictionary)
{
    string t = Convert.ToString(typeof(T).Name);
    string criteria = string.Empty;
    foreach (KeyValuePair<string, QueryObject> item in dictionary)
    {
        if (!string.IsNullOrEmpty(criteria))
        {
            switch (item.Value.Operation)
            {
                case Operation.And:
                    criteria += " and ";
                    break;
                case Operation.Or:
                    criteria += " or ";
                    break;
                default:
                    break;
            }
        }

        if (item.Value.Type == typeof(int))
        {
            criteria += item.Key + " = " + item.Value + " ";    
        }
        else
        {
            criteria += item.Key + " = '" + item.Value + "'";
        }
    }

    string query = " from " + t;

    if (criteria != string.Empty)
        query += " where " + criteria;

    return FindByHql(query);
}

Answer 3

My inital thought is that creating something like this isn't too sensible. You are writing code that generates HQL. Which in turn gets passed to nhibernate which generates SQL.

I would suggest taking a look at NHibernate criteria queries. Firstly as an easier way of dynamically building up an NHibernate query. But also to give you some sort of idea of how complex rolling your own dynamic query builder is likely to be.

That said. If I was doing it I would probably use NHibernate Criteria as a base for any sort of dynamic query builder. Theres no reason why it couldn't generate a query to be used by another ORM.

A more general solution to the problem is to abstract your data access so that if you want to switch ORM then you only need to change the code behind your abstraction. This is more work of course but I'm not convinced that keeping your data access code ORM independent is something that is particularly important.

Answer 4

Well an alternative to what you are doing is passing an Expression> instead of the dictionary and then parse the Linq expression for what you want. A small suggestion that I would make is to use a Criteria query instead of HQL. Of course parsing the Linq expression is probably far more complex than what you have here.

You may be able to process the dictionary and make great HQL, but it hurts my head thinking about doing it that way. Criteria queries seem designed for this sort of thing.

Answer 5

If you were to use Criteria API, there is already "Query by Example" (aka QBE) functionality that uses entity properties to generate queries.

Take a look at section 12.6:

https://www.hibernate.org/hib_docs/nhibernate/html/querycriteria.html