采用表达式树进行多表连查，作为一个棘手的问题，在不使用linq的情况下，稍微封装了一下
static void Main(string[] args) { JoinBuilder jb = new JoinBuilder(); jb.AddLeftJoin<Order, Product>(m=>new {ID= m.ProductID},m=>m.ID); jb.AddWhere<Order>(m=>m.ProductID>=2).AddWhere<Product>(m=>m.Name=="phone"); jb.AddOrderByAsc<Product>(m=>new { m.ID, m.Name }).AddOrderByDesc<Order>(m=>m.UserName); jb.AddSelect<Order>(m => new { m.ProductID, ID1 = m.ID }); jb.Select<dynamic>(); Console.Read(); }
作为简单规整的连接查询，避免了sql还是比较方便，基本上考虑到多表、条件<带参数化>、排序，对于较为复杂的查询譬如分组、嵌套等，因为封装起来实在太复杂，就算封装出来了也会像写linq一样写的比较复杂，不如直接写sql来的成本低
public class ConditionVisitor<T> : ExpressionVisitor { /// <summary> /// 连接条件 /// </summary> public List<OnCondition> Conditions { get; set; } public ConditionVisitor() { Conditions = new List<OnCondition>(); } public void Translate(Expression<Func<T, bool>> exp) { this.Conditions.Clear(); this.Visit(exp); } public void Translate<TKey>(Expression<Func<T, TKey>> exp) { this.Conditions.Clear(); this.Visit(exp); } protected override Expression VisitNew(NewExpression node) { for (int i = 0; i < node.Arguments.Count; i++) { if (Conditions.Count(m => m.Key == node.Members[i].Name) == 0) { Conditions.Add(new OnCondition { Key = node.Members[i].Name }); } this.Visit(node.Arguments[i]); } return node; } protected override MemberAssignment VisitMemberAssignment(MemberAssignment assignment) { if (Conditions.Count(m => m.Key == assignment.Member.Name) == 0) { Conditions.Add(new OnCondition { Key = assignment.Member.Name }); } Expression e = this.Visit(assignment.Expression); if (e != assignment.Expression) { return Expression.Bind(assignment.Member, e); } return assignment; } protected override Expression VisitMember(MemberExpression memberExp) { if (memberExp.Expression != null && memberExp.Expression.NodeType == ExpressionType.Parameter) { var ss = memberExp.Expression.Type.Name; string fieldName = typeof(T).Name + "." + memberExp.Member.Name; if (Conditions.Count > 0) { Conditions.Last().Field = fieldName; } else { Conditions.Add(new OnCondition { Key = memberExp.Member.Name, Field = fieldName }); } return memberExp; } else if (memberExp.NodeType == ExpressionType.MemberAccess) { object result = Expression.Lambda(memberExp).Compile().DynamicInvoke(); var r = Expression.Constant(result, result.GetType()); this.Visit(r); return r; } throw new NotSupportedException(string.Format("成员 '{0}’不支持", memberExp.Member.Name)); } protected override Expression VisitMethodCall(MethodCallExpression methodExp) { if (methodExp.Method.DeclaringType == typeof(string) && methodExp.Method.Name == "Contains") { Expression obj = this.Visit(methodExp.Object); this.Visit(methodExp.Arguments[0]); return methodExp; } else { Expression obj = this.Visit(methodExp.Object); object result = Expression.Lambda(methodExp).Compile().DynamicInvoke(); var r = Expression.Constant(result, methodExp.Method.ReturnType); this.Visit(r); return r; } throw new NotSupportedException(string.Format("lambda表达式不支持使用此'{0}'方法", methodExp.Method.Name)); } protected override Expression VisitUnary(UnaryExpression unaryExp) { try { if (unaryExp.NodeType == ExpressionType.Convert) { this.Visit(unaryExp.Operand); return unaryExp; } else { object result = Expression.Lambda(unaryExp).Compile().DynamicInvoke(); var r = Expression.Constant(result, result.GetType()); this.Visit(r); return r; } } catch { throw new NotSupportedException(string.Format("一元运算符 '{0}’不支持", unaryExp.NodeType)); } } protected override Expression VisitBinary(BinaryExpression binaryExp) { this.Visit(binaryExp.Left); this.Visit(binaryExp.Right); return binaryExp; } protected bool IsNullConstant(Expression constantExp) { object result = Expression.Lambda(constantExp).Compile().DynamicInvoke(); return result == null; } }
VisitMethodCall可以扩展支持更多的方法，譬如可以自己扩展一个In的方法
protected override Expression VisitMethodCall(MethodCallExpression methodExp) { if (methodExp.Method.DeclaringType == typeof(string) && methodExp.Method.Name == "Contains") { Expression obj = this.Visit(methodExp.Object); SqlBuilder.Append(" LIKE '%'+"); this.Visit(methodExp.Arguments[0]); SqlBuilder.Append("+'%'"); return methodExp; } else if (methodExp.Method.DeclaringType == typeof(Extension) && methodExp.Method.Name == "Like") { Expression obj = this.Visit(methodExp.Arguments[0]); SqlBuilder.Append(" LIKE '%'+"); this.Visit(methodExp.Arguments[1]); SqlBuilder.Append("+'%'"); return methodExp; } else if (methodExp.Method.DeclaringType == typeof(Extension) && methodExp.Method.Name == "In") { Expression obj = this.Visit(methodExp.Arguments[0]); SqlBuilder.Append(" IN ("); int count = ((NewArrayExpression)methodExp.Arguments[1]).Expressions.Count; for (int i = 0; i < count; i++) { //ParameterName = ParameterName + i.ToString(); this.Visit(((NewArrayExpression)methodExp.Arguments[1]).Expressions[i]); if (i < count - 1) { SqlBuilder.Append(","); } } SqlBuilder.Append(")"); return methodExp; } else if (methodExp.Method.DeclaringType == typeof(Extension) && methodExp.Method.Name == "NotIn") { Expression obj = this.Visit(methodExp.Arguments[0]); SqlBuilder.Append(" NOT IN ("); int count = ((NewArrayExpression)methodExp.Arguments[1]).Expressions.Count; for (int i = 0; i < count; i++) { //ParameterName = ParameterName + i.ToString(); this.Visit(((NewArrayExpression)methodExp.Arguments[1]).Expressions[i]); if (i < count - 1) { SqlBuilder.Append(","); } } SqlBuilder.Append(")"); return methodExp; } else { Expression obj = this.Visit(methodExp.Object); object result = Expression.Lambda(methodExp).Compile().DynamicInvoke(); var r = Expression.Constant(result, methodExp.Method.ReturnType); this.Visit(r); return r; } throw new NotSupportedException(string.Format("lambda表达式不支持使用此'{0}'方法", methodExp.Method.Name)); }
public class WhereVisitor<T> : ExpressionVisitor, ITranslator { private List<DbParameter> parameters; /// <summary> /// 条件参数 /// </summary> public List<DbParameter> Parameters { get { return this.parameters; } } public string ParameterPrefix { get { return "@"; } } public string ParameterName { get; set; } public StringBuilder SqlBuilder { get; set; } public WhereVisitor() { this.parameters = new List<DbParameter>(); } public string Translate(Expression<Func<T, bool>> exp) { this.SqlBuilder = new StringBuilder(); this.Visit(exp); return this.SqlBuilder.ToString(); } protected override Expression VisitMethodCall(MethodCallExpression methodExp) { if (methodExp.Method.DeclaringType == typeof(string) && methodExp.Method.Name == "Contains") { Expression obj = this.Visit(methodExp.Object); SqlBuilder.Append(" LIKE '%'+"); this.Visit(methodExp.Arguments[0]); SqlBuilder.Append("+'%'"); return methodExp; } else { Expression obj = this.Visit(methodExp.Object); object result = Expression.Lambda(methodExp).Compile().DynamicInvoke(); var r = Expression.Constant(result, methodExp.Method.ReturnType); this.Visit(r); return r; } throw new NotSupportedException(string.Format("lambda表达式不支持使用此'{0}'方法", methodExp.Method.Name)); } protected override Expression VisitUnary(UnaryExpression unaryExp) { try { if (unaryExp.NodeType == ExpressionType.Convert) { this.Visit(unaryExp.Operand); return unaryExp; } else { object result = Expression.Lambda(unaryExp).Compile().DynamicInvoke(); var r = Expression.Constant(result, result.GetType()); this.Visit(r); return r; } } catch { throw new NotSupportedException(string.Format("一元运算符 '{0}’不支持", unaryExp.NodeType)); } } protected override Expression VisitBinary(BinaryExpression binaryExp) { SqlBuilder.Append("("); this.Visit(binaryExp.Left); switch (binaryExp.NodeType) { case ExpressionType.And: SqlBuilder.Append(" AND "); break; case ExpressionType.AndAlso: SqlBuilder.Append(" AND "); break; case ExpressionType.Or: SqlBuilder.Append(" OR "); break; case ExpressionType.OrElse: SqlBuilder.Append(" OR "); break; case ExpressionType.Equal: if (IsNullConstant(binaryExp.Right)) { SqlBuilder.Append(" IS "); } else { SqlBuilder.Append(" = "); } break; case ExpressionType.NotEqual: if (IsNullConstant(binaryExp.Right)) { SqlBuilder.Append(" IS NOT "); } else { SqlBuilder.Append(" <> "); } break; case ExpressionType.LessThan: SqlBuilder.Append(" < "); break; case ExpressionType.LessThanOrEqual: SqlBuilder.Append(" <= "); break; case ExpressionType.GreaterThan: SqlBuilder.Append(" > "); break; case ExpressionType.GreaterThanOrEqual: SqlBuilder.Append(" >= "); break; default: throw new NotSupportedException(string.Format("二元运算符 '{0}'不支持", binaryExp.NodeType)); } this.Visit(binaryExp.Right); SqlBuilder.Append(")"); return binaryExp; } protected bool IsNullConstant(Expression constantExp) { object result = Expression.Lambda(constantExp).Compile().DynamicInvoke(); return result == null; } protected override Expression VisitConstant(ConstantExpression constantExp) { IQueryable q = constantExp.Value as IQueryable; if (constantExp.Value == null) { SqlBuilder.Append("NULL"); } else if (q == null) { int count = 0; string tempName = ParameterName; if ((count = this.parameters.Where(m => m.ParameterName.Contains(ParameterName)).Count()) > 0) { tempName = string.Format("{0}{1}{2}", ParameterName, "_", count); } this.parameters.Add(GetDbParameter(tempName, constantExp.Value)); SqlBuilder.Append(tempName); } return constantExp; } protected override Expression VisitMember(MemberExpression memberExp) { if (memberExp.Expression != null && memberExp.Expression.NodeType == ExpressionType.Parameter) { var ss = memberExp.Expression.Type.Name; string fieldName = GetFieldName(memberExp.Member.Name); if (!string.IsNullOrEmpty(fieldName)) { SqlBuilder.Append(fieldName); ParameterName = ParameterPrefix+ typeof(T).Name + "_" + memberExp.Member.Name + "_Lambda"; } return memberExp; } else if (memberExp.NodeType == ExpressionType.MemberAccess) { object result = Expression.Lambda(memberExp).Compile().DynamicInvoke(); var r = Expression.Constant(result, result.GetType()); this.Visit(r); return r; } throw new NotSupportedException(string.Format("成员 '{0}’不支持", memberExp.Member.Name)); } protected string GetFieldName(string propertyName) { return typeof(T).Name+"."+propertyName; } public DbParameter GetDbParameter(string parameterName, object value) { return new SqlParameter(parameterName, value); } }
public enum LinkType { Outer, Inner } /// <summary> /// 连接对条件 /// </summary> public class OnCondition { public string Key { get; set; } public string Field { get; set; } } /// <summary> /// 连接条件 /// </summary> public class JoinLink { public JoinLink() { LeftFields = new List<OnCondition>(); RightFields = new List<OnCondition>(); } public LinkType LinkType { get; set; } public string LeftTable { get; set; } public string RightTable { get; set; } public List<OnCondition> LeftFields { get; set; } public List<OnCondition> RightFields { get; set; } public string GetCondition() { StringBuilder cb = new StringBuilder(); foreach (OnCondition c in LeftFields) { cb.Append(" "); cb.Append(c.Field); cb.Append("="); cb.Append(RightFields.SingleOrDefault(m => m.Key == c.Key).Field); cb.Append(" AND"); } return cb.ToString().TrimEnd("AND".ToCharArray()); } } public class TableSelect { public TableSelect() { Fields = new List<OnCondition>(); } public string Table { get; set; } /// <summary> /// field AS key /// </summary> public List<OnCondition> Fields { get; set; } public string GetSelect() { StringBuilder cb = new StringBuilder(); foreach (OnCondition c in Fields) { cb.Append(c.Field); cb.Append(" AS "); cb.Append(c.Key); cb.Append(","); } return cb.ToString(); } } /// <summary> /// 连接查询辅助类 /// 未考虑实体、属性的特性 /// </summary> public class JoinBuilder { /// <summary> /// SQL /// </summary> public StringBuilder SqlBuilder { get; set; } /// <summary> /// 连接条件 /// </summary> public List<JoinLink> JoinLinks { get; set; } /// <summary> /// where条件 /// 最后拼接SQL的时候要Trim掉'AND' /// </summary> public StringBuilder Where { get; set; } /// <summary> /// 条件参数 /// </summary> public List<DbParameter> Parameters { get; set; } /// <summary> /// 排序 /// 最后拼接SQL的时候要Trim掉',' /// </summary> public StringBuilder OrderBy { get; set; } /// <summary> /// select筛选 /// </summary> public List<TableSelect> TableSelects { get; set; } public JoinBuilder() { SqlBuilder = new StringBuilder(); JoinLinks = new List<JoinLink>(); TableSelects = new List<TableSelect>(); Where = new StringBuilder(); Parameters = new List<DbParameter>(); OrderBy = new StringBuilder(); } /// <summary> /// 添加左外连接 /// </summary> /// <typeparam name="TLeft"></typeparam> /// <typeparam name="TRight"></typeparam> /// <param name="onLeft"></param> /// <param name="onRight"></param> /// <returns></returns> public JoinBuilder AddLeftJoin<TLeft, TRight>(Expression<Func<TLeft, dynamic>> onLeft, Expression<Func<TRight, dynamic>> onRight) { JoinLink link = new JoinLink { LinkType = LinkType.Outer }; //暂时不考虑特性 link.LeftTable = typeof(TLeft).Name; link.RightTable = typeof(TRight).Name; ConditionVisitor<TLeft> visitorL = new ConditionVisitor<TLeft>(); visitorL.Translate(onLeft); link.LeftFields.AddRange(visitorL.Conditions.ToArray());//得到左边lambda属性信息 ConditionVisitor<TRight> visitorR = new ConditionVisitor<TRight>(); visitorR.Translate(onRight); link.RightFields.AddRange(visitorR.Conditions.ToArray());//得到右边lambda属性信息 JoinLinks.Add(link); return this; } /// <summary> /// 添加左内连接 /// </summary> /// <typeparam name="TLeft"></typeparam> /// <typeparam name="TRight"></typeparam> /// <param name="onLeft"></param> /// <param name="onRight"></param> /// <returns></returns> public JoinBuilder AddInnerJoin<TLeft, TRight>(Expression<Func<TLeft, dynamic>> onLeft, Expression<Func<TRight, dynamic>> onRight) { JoinLink link = new JoinLink { LinkType = LinkType.Inner }; //暂时不考虑特性 link.LeftTable = typeof(TLeft).Name; link.RightTable = typeof(TRight).Name; ConditionVisitor<TLeft> visitorL = new ConditionVisitor<TLeft>(); visitorL.Translate(onLeft); link.LeftFields = visitorL.Conditions;//得到左边lambda属性信息 ConditionVisitor<TRight> visitorR = new ConditionVisitor<TRight>(); visitorR.Translate(onRight); link.RightFields = visitorR.Conditions;//得到右边lambda属性信息 JoinLinks.Add(link); return this; } /// <summary> /// 添加where查询条件 /// </summary> /// <typeparam name="TEntity"></typeparam> /// <param name="where"></param> /// <returns></returns> public JoinBuilder AddWhere<TEntity>(Expression<Func<TEntity, bool>> where) { WhereVisitor<TEntity> visitor = new WhereVisitor<TEntity>(); visitor.Translate(where); Where.Append(visitor.SqlBuilder.ToString() + " AND"); Parameters.AddRange(visitor.Parameters); return this; } public JoinBuilder AddOrderByAsc<TEntity>(Expression<Func<TEntity, dynamic>> orderBy) { ConditionVisitor<TEntity> visitor = new ConditionVisitor<TEntity>(); visitor.Translate(orderBy); foreach (OnCondition c in visitor.Conditions) { OrderBy.Append(c.Field + " ASC,"); } return this; } public JoinBuilder AddOrderByDesc<TEntity>(Expression<Func<TEntity, dynamic>> orderBy) { ConditionVisitor<TEntity> visitor = new ConditionVisitor<TEntity>(); visitor.Translate(orderBy); foreach (OnCondition c in visitor.Conditions) { OrderBy.Append(c.Field + " DESC,"); } return this; } public JoinBuilder AddSelect<TEntity>(Expression<Func<TEntity, dynamic>> select = null) { TableSelect tableSelect = new TableSelect { Table = typeof(TEntity).Name }; if (select == null) { tableSelect.Fields.Add(new OnCondition { Field = typeof(TEntity).Name + ".*" }); } else { ConditionVisitor<TEntity> visitor = new ConditionVisitor<TEntity>(); visitor.Translate(select); tableSelect.Fields.AddRange(visitor.Conditions.ToArray()); } TableSelects.Add(tableSelect); return this; } public TModel Select<TModel>() { SqlBuilder.Append(" SELECT "); foreach (TableSelect s in TableSelects) { SqlBuilder.Append(s.GetSelect()); } SqlBuilder.Remove(SqlBuilder.Length - 1, 1);//去掉',' SqlBuilder.Append(" FROM "); SqlBuilder.Append(JoinLinks[0].LeftTable); foreach (JoinLink j in JoinLinks) { if (j.LinkType == LinkType.Outer) { SqlBuilder.Append(" LEFT OUTER JOIN "); } else { SqlBuilder.Append(" INNER JOIN "); } SqlBuilder.Append(j.RightTable); SqlBuilder.Append(" ON "); SqlBuilder.Append(j.GetCondition()); } if (Where.Length > 0) { SqlBuilder.Append(" WHERE "); SqlBuilder.Append(Where.ToString()); SqlBuilder.Remove(SqlBuilder.Length - 3, 3);//去掉'AND' } if (OrderBy.Length > 0) { SqlBuilder.Append(" ORDER BY "); SqlBuilder.Append(OrderBy.ToString()); SqlBuilder.Remove(SqlBuilder.Length - 1, 1);//去掉'AND' } //开始组装sql return default(TModel); } }