public void Build(Expression expression, AverageFunction ave)
{
var resolve = new ExpressionResolve();
_builder.SelectField = new List <string>();
resolve.VisitMember(expression).ForEach(x =>
{
var field = string.Empty;
switch (ave)
{
case AverageFunction.MAX:
field = " MAX({0})";
break;
case AverageFunction.MIN:
field = " MIN({0})";
break;
case AverageFunction.SUM:
field = " SUM({0})";
break;
case AverageFunction.AVG:
field = " AVG({0})";
break;
}
_builder.SelectField.Add(string.Format(field, _builder.Adapter.Field(_builder.Table, _builder.TableAliasName, x.FieldName, x.SelectFiledAliasName)));
});
}
public void Build(Expression expression)
{
var resolve = new ExpressionResolve();
_builder.SelectField = new List <string>();
_builder.SelectField =
resolve.VisitMember(expression).Select(x => _builder.Adapter.Field(_builder.Table, _builder.TableAliasName, x.FieldName, x.SelectFiledAliasName))
.ToList();
}
public static Tuple <string, List <SqlParameter> > DeleteSql <T>(this T entity, Expression <Func <T, bool> > func)
{
var condition = ExpressionResolve.Resolve(func);
var et = typeof(T);
var tableAttr = et.GetCustomAttributes(typeof(TableNameAttribute), true);
var tableName = tableAttr.Any() ? (tableAttr[0] as TableNameAttribute).Name : et.Name;
var sql = string.Format("DELETE [{0}] WHERE {1}; SELECT @@ROWCOUNT;", tableName.ToUpper(), condition.Item1);
return(new Tuple <string, List <SqlParameter> >(sql, condition.Item2));
}
public void Build(Expression expression, bool desc = false)
{
var resolve = new ExpressionResolve();
resolve.VisitMember(expression).ForEach(x =>
{
var order = _builder.Adapter.Field(_builder.IsEnableAlias ? x.TableAliasName : x.TableName, x.FieldName);
_builder.Order.Add(order + (desc ? " DESC " : " ASC "));
});
}
public void Build(Expression expression)
{
var resolve = new ExpressionResolve();
var memberList = new List <string>();
resolve.VisitMember(expression).ForEach(x =>
{
memberList.Add(_builder.Adapter.Field(x.TableName, x.TableAliasName, x.FieldName));
});
_builder.GroupBy = " GROUP BY " + string.Join(",", memberList);
}
public void TestMethod1()
{
var a = "hello";
var b = "hello2";
var d = DateTime.Now;
var res = ExpressionResolve.Resolve <Demo>(t => t.Name == "hello" && (t.Name == a || t.Name == b) || t.IsDel == true && t.AddTime < d || t.Name == "jerry" || t.Name == "56");
var res2 = ExpressionResolve.Resolve <Demo>(t => t.Name == "Hello" || (t.Name.Contains("He") && t.Age > 20));
var res3 = ExpressionResolve.Resolve <Demo>(t => t.Name.Contains("He") && (t.Age > 20 || t.AddTime < d));
var res4 = ExpressionResolve.Resolve <Demo>(t => (t.Age > 20 || t.AddTime < d) && t.Name.Contains("He"));
var res5 = ExpressionResolve.Resolve <Demo>(t => (t.Age > 20 && t.AddTime < d) || (t.Name.Contains("Hel") && t.Name.Contains("He")));
Assert.IsTrue(true);
}
public static Tuple <string, List <SqlParameter> > TopSql <T>(this T entity, Expression <Func <T, bool> > func, Expression <Func <T, object> > order, bool isDesc = false)
{
var condition = ExpressionResolve.Resolve(func);
var orderName = order == null ? "" : ExpressionResolve.ResolveSingle(order);
var sortMode = isDesc ? "DESC" : "ASC";
var et = typeof(T);
var tableAttr = et.GetCustomAttributes(typeof(TableNameAttribute), true);
var tableName = tableAttr.Any() ? (tableAttr[0] as TableNameAttribute).Name : et.Name;
var sql = order == null
? string.Format("SELECT TOP 1 * FROM [{0}] WITH(NOLOCK) WHERE {1} ;", tableName, condition.Item1)
: string.Format("SELECT TOP 1 * FROM [{0}] WITH(NOLOCK) WHERE {1} ORDER BY [{2}] {3};", tableName, condition.Item1, orderName, sortMode);
return(new Tuple <string, List <SqlParameter> >(sql, condition.Item2));
}
public static Tuple <string, List <SqlParameter>, string> PageSql <T>(this T entity, Expression <Func <T, bool> > where, Expression <Func <T, object> > order, int page, int limit, bool isDesc = false)
{
var condition = ExpressionResolve.Resolve(where);
var orderName = ExpressionResolve.ResolveSingle(order);
var sortMode = isDesc ? "DESC" : "ASC";
var et = typeof(T);
var tableAttr = et.GetCustomAttributes(typeof(TableNameAttribute), true);
var tableName = tableAttr.Any() ? (tableAttr[0] as TableNameAttribute).Name : et.Name;
var sql = string.Format(@"WITH PAGERESULT AS (SELECT ROW_NUMBER() OVER(ORDER BY [{0}] {1}) AS NUMBER, * FROM [{2}] WHERE {3}) "
, orderName, sortMode, tableName, condition.Item1);
var sqlData = string.Format("{0} SELECT TOP {1} * FROM PAGERESULT WHERE NUMBER>{2} ;", sql, limit, (page - 1) * limit);
var sqlCnt = string.Format("{0} SELECT COUNT(1) FROM PAGERESULT ;", sql);
return(new Tuple <string, List <SqlParameter>, string>(sqlData, condition.Item2, sqlCnt));
}
public void Build <T1, TResult>(Expression <Func <T1, TResult, bool> > expression, JoinType joinType)
{
var joinExpression = Helper.GetBinaryExpression(expression.Body);
var resolve = new ExpressionResolve();
var originalMember = resolve.VisitMember(joinExpression.Left).FirstOrDefault();
var joinMember = resolve.VisitMember(joinExpression.Right).FirstOrDefault();
var originalTable = CacheHelper.GetTableInfo(typeof(T));
var joinTable = CacheHelper.GetTableInfo(typeof(TResult));
var joinTypeString = string.Empty;
switch (joinType)
{
case JoinType.InnerJoin:
joinTypeString = "INNER JOIN";
break;
case JoinType.LeftJoin:
joinTypeString = "LEFT JOIN";
break;
case JoinType.RightJoin:
joinTypeString = "RIGHT JOIN";
break;
}
var joinString = string.Format(" {0} {1} ON {2} = {3}",
joinTypeString,
_builder.Adapter.Table(joinTable.Name, joinTable.Alias),
_builder.Adapter.Field(originalMember.TableAliasName, originalMember.FieldName),
_builder.Adapter.Field(joinMember.TableAliasName, joinMember.FieldName));
_builder.Table = _builder.Table + joinString;
}
