/// <summary> /// 转换字符串为特定类型的查询对象,其中类型由 T 参数定义 /// <para> /// 例:SELECT FieldName FROM TableName WHERE Condition={0} AND Condition1={1} /// </para> /// </summary> /// <param name="sql">SQL 命令</param> /// <param name="args">参数列表</param> public IDbQueryable <T> GetTable <T>(string sql, params object[] args) { DbQueryable <T> query = new DbQueryable <T>(this, new List <DbExpression> { new DbExpression(DbExpressionType.GetTable, new[] { Expression.Constant(sql), Expression.Constant(args, typeof(object[])) }), }); return(query); }
/// <summary> /// 构造一个 <see cref="IDbQueryable"/> 对象,该对象可计算指定表达式树所表示的查询 /// </summary> /// <typeparam name="TResult">返回的元素类型</typeparam> /// <param name="dbExpression">查询表达式</param> /// <returns></returns> public IDbQueryable <TResult> CreateQuery <TResult>(DbExpression dbExpression) { var collection = new List <DbExpression>(this.DbExpressions.Count + (dbExpression != null ? 1 : 0)); collection.AddRange(_dbExpressions); if (dbExpression != null) { collection.Add(dbExpression); } IDbQueryable <TResult> query = new DbQueryable <TResult>(this.DbContext, collection); return(query); }
/// <summary> /// 创建 SQL 命令 /// </summary> /// <param name="source">查询 语句</param> /// <param name="indent">缩进</param> /// <param name="isOutermost">是否外层查询,内层查询不需要结束符(;)</param> /// <param name="context">解析SQL命令上下文</param> /// <returns></returns> internal DbRawCommand Translate <T>(IDbQueryable <T> source, int indent, bool isOutermost, ITranslateContext context) { DbQueryable <T> dbQueryable = (DbQueryable <T>)source; // 当前查询语义如果不设置参数化,默认使用参数化 if (!dbQueryable.HasSetParameterized) { dbQueryable.Parameterized = true; } // 如果解析上下文是空,默认创建一个上下文,用来承载 DbContext (查询上下文) if (context == null) { context = this.CreateTranslateContext(dbQueryable.DbContext); } // 如果使用参数化查询并且参数列表为空,默认创建了一个新的参数列表 if (dbQueryable.Parameterized && context.Parameters == null) { context.Parameters = new List <IDbDataParameter>(8); } // 解析查询语义 IDbQueryTree tree = DbQueryableParser.Parse <T>(dbQueryable); // 查询 if (tree is DbQuerySelectTree) { return(this.TranslateSelectCommand((DbQuerySelectTree)tree, indent, isOutermost, context)); } // 新增 else if (tree is DbQueryInsertTree) { return(this.TranslateInsertCommand <T>((DbQueryInsertTree)tree, context)); } // 更新 else if (tree is DbQueryUpdateTree) { return(this.TranslateUpdateCommand <T>((DbQueryUpdateTree)tree, context)); } // 删除 else if (tree is DbQueryDeleteTree) { return(this.TranslateDeleteCommand <T>((DbQueryDeleteTree)tree, context)); } throw new NotImplementedException(); }
/// <summary> /// 解析左关联 /// </summary> protected virtual void AppendLfInJoin(DbExpression dbExpression) { _builder.Append(' '); DbQueryable dbQueryable = (DbQueryable)((dbExpression.Expressions[0] as ConstantExpression).Value); dbQueryable.Parameterized = _builder.Parameterized; if (dbQueryable.DbExpressions.Count == 1 && dbQueryable.DbExpressions[0].DbExpressionType == DbExpressionType.GetTable) { // 区别 GetTable 有三个重载 var expressions = dbQueryable.DbExpressions[0].Expressions; if (expressions.Length == 2 && ((expressions[0] as ConstantExpression).Value as string) != null) { string text = (expressions[0] as ConstantExpression).Value as string; object[] @params = (expressions[1] as ConstantExpression).Value as object[]; var provider = ((DbQueryProvider)_builder.Provider); var context = _builder.TranslateContext; // 解析参数 object[] args = null; if (@params != null) { args = @params.Select(x => provider.Constor.GetSqlValue(x, context)).ToArray(); } string sql = text; if (args != null && args.Length > 0) { sql = string.Format(sql, args); } var cmd = new DbRawCommand(sql, context.Parameters, CommandType.Text); _builder.Append("("); _builder.Append(cmd.CommandText); _builder.AppendNewLine(); _builder.Append(')'); } else { Type type = dbExpression.Expressions[0].Type.GetGenericArguments()[0]; var typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo(type); _builder.AppendTable(typeRuntime.TableSchema, typeRuntime.TableName, typeRuntime.IsTemporary); } } else { // 嵌套 var cmd = dbQueryable.Translate(_builder.Indent + 1, false, _builder.TranslateContext); _builder.Append("("); _builder.Append(cmd.CommandText); _builder.AppendNewLine(); _builder.Append(')'); } var left = dbExpression.Expressions[1] as LambdaExpression; var right = dbExpression.Expressions[2] as LambdaExpression; // t0(t1) string alias = !(left.Body.NodeType == ExpressionType.New || left.Body.NodeType == ExpressionType.MemberInit) ? _ag.GetTableAlias(dbExpression.Expressions[2]) : _ag.GetTableAlias(right.Parameters[0]); _builder.Append(' '); _builder.Append(alias); _builder.Append(' '); // ON a.Name = b.Name AND a.Id = b.Id _builder.Append("ON "); if (left.Body.NodeType == ExpressionType.New) { var body1 = left.Body as NewExpression; var body2 = right.Body as NewExpression; for (int index = 0; index < body1.Arguments.Count; ++index) { base.Visit(body1.Arguments[index]); _builder.Append(" = "); base.Visit(body2.Arguments[index]); if (index < body1.Arguments.Count - 1) { _builder.Append(" AND "); } } } else if (left.Body.NodeType == ExpressionType.MemberInit) { var body1 = left.Body as MemberInitExpression; var body2 = right.Body as MemberInitExpression; for (int index = 0; index < body1.Bindings.Count; ++index) { base.Visit((body1.Bindings[index] as MemberAssignment).Expression); _builder.Append(" = "); base.Visit((body2.Bindings[index] as MemberAssignment).Expression); if (index < body1.Bindings.Count - 1) { _builder.Append(" AND "); } } } else { base.Visit(left.Body.ReduceUnary()); _builder.Append(" = "); base.Visit(right.Body.ReduceUnary()); } }
/// <summary> /// 返回特定类型的对象的集合,其中类型由 T 参数定义 /// <para> /// 适用场景: /// 多个导航属性类型一致,用此重载可解决 a.Navigation.Property 的表别名定位问题 /// </para> /// </summary> /// <typeparam name="T">元素类型</typeparam> /// <typeparam name="TNavMember">导航属性类型</typeparam> /// <param name="path">导航属性,注意在一组上下文中第一个 GetTable 的这个参数将被自动忽略</param> /// <returns></returns> public IDbQueryable <TNavMember> GetTable <T, TNavMember>(Expression <Func <T, TNavMember> > path) { DbQueryable <TNavMember> query = new DbQueryable <TNavMember>(this, new List <DbExpression> { new DbExpression(DbExpressionType.GetTable, new[] { Expression.Constant(typeof(TNavMember)), (Expression)path }), }); return(query); //// 在 select 语义保证外键属性不为空,否则在 ko 绑定的时候会产生不可预料的异常 //var query = // from a in context.GetTable<Client>() // // 最新订单 // join b in newestQuery on a.ClientId equals b.ClientId into u_b // // 注册推荐码 // join c in context.GetTable<Client, RecommendationCode>(a => a.RegRecommendationCode) on a.RegRecommendationId equals c.RecommendationId into u_c // // 注册推荐码对应的合作商 // from c in u_c.DefaultIfEmpty() // join d in context.GetTable<Partner>() on new { A0 = (int)RecommendationType.ServiceAgent, B0 = c.ServiceAgentId } equals new { A0 = d.PartnerType, B0 = d.PartnerId } into u_d // join e in context.GetTable<Partner>() on new { A0 = (int)RecommendationType.SaleAgent, B0 = c.SaleAgentId } equals new { A0 = e.PartnerType, B0 = e.PartnerId } into u_e // // 推荐码 // join f in context.GetTable<Client, RecommendationCode>(a => a.RecommendationCode) on a.RecommendationId equals f.RecommendationId into u_f // // 推荐码对应的合作商 // from f in u_f.DefaultIfEmpty() // join g in context.GetTable<Partner>() on new { A0 = (int)RecommendationType.ServiceAgent, B0 = f.ServiceAgentId } equals new { A0 = g.PartnerType, B0 = g.PartnerId } into u_g // join h in context.GetTable<Partner>() on new { A0 = (int)RecommendationType.SaleAgent, B0 = f.SaleAgentId } equals new { A0 = h.PartnerType, B0 = h.PartnerId } into u_h // from b in u_b.DefaultIfEmpty() // from d in u_d.DefaultIfEmpty() // from e in u_e.DefaultIfEmpty() // from g in u_g.DefaultIfEmpty() // from h in u_h.DefaultIfEmpty() // select new Client(a) // { // PartnerShortName = h.PartnerId != null ? h.ShortName : (g.PartnerId != null ? g.ShortName : (e.PartnerId != null ? e.ShortName : d.ShortName)), // Employee = new Employee { Deletable = canEidtEmployee, EmployeeCode = a.Employee.EmployeeCode, EmployeeName = a.Employee.EmployeeName }, // Operation = new Employee { Deletable = canEidtOperation, EmployeeName = a.Operation.EmployeeName }, // CS = new Employee { Deletable = canEidtCs, EmployeeName = a.CS.EmployeeName }, // Manager = new Employee { EmployeeName = a.Manager.EmployeeName }, // Signature = new Employee { EmployeeName = a.Signature.EmployeeName }, // Package = new Package { PackageType = a.Package.PackageType, PackageCode = a.Package.PackageCode, PackageName = a.Package.PackageName }, // RegRecommendationCode = new RecommendationCode // { // SaleType = c.SaleType, // RecommendationName = c.RecommendationName // //RecommendationName = a.RegRecommendationCode.RecommendationName // }, // RecommendationCode = new RecommendationCode // { // SaleType = f.SaleType, // RecommendationName = f.RecommendationName // //RecommendationName = a.RecommendationCode.RecommendationName //f.RecommendationName // }, // NewestOrder = new SaleOrder // { // OrderId = b.OrderId, // OrderNo = b.OrderNo, // EmployeeId = b.EmployeeId, // EmployeeName = b.EmployeeName, // PayDate = b.PayDate, // PayAmount = b.PayAmount, // ClientId = b.ClientId, // Currency = b.Currency // } // }; //query = query.Where(predicate); //return query; }
/// <summary> /// 解析 SQL 命令 /// <para> /// 返回的已经解析语义中执行批次用 null 分开 /// </para> /// </summary> /// <param name="dbQueryables">查询语句</param> /// <returns></returns> public virtual List <DbRawCommand> Translate(List <object> dbQueryables) { ITranslateContext context = null; var sqlList = new List <DbRawCommand>(); foreach (var obj in dbQueryables) { if (obj == null) { continue; } if (obj is IDbQueryable) { DbQueryable dbQuery = (DbQueryable)obj; dbQuery.Parameterized = true; if (context == null) { context = this.CreateTranslateContext(dbQuery.DbContext); } if (context.Parameters == null) { context.Parameters = new List <IDbDataParameter>(8); } var cmd = dbQuery.Translate(0, true, context); sqlList.Add(cmd); if (cmd.Parameters != null && cmd.Parameters.Count > 1000) { // 1000个参数,就要重新分批 sqlList.Add(null); context = this.CreateTranslateContext(dbQuery.DbContext); context.Parameters = new List <IDbDataParameter>(8); } } else if (obj is DbRawSql) { DbRawSql rawSql = (DbRawSql)obj; if (context == null) { context = this.CreateTranslateContext(rawSql.DbContext); } if (context.Parameters == null) { context.Parameters = new List <IDbDataParameter>(8); } // 解析参数 object[] args = null; if (rawSql.Parameters != null) { args = rawSql.Parameters.Select(x => this.Constor.GetSqlValue(x, context)).ToArray(); } string sql = rawSql.CommandText; if (args != null && args.Length > 0) { sql = string.Format(sql, args); } var cmd = new DbRawCommand(sql, context.Parameters, CommandType.Text); sqlList.Add(cmd); if (cmd.Parameters != null && cmd.Parameters.Count > 1000) { // 1000个参数,就要重新分批 sqlList.Add(null); context = this.CreateTranslateContext(rawSql.DbContext); context.Parameters = new List <IDbDataParameter>(8); } } else if (obj is string) { string sql = obj.ToString(); sqlList.Add(new DbRawCommand(sql)); } else { // 解析批量插入操作 List <IDbQueryable> bulkList = obj as List <IDbQueryable>; if (bulkList != null && bulkList.Count > 0) { this.TranslateBulk(sqlList, bulkList); } } } return(sqlList); }
// LEFT OR INNER JOIN private void AppendLfInJoin(ISqlBuilder jf, ISqlBuilder on, DbExpression dbExpression) { DbQueryable dbQueryable = (DbQueryable)((dbExpression.Expressions[0] as ConstantExpression).Value); dbQueryable.Parameterized = jf.Parameterized; if (!usedKeyword) { jf.AppendNewLine(); jf.Append(_keywordName); usedKeyword = true; } else { jf.Append(','); jf.AppendNewLine(); jf.Append(_pad); } if (dbQueryable.DbExpressions.Count == 1 && dbQueryable.DbExpressions[0].DbExpressionType == DbExpressionType.GetTable) { // 区别 GetTable 有三个重载 var expressions = dbQueryable.DbExpressions[0].Expressions; if (expressions.Length == 2 && ((expressions[0] as ConstantExpression).Value as string) != null) { string text = (expressions[0] as ConstantExpression).Value as string; object[] @params = (expressions[1] as ConstantExpression).Value as object[]; var provider = ((DbQueryProvider)jf.Provider); var context = jf.TranslateContext; // 解析参数 object[] args = null; if (@params != null) { args = @params.Select(x => provider.Constor.GetSqlValue(x, context)).ToArray(); } string sql = text; if (args != null && args.Length > 0) { sql = string.Format(sql, args); } var cmd = new DbRawCommand(sql, context.Parameters, CommandType.Text); jf.Append("( "); jf.Append(_dbExpressionType == DbExpressionType.Delete ? cmd.CommandText.TrimStart() : cmd.CommandText); jf.Append(')'); } else { Type type = dbExpression.Expressions[0].Type.GetGenericArguments()[0]; var typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo(type); jf.AppendTable(typeRuntime.TableSchema, typeRuntime.TableName, typeRuntime.IsTemporary); } } else { // 嵌套 var cmd = dbQueryable.Translate(jf.Indent + _dbExpressionType == DbExpressionType.Delete ? 2 : 1, false, jf.TranslateContext); jf.Append("( "); jf.Append(_dbExpressionType == DbExpressionType.Delete ? cmd.CommandText.TrimStart() : cmd.CommandText); jf.Append(')'); } LambdaExpression left = dbExpression.Expressions[1] as LambdaExpression; LambdaExpression right = dbExpression.Expressions[2] as LambdaExpression; // t0(t1) string alias = !(left.Body.NodeType == ExpressionType.New || left.Body.NodeType == ExpressionType.MemberInit) ? _ag.GetTableAlias(dbExpression.Expressions[2]) : _ag.GetTableAlias(right.Parameters[0]); jf.Append(' '); jf.Append(alias); if (on.Length > 0) { on.Append(" AND "); } if (left.Body.NodeType == ExpressionType.New) { NewExpression body1 = left.Body as NewExpression; NewExpression body2 = right.Body as NewExpression; for (int index = 0; index < body1.Arguments.Count; ++index) { on.AppendMember(_ag, body1.Arguments[index]); on.Append(" = "); on.AppendMember(_ag, body2.Arguments[index]); if (index < body1.Arguments.Count - 1) { on.Append(" AND "); } } } else if (left.Body.NodeType == ExpressionType.MemberInit) { MemberInitExpression body1 = left.Body as MemberInitExpression; MemberInitExpression body2 = right.Body as MemberInitExpression; for (int index = 0; index < body1.Bindings.Count; ++index) { on.AppendMember(_ag, (body1.Bindings[index] as MemberAssignment).Expression); on.Append(" = "); on.AppendMember(_ag, (body2.Bindings[index] as MemberAssignment).Expression); if (index < body1.Bindings.Count - 1) { on.Append(" AND "); } } } else { on.AppendMember(_ag, left.Body.ReduceUnary()); on.Append(" = "); on.AppendMember(_ag, right.Body.ReduceUnary()); } }