private static void WalkTree <T>(Expression <Func <T, bool> > exp, ref SQlQuery query)
{
var expression = exp.Body;
var body = expression as BinaryExpression;
if (body != null)
{
WalkTree <T>(body, ExpressionType.Default, ref query);
}
BinaryExpression binary = expression as BinaryExpression;
UnaryExpression unary = expression as UnaryExpression;
MethodCallExpression methodCall;
if (binary == null)
{
if (unary != null)
{
methodCall = unary.Operand as MethodCallExpression;
}
else
{
methodCall = expression as MethodCallExpression;
}
string strUnary = unary.ToString();
string[] arrUnary = unary.ToString().Split('(');
string condition = string.Empty;
GetCondition <T>(string.Empty, arrUnary, ref condition, ref query);
}
}
public static void AddWhereCondition(this SQlQuery query, string whereCondition)
{
if (!string.IsNullOrEmpty(whereCondition))
{
query.WhereCondition.Add(whereCondition);
}
}
/// <summary>
/// "Any" keyword is used without expression
/// Way to use:
/// Condition[Table].Any()
/// </summary>
public static bool Any <T>(IDbConnection db)
{
SQlQuery sql = new SQlQuery(EXTable.GetTableName <T>());
string newSql = @"SELECT CASE WHEN ( EXISTS ( " + sql.Query + " )) THEN cast(1 as bit) ELSE cast(0 as bit) END";
var result = db.Query <bool>(newSql).FirstOrDefault();
return(result);
}
/// <summary>
/// "Count" keyword is used without expression --
/// Way to use: [Table].Count()
/// </summary>
public static int Count <T>(IDbConnection db)
{
SQlQuery query = new SQlQuery(EXTable.GetTableName <T>());
string newSql = @"SELECT COUNT(1) FROM ( " + query.Query + " ) as Extend";
var result = db.Query <int>(newSql).FirstOrDefault();
return(result);
}
public static QueryResult <T> Get <T>(Expression <Func <T, bool> > expression)
{
var body = expression.Body as BinaryExpression;
SQlQuery query = new SQlQuery(EXTable.GetTableName <T>());
WalkTree <T>(expression, ref query);
return(new QueryResult <T>(query));
}
public static void AddJoinOperator(this SQlQuery query, List <string> joinOperator)
{
if (joinOperator != null && joinOperator.Any())
{
query.JoinContidion.AddRange(joinOperator);
}
query.JoinContidion = query.JoinContidion.Distinct().ToList();
}
/// <summary>
/// "Any" keyword is used with expression
/// Way to use:
/// Any[Table](x=> x.id = 10);
/// Find[Table](x=> x.id = 10).Any(x=>x.id = 10);
/// </summary>
public static bool Any <T>(IDbConnection db, Expression <Func <T, bool> > expression)
{
var body = expression.Body as BinaryExpression;
SQlQuery query = new SQlQuery(EXTable.GetTableName <T>());
WalkTree <T>(expression, ref query);
string newSql = @"SELECT CASE WHEN ( EXISTS ( " + query.Query + " )) THEN cast(1 as bit) ELSE cast(0 as bit) END";
var result = db.Query <bool>(newSql, (object)query.Param).FirstOrDefault();
return(result);
}
/// <summary>
/// "Count" keyword is used without expression --
/// Way to use: [Table].Count(expression)
/// </summary>
public static int Count <T>(Expression <Func <T, bool> > expression, IDbConnection db)
{
var body = expression.Body as BinaryExpression;
SQlQuery query = new SQlQuery(EXTable.GetTableName <T>());
WalkTree <T>(expression, ref query);
string newSql = @"SELECT COUNT(1) FROM ( " + query.Query + " ) as Extend";
var result = db.Query <int>(newSql, (object)query.Param).FirstOrDefault();
return(result);
}
private static SQlQuery BuildCondition <T>(Expression expression, BinaryExpression body, SQlQuery query)
{
BinaryExpression binary = expression as BinaryExpression;
UnaryExpression unary = expression as UnaryExpression;
MethodCallExpression methodCall;
if (binary == null)
{
if (unary != null)
{
//methodCall = unary.Operand as MethodCallExpression;
//string strUnary = methodCall.ToString();
//string[] arrUnary = methodCall.ToString().Split('(');
//string condition = string.Empty;
//GetCondition<T>(string.Empty, arrUnary, ref condition, ref query);
string exp = unary.ToString();
string strUnary = exp.ToString();
string[] arrUnary = exp.ToString().Split('(');
string condition = string.Empty;
GetCondition <T>(string.Empty, arrUnary, ref condition, ref query);
}
else
{
methodCall = expression as MethodCallExpression;
string strUnary = methodCall.ToString();
string[] arrUnary = methodCall.ToString().Split('(');
string condition = string.Empty;
GetCondition <T>(string.Empty, arrUnary, ref condition, ref query);
}
}
else
{
WalkTree <T>(binary, body.NodeType, ref query);
}
return(query);
}
private static void WalkTree <T>(BinaryExpression body, ExpressionType linkingType, ref SQlQuery query)
{
List <QueryParameter> queryProperties = new List <QueryParameter>();
if (body.NodeType != ExpressionType.AndAlso && body.NodeType != ExpressionType.OrElse)
{
var slides = body.Left.ToString().Split(new char[] { '.' });
string[] conditions = new string[slides.Count()];
GetExactlyNameParam(typeof(T), slides, ref conditions);
string propertyName = string.Join(".", conditions.Where(c => !string.IsNullOrEmpty(c)));
if (body.Left.NodeType == ExpressionType.Convert)
{
//Remove the trailing ) when convering.
propertyName = propertyName.Replace(")", string.Empty);
}
List <string> joinOperator = new List <string>();
if (propertyName.Contains('.')) //apply join
{
GetJoinOperation(typeof(T), propertyName, ref joinOperator);
}
else
{
propertyName = query.RootTable + '.' + propertyName;
}
dynamic propertyValue = body.Right;
string opr = GetOperator(body.NodeType);
string link = GetOperator(linkingType);
string[] elements = propertyName.Split(new char[] { '.' });
if (elements.Count() > 2)
{
string removed = elements[0];
propertyName = propertyName.Replace(removed + '.', string.Empty);
}
query.AddQueryParameter(new QueryParameter(link, propertyName, propertyValue.Value, opr));
if (joinOperator != null && joinOperator.Any())
{
query.AddJoinOperator(joinOperator);
}
}
else
{
query = BuildCondition <T>(body.Left, body, query);
query = BuildCondition <T>(body.Right, body, query);
}
}
private static void GetCondition <T>(string linkOperator, string[] arrUnary, ref string condition, ref SQlQuery query)
{
switch (arrUnary.FirstOrDefault())
{
case "Not":
condition += " NOT {0}";
break;
case "IsNullOrEmpty":
string value = arrUnary[1].Replace(")", string.Empty);
List <string> joinOperator = new List <string>();
string whereCondition = string.Empty;
string[] arrValue = value.Split('.');
if (arrValue.Count() > 1)
{
var replacedTable = value[0];
string newProperties = string.Empty;
for (int i = 0; i < arrValue.Count(); i++)
{
if (i == 0)
{
continue;
}
else if (i == arrValue.Count() - 1)
{
newProperties += arrValue[i];
}
else
{
newProperties += arrValue[i];
newProperties += '.';
}
}
//string tableName = QueryHelper.GetTableName<T>();
GetJoinOperation(typeof(T), newProperties, ref joinOperator);
GetWhereOperation(linkOperator, newProperties, ref whereCondition);
}
if (!string.IsNullOrEmpty(condition))
{
whereCondition = string.Format(condition, whereCondition);
}
query.AddJoinOperator(joinOperator);
query.AddWhereCondition(whereCondition);
condition = string.Empty;
break;
}
arrUnary = arrUnary.Where((value, index) => index != 0).ToArray();
if (arrUnary != null && arrUnary.Count() > 1)
{
GetCondition <T>(linkOperator, arrUnary, ref condition, ref query);
}
}
public static QueryResult <T> Get <T>()
{
SQlQuery query = new SQlQuery(EXTable.GetTableName <T>());
return(new QueryResult <T>(query));
}
public QuerySource(SQlQuery sql)
{
Sql = sql;
}
public QueryResult(SQlQuery sql)
{
_result = new QuerySource <SQlQuery>(sql);
}
public static void AddQueryParameter(this SQlQuery query, QueryParameter parameters)
{
query.QueryParameters.Add(parameters);
}