C#
- 1 ответ
- 0 вопросов
0
Вклад в тег
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 для получения данных в объект