Как реализовать Fluent конструктор SQL запросов?

Question

[Михаил Иванов]

Мне нужно написать Fluent конструктор запросов. Я решил сделать интерфейсы для конструкторов каждой части SQL запроса (FROM, WHERE и т.д) и для каждого типа запроса (SELECT, INSERT и т.д).
Сейчас мой код выглядит следующим образом:

// BUILDERS

public interface IBuilder
{

}

public interface IConditionBuilder<out TThis> : IBuilder
    where TThis : IQuery
{
    TThis Or();
    TThis Not();
}

public interface IFromBuilder<out TThis> : IBuilder
    where TThis : IQuery
{
    TThis From(string table);
    TThis FromRaw(string expression);
}

public interface IGroupBuilder<out TThis> : IBuilder
    where TThis: IQuery
{
    TThis GroupBy(params string[] fields);
    TThis GroupByRaw(string expression);
}

public interface IHavingBuilder<out TThis> : IConditionBuilder<TThis>
    where TThis : IQuery
{
    TThis Having<T>(string field, string operation, T value);
    TThis HavingRaw(string expression);
}

public interface IJoinBuilder<TThis> : IBuilder
    where TThis : IQuery
{
    TThis Join(string table, string first, string second);
    TThis Join(string table, string first, string second, string op);
    TThis Join(string table, string first, string second, string op, string type);
    TThis Join(string table, Func<IOnBuilder<TThis>, IOnBuilder<TThis>> on); // PROBLEM HERE
    TThis Join(string table, Func<IOnBuilder<TThis>, IOnBuilder<TThis>> on, string type); // PROBLEM HERE
}

public interface IJoinBuilder<TThis, in TQuery> : IJoinBuilder<TThis>
    where TThis : IQuery
    where TQuery : ISelectQuery
{
    TThis Join(TQuery query, string first, string second);
    TThis Join(TQuery query, string first, string second, string op);
    TThis Join(TQuery query, string first, string second, string op, string type);
    TThis Join(TQuery query, Func<IOnBuilder<TThis>, IOnBuilder<TThis>> on); // PROBLEM HERE
    TThis Join(TQuery query, Func<IOnBuilder<TThis>, IOnBuilder<TThis>> on, string type); // PROBLEM HERE
}

public interface IOnBuilder<out TThis> : IConditionBuilder<TThis>
    where TThis : IQuery
{
    TThis On(string first, string second);
    TThis On(string first, string second, string op);
    TThis OnRaw(string expression);
}

public interface IOrderBuilder<out TThis> : IBuilder
    where TThis : IQuery
{
    TThis OrderBy(params string[] fields);
    TThis OrderByDesc(params string[] fields);
    TThis OrderByRaw(string expression);
}

public interface IReturningBuilder<out TThis> : IBuilder
    where TThis : IQuery
{
    TThis Returning(params string[] fields);
}

public interface ISelectBuilder<out TThis> : IBuilder
{
    TThis Select(params string[] columns);
    TThis Select(params object[] columns);
    TThis SelectRaw(string expression);
}

public interface ISelectBuilder<out TThis, TQuery> : ISelectBuilder<TThis>
    where TThis: IQuery
    where TQuery: ISelectQuery
{
    TThis Select(TQuery query, string alias);
    TThis Select(Func<TQuery, TQuery> query, string alias);
}

public interface IWhereBuilder<out TThis> : IConditionBuilder<TThis>
    where TThis : IQuery
{
    TThis Where(string field, object value);
    TThis Where(string field, string op, object value);
    TThis WhereBetween<T>(string field, T lower, T higher);
    TThis WhereNull(string field);
    TThis WhereIn<T>(string field, IEnumerable<T> values);
    TThis WhereColumns(string fieldOne, string op, string fieldTwo);
    TThis WhereContains(string field, string value);
    TThis WhereContains(string field, string value, bool isCaseSensetive);
    TThis WhereStarts(string field, string value);
    TThis WhereStarts(string field, string value, bool isCaseSensetive);
    TThis WhereEnds(string field, string value);
    TThis WhereEnds(string field, string value, bool isCaseSensetive);
    TThis WhereRaw(string expression);
}

public interface IWhereBuilder<out TThis, TQuery> : IWhereBuilder<TThis>
    where TThis : IQuery
    where TQuery : ISelectQuery
{
    TThis Where(IWhereBuilder<out TThis, TQuery> where);
    TThis Where(Func<TQuery, TQuery> where); // PROBLEM HERE
    TThis WhereIn(string field, TQuery query);
    TThis WhereIn(string field, Func<TQuery, TQuery> query);
    TThis WhereExists(TQuery query);
    TThis WhereExists(Func<TQuery, TQuery> query);
}

// QUERIES

public interface IQuery
{
    string ToSql();
}

public interface ISelectQuery : IQuery, 
    ISelectBuilder<ISelectQuery>,
    IFromBuilder<ISelectQuery, ISelectQuery>,
    IWhereBuilder<ISelectQuery, ISelectQuery>,
    IJoinBuilder<ISelectQuery>
{
    ISelectQuery Distinct();
    ISelectQuery Take(long limit);
    ISelectQuery Skip(long offset);
    ISelectQuery Union(ISelectQuery query);
    ISelectQuery Union(Func<ISelectQuery, ISelectQuery> query);
}

У меня проблема с условиями. На данный момент я могу написать что-то вроде этого:

query.Where("Id", 10).Or().WhereBetween("Id", 20, 25);

Мне хотелось бы иметь возможность также писать и что-то вроде этого:

query.Where(w=>w.Where("id", 11).Or().Where("id", 15));
query.Join("table", on => on.On("Id","=","IdTwo").Or().On("Id", "=", "IdThree");

Вопрос: как это реализовать?

Comments

[Станислав Макаров]

А в чём конкретно сейчас проблема? Вижу вы отдаёте функутор вторым параметром в Join, это не работает?

Да, в первом случае лучше

query.Where(w => w.Equal("id", 11).Or().Equal("id", 15));

Answer 1

[Виктор]

могу предложить следующую реализацию:

DynamicQuery

public sealed class DynamicQuery
    {
        public static QueryResult GetDynamicQuery<T>(string tableName, Expression<Func<T, bool>> expression)
        {
            var queryProperties = new List<QueryParameter>();
            var body = (BinaryExpression)expression.Body;
            IDictionary<string, Object> expando = new ExpandoObject();
            var builder = new StringBuilder();

            WalkTree(body, ExpressionType.Default, ref queryProperties);

            // как вариант можно передавать набор полей или 
            // считывать их из атрибутов <T> передаваемого класса
            builder.Append("SELECT * FROM ");
            builder.Append(tableName);
            builder.Append(" WHERE ");

            for (int i = 0; i < queryProperties.Count(); i++)
            {
                QueryParameter item = queryProperties[i];

                if (!string.IsNullOrEmpty(item.LinkingOperator) && i > 0)
                {
                    builder.Append(string.Format("{0} {1} {2} @{1} ", item.LinkingOperator, item.PropertyName, item.QueryOperator));
                }
                else
                {
                    builder.Append(string.Format("{0} {1} @{0} ", item.PropertyName, item.QueryOperator));
                }
                expando[item.PropertyName] = item.PropertyValue;
            }
            return new QueryResult(builder.ToString().TrimEnd(), expando);
        }

        private static void WalkTree(BinaryExpression body, ExpressionType linkingType, ref List<QueryParameter> queryProperties)
        {
            if (body.NodeType != ExpressionType.AndAlso && body.NodeType != ExpressionType.OrElse)
            {
                string propertyName = GetPropertyName(body);
                dynamic propertyValue = body.Right;
                string opr = GetOperator(body.NodeType);
                string link = GetOperator(linkingType);

                queryProperties.Add(new QueryParameter(link, propertyName, propertyValue, opr));
            }
            else
            {
                WalkTree((BinaryExpression)body.Left, body.NodeType, ref queryProperties);
                WalkTree((BinaryExpression)body.Right, body.NodeType, ref queryProperties);
            }
        }

        private static string GetPropertyName(BinaryExpression body)
        {
            string propertyName = body.Left.ToString().Split(new char[] { '.' })[1];

            if (body.Left.NodeType == ExpressionType.Convert)
            {
                // hack to remove the trailing ) when convering.
                propertyName = propertyName.Replace(")", string.Empty);
            }
            return propertyName;
        }

        private static string GetOperator(ExpressionType type)
        {
            switch (type)
            {
                case ExpressionType.Equal:
                    return "=";
                case ExpressionType.NotEqual:
                    return "!=";
                case ExpressionType.LessThan:
                    return "<";
                case ExpressionType.GreaterThan:
                    return ">";
                case ExpressionType.AndAlso:
                case ExpressionType.And:
                    return "AND";
                case ExpressionType.Or:
                case ExpressionType.OrElse:
                    return "OR";
                case ExpressionType.Default:
                    return string.Empty;
                default:
                    throw new NotImplementedException();
            }
        }
    }

    internal class QueryParameter
    {
        public string LinkingOperator { get; set; }
        public string PropertyName { get; set; }
        public object PropertyValue { get; set; }
        public string QueryOperator { get; set; }

        internal QueryParameter(string linkingOperator, string propertyName, object propertyValue, string queryOperator)
        {
            LinkingOperator = linkingOperator;
            PropertyName = propertyName;
            PropertyValue = propertyValue;
            QueryOperator = queryOperator;
        }
    }

    public class QueryResult
    {
        private readonly Tuple<string, dynamic> _result;

        public string Sql => _result.Item1;

        public dynamic Param => _result.Item2;

        public QueryResult(string sql, dynamic param)
        {
            _result = new Tuple<string, dynamic>(sql, param);
        }
    }

пример использования:

public class TestEntity
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public string Caption { get; set; }
    }

public virtual IEnumerable<T> FindById(int UserId)
{
  IEnumerable<T> items;
  //возвращает QueryResult содержащий форматированный запрос вида:
  // SELECT * FROM testTable WHERE Id = @Id OR Name = @Name
  // и набор параметров 
  QueryResult dQuery = DynamicQuery.GetDynamicQuery<TestEntity>("testTable", t => t.Id == 23 || t.Name =="Test");
            using (IDbConnection cn = Connection)
            {
                if (cn.State != ConnectionState.Open) cn.Open();
                items = cn.Query<T>(dQuery.Sql, (object)dQuery.Param);
            }
  return items;
}

cn.Query<T> используется Dapper для получения данных в объект