示例#1
0
        public override void Run(DatabaseType dbType)
        {
            var context = _newContext();

            // 声明表变量
            var typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo <SqlServerModel.JoinKey>();

            context.AddQuery(string.Format("DECLARE {0} [{1}]", typeRuntime.TableName, typeRuntime.TableName.TrimStart('@')));
            List <SqlServerModel.JoinKey> keys = new List <SqlServerModel.JoinKey>
            {
                new SqlServerModel.JoinKey {
                    Key1 = 2
                },
                new SqlServerModel.JoinKey {
                    Key1 = 3
                },
            };

            // 向表变量写入数据
            context.Insert <SqlServerModel.JoinKey>(keys);
            // 像物理表一样操作表变量
            var query =
                from a in context.GetTable <Model.Client>()
                join b in context.GetTable <SqlServerModel.JoinKey>() on a.ClientId equals b.Key1
                select a;

            context.AddQuery(query);
            // 提交查询结果
            List <Model.Client> result = null;

            context.SubmitChanges(out result);


            base.Run(dbType);
        }
示例#2
0
        // 创建 DELETE 命令
        protected override DbCommandDefinition ParseDeleteCommand <T>(DbQueryableInfo_Delete <T> dQuery, List <IDbDataParameter> parameters = null)
        {
            TypeRuntimeInfo typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo <T>();
            ISqlBuilder     builder     = this.CreateSqlBuilder(parameters);
            bool            useKey      = false;

            builder.Append("DELETE FROM ");
            builder.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary);
            builder.Append(" t0 ");

            if (dQuery.Entity != null)
            {
                object entity = dQuery.Entity;

                builder.AppendNewLine();
                builder.Append("WHERE ");

                foreach (var kv in typeRuntime.Invokers)
                {
                    MemberInvokerBase invoker = kv.Value;
                    var column = invoker.Column;

                    if (column != null && column.IsKey)
                    {
                        useKey = true;
                        var value = invoker.Invoke(entity);
                        var seg   = builder.GetSqlValue(value, column);
                        builder.AppendMember("t0", invoker.Member.Name);
                        builder.Append(" = ");
                        builder.Append(seg);
                        builder.Append(" AND ");
                    }
                    ;
                }
                builder.Length -= 5;

                if (!useKey)
                {
                    throw new XFrameworkException("Delete<T>(T value) require T must have key column.");
                }
            }
            else if (dQuery.SelectInfo != null)
            {
                TableAliasCache aliases = this.PrepareAlias <T>(dQuery.SelectInfo);
                var             cmd2    = new OracleDbCommandDefinition_Delete(this, aliases, builder.Parameters);
                cmd2.HaveListNavigation = dQuery.SelectInfo.HaveListNavigation;

                var visitor0 = new OracleJoinExpressionVisitor(this, aliases, dQuery.SelectInfo.Join, dQuery.SelectInfo.Where);
                visitor0.Write(cmd2);

                var visitor1 = new OracleWhereExpressionVisitor(this, aliases, dQuery.SelectInfo.Where);
                visitor1.Write(cmd2.WhereFragment);
                cmd2.AddNavMembers(visitor1.NavMembers);
                builder.Append(cmd2.CommandText);
            }

            builder.Append(';');
            return(new DbCommandDefinition(builder.ToString(), builder.Parameters, System.Data.CommandType.Text));
        }
示例#3
0
        // 创建 DELETE 命令
        protected override CommandDefine ParseDeleteCommand <T>(DbQueryableInfo_Delete <T> qDelete)
        {
            var        rInfo   = TypeRuntimeInfoCache.GetRuntimeInfo <T>();
            SqlBuilder builder = new SqlBuilder(this.EscCharLeft, this.EscCharRight);
            bool       useKey  = false;

            builder.Append("DELETE t0 FROM ");
            builder.AppendMember(rInfo.TableName);
            builder.Append(" t0 ");

            if (qDelete.Entity != null)
            {
                object entity = qDelete.Entity;

                builder.AppendNewLine();
                builder.Append("WHERE ");

                foreach (var kv in rInfo.Wrappers)
                {
                    var wrapper = kv.Value as MemberAccessWrapper;
                    var column  = wrapper.Column;

                    if (column != null && column.IsKey)
                    {
                        useKey = true;
                        var value = wrapper.Get(entity);
                        var seg   = ExpressionVisitorBase.GetSqlValue(value);
                        builder.AppendMember("t0", wrapper.Member.Name);
                        builder.Append(" = ");
                        builder.Append(seg);
                        builder.Append(" AND ");
                    }
                    ;
                }
                builder.Length -= 5;

                if (!useKey)
                {
                    throw new XfwException("Delete<T>(T value) require T must have key column.");
                }
            }
            else if (qDelete.SelectInfo != null)
            {
                TableAliasCache aliases = this.PrepareAlias <T>(qDelete.SelectInfo);
                var             sc      = new CommandDefine_Select.Builder(this.EscCharLeft, this.EscCharRight, aliases);

                ExpressionVisitorBase visitor = new JoinExpressionVisitor(this, aliases, qDelete.SelectInfo.Join);
                visitor.Write(sc.JoinFragment);

                visitor = new WhereExpressionVisitor(this, aliases, qDelete.SelectInfo.Where);
                visitor.Write(sc.WhereFragment);
                sc.AddNavigation(visitor.Navigations);

                builder.Append(sc.Command);
            }

            return(new CommandDefine(builder.ToString(), null, System.Data.CommandType.Text)); //builder.ToString();
        }
示例#4
0
        /// <summary>
        /// 创建 DELETE 命令
        /// </summary>
        /// <param name="tree">查询语义</param>
        /// <param name="context">解析SQL命令上下文</param>
        /// <returns></returns>
        protected override DbRawCommand TranslateDeleteCommand <T>(DbQueryDeleteTree tree, ITranslateContext context)
        {
            ISqlBuilder builder     = this.CreateSqlBuilder(context);
            var         typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo <T>();

            builder.Append("DELETE FROM ");
            builder.AppendTable(typeRuntime.TableSchema, typeRuntime.TableName, typeRuntime.IsTemporary);
            builder.Append(" t0 ");

            if (tree.Entity != null)
            {
                if (typeRuntime.KeyMembers == null || typeRuntime.KeyMembers.Count == 0)
                {
                    throw new XFrameworkException("Delete<T>(T value) require entity must have key column.");
                }

                object entity = tree.Entity;

                builder.AppendNewLine();
                builder.Append("WHERE ");

                foreach (FieldAccessorBase m in typeRuntime.KeyMembers)
                {
                    var value         = m.Invoke(entity);
                    var sqlExpression = this.Constor.GetSqlValue(value, context, m.Column);

                    builder.AppendMember("t0", m.Member, typeRuntime.Type);
                    builder.Append(" = ");
                    builder.Append(sqlExpression);
                    builder.Append(" AND ");
                }
                builder.Length -= 5;
            }
            else if (tree.Select != null)
            {
                AliasGenerator ag  = this.PrepareTableAlias(tree.Select, context.AliasPrefix);
                var            cmd = new NpgDbSelectCommand(context, ag, DbExpressionType.Delete, tree.Select.SelectHasMany);

                if (tree.Select.Joins != null)
                {
                    var visitor = new NpgJoinExpressionVisitor(ag, cmd.JoinFragment, DbExpressionType.Delete, cmd);
                    visitor.Visit(tree.Select.Joins);
                }

                if (tree.Select.Wheres != null)
                {
                    var visitor = new NpgWhereExpressionVisitor(ag, cmd.WhereFragment);
                    visitor.Visit(tree.Select.Wheres);
                    cmd.AddNavMembers(visitor.NavMembers);
                }

                builder.Append(cmd.CommandText);
            }

            builder.Append(';');
            return(new DbRawCommand(builder.ToString(), builder.TranslateContext != null ? builder.TranslateContext.Parameters : null, System.Data.CommandType.Text));
        }
        // 创建 DELETE 命令
        protected override Command ResolveDeleteCommand <T>(DbQueryableInfo_Delete <T> dbQuery, ResolveToken token)
        {
            ISqlBuilder     builder     = this.CreateSqlBuilder(token);
            TypeRuntimeInfo typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo <T>();
            IDbQueryable    sourceQuery = dbQuery.SourceQuery;
            var             context     = (SqlServerDbContext)sourceQuery.DbContext;

            builder.Append("DELETE t0 FROM ");
            builder.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary);
            builder.Append(" t0 ");

            if (dbQuery.Entity != null)
            {
                if (typeRuntime.KeyInvokers == null || typeRuntime.KeyInvokers.Count == 0)
                {
                    throw new XFrameworkException("Delete<T>(T value) require entity must have key column.");
                }

                object entity = dbQuery.Entity;

                builder.AppendNewLine();
                builder.Append("WHERE ");

                foreach (var invoker in typeRuntime.KeyInvokers)
                {
                    var column = invoker.Column;
                    var value  = invoker.Invoke(entity);
                    var seg    = this.DbValue.GetSqlValue(value, token, column);

                    builder.AppendMember("t0", invoker.Member.Name);
                    builder.Append(" = ");
                    builder.Append(seg);
                    builder.Append(" AND ");
                }
                builder.Length -= 5;
            }
            else if (dbQuery.SelectInfo != null)
            {
                TableAliasCache aliases = this.PrepareTableAlias <T>(dbQuery.SelectInfo, token);
                var             cmd2    = new SqlServerMapperCommand(context, aliases, token)
                {
                    HasMany = dbQuery.SelectInfo.HasMany
                };

                ExpressionVisitorBase visitor = new SqlServerJoinExpressionVisitor(context, aliases, dbQuery.SelectInfo.Joins);
                visitor.Write(cmd2.JoinFragment);

                visitor = new WhereExpressionVisitor(this, aliases, dbQuery.SelectInfo.Condtion);
                visitor.Write(cmd2.WhereFragment);
                cmd2.AddNavMembers(visitor.NavMembers);

                builder.Append(cmd2.CommandText);
            }

            return(new Command(builder.ToString(), builder.Token != null ? builder.Token.Parameters : null, System.Data.CommandType.Text));
        }
示例#6
0
        // 创建 DELETE 命令
        protected override Command ParseDeleteCommand <T>(DbQueryableInfo_Delete <T> dQueryInfo, ResolveToken token)
        {
            TypeRuntimeInfo typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo <T>();
            ISqlBuilder     builder     = this.CreateSqlBuilder(token);

            builder.Append("DELETE FROM ");
            builder.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary);
            builder.Append(" t0 ");

            if (dQueryInfo.Entity != null)
            {
                if (typeRuntime.KeyInvokers == null || typeRuntime.KeyInvokers.Count == 0)
                {
                    throw new XFrameworkException("Delete<T>(T value) require entity must have key column.");
                }

                object entity = dQueryInfo.Entity;

                builder.AppendNewLine();
                builder.Append("WHERE ");

                foreach (var invoker in typeRuntime.KeyInvokers)
                {
                    var column = invoker.Column;
                    var value  = invoker.Invoke(entity);
                    var seg    = this.DbValue.GetSqlValue(value, token, column);

                    builder.AppendMember("t0", invoker.Member.Name);
                    builder.Append(" = ");
                    builder.Append(seg);
                    builder.Append(" AND ");
                }
                builder.Length -= 5;
            }
            else if (dQueryInfo.SelectInfo != null)
            {
                TableAliasCache aliases = this.PrepareAlias <T>(dQueryInfo.SelectInfo, token);
                var             cmd2    = new NpgNavigationCommand(this, aliases, DbExpressionType.Delete, token)
                {
                    HasMany = dQueryInfo.SelectInfo.HasMany
                };

                var visitor0 = new NpgJoinExpressionVisitor(this, aliases, dQueryInfo.SelectInfo.Joins, DbExpressionType.Delete);
                visitor0.Write(cmd2);

                var visitor1 = new NpgWhereExpressionVisitor(this, aliases, dQueryInfo.SelectInfo.WhereExpression);
                visitor1.Write(cmd2.WhereFragment);
                cmd2.AddNavMembers(visitor1.NavMembers);
                builder.Append(cmd2.CommandText);
            }

            builder.Append(';');
            return(new Command(builder.ToString(), builder.Token != null ? builder.Token.Parameters : null, System.Data.CommandType.Text));
        }
示例#7
0
        /// <summary>
        /// 创建 DELETE 命令
        /// </summary>
        /// <param name="dbQuery">查询语义</param>
        /// <param name="token">解析上下文</param>
        /// <returns></returns>
        protected override RawCommand ResolveDeleteCommand <T>(IDbQueryableInfo_Delete dbQuery, ResolveToken token)
        {
            ISqlBuilder builder     = this.CreateSqlBuilder(token);
            var         typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo <T>();

            builder.Append("DELETE FROM ");
            builder.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary);
            builder.Append(" t0 ");

            if (dbQuery.Entity != null)
            {
                if (typeRuntime.KeyMembers == null || typeRuntime.KeyMembers.Count == 0)
                {
                    throw new XFrameworkException("Delete<T>(T value) require entity must have key column.");
                }

                object entity = dbQuery.Entity;

                builder.AppendNewLine();
                builder.Append("WHERE ");

                foreach (var m in typeRuntime.KeyMembers)
                {
                    var column = m.Column;
                    var value  = m.Invoke(entity);
                    var seg    = this.DbValue.GetSqlValue(value, token, column);

                    builder.AppendMember("t0", m.Member.Name);
                    builder.Append(" = ");
                    builder.Append(seg);
                    builder.Append(" AND ");
                }
                builder.Length -= 5;
            }
            else if (dbQuery.Query != null)
            {
                TableAlias aliases = this.PrepareTableAlias(dbQuery.Query, token);
                var        cmd     = new NpgMappingDbCommand(this, aliases, DbExpressionType.Delete, token)
                {
                    HasMany = dbQuery.Query.HasMany
                };

                var visitor = new NpgJoinExpressionVisitor(this, aliases, dbQuery.Query.Joins, DbExpressionType.Delete);
                visitor.Write(cmd);

                var visitor_ = new NpgWhereExpressionVisitor(this, aliases, dbQuery.Query.Where);
                visitor_.Write(cmd.WhereFragment);
                cmd.AddNavMembers(visitor_.NavMembers);
                builder.Append(cmd.CommandText);
            }

            builder.Append(';');
            return(new RawCommand(builder.ToString(), builder.Token != null ? builder.Token.Parameters : null, System.Data.CommandType.Text));
        }
示例#8
0
        /// <summary>
        /// 访问 IDbQueryable.Contains 方法
        /// </summary>
        /// <param name="m">字段或属性表达式</param>
        protected override Expression VisitQueryableContains(MethodCallExpression m)
        {
            ResolveToken token    = _builder.Token;
            IDbQueryable subQuery = m.Arguments[0].Evaluate().Value as IDbQueryable;

            subQuery.Parameterized = _builder.Parameterized;

            var cmd = subQuery.Resolve(_builder.Indent + 1, false, token != null ? new ResolveToken
            {
                Parameterized = token.Parameterized,
                Parameters    = token.Parameters,
                AliasPrefix   = "s",
                DbContext     = token.DbContext
            } : null) as MappingCommand;

            if (this.NotOperands != null && this.NotOperands.Contains(m))
            {
                _builder.Append("NOT ");
            }
            _builder.Append("EXISTS(");
            _builder.Append(cmd.CommandText);

            if (((MappingCommand)cmd).WhereFragment.Length > 0)
            {
                _builder.Append(" AND ");
            }
            else
            {
                _builder.Append("WHERE ");
            }
            _builder.Append(cmd.PickColumnText);
            _builder.Append(" = ");

            // exists 不能用别名
            if (token != null && token.Extendsions != null && token.Extendsions.ContainsKey("SQLiteDelete"))
            {
                var typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo(((MemberExpression)m.Arguments[1]).Expression.Type);
                _builder.AppendMember(typeRuntime.TableName);
                _builder.Append('.');
            }

            _visitor.Visit(m.Arguments[1]);
            _builder.Append(")");

            return(m);
        }
示例#9
0
        /// <summary>
        /// 访问 IDbQueryable.Contains 方法
        /// </summary>
        /// <param name="m">字段或属性表达式</param>
        protected override Expression VisitQueryableContains(MethodCallExpression m)
        {
            ITranslateContext context  = _builder.TranslateContext;
            DbQueryable       subquery = m.Arguments[0].Evaluate().Value as DbQueryable;

            subquery.Parameterized = _builder.Parameterized;

            // 可能会有几级嵌套,这里用 Builder.Ident 标记是第几层级
            string[] subs       = new[] { "p", "u", "v", "w", "x" };
            var      newContext = context != null?TranslateContext.Copy(context, subs[_builder.Indent]) : null;

            bool isDelete = context != null && ((SQLiteTranslateContext)context).IsDelete;
            var  cmd      = subquery.Translate(_builder.Indent + 1, false, newContext) as DbSelectCommand;

            if (this.NotOperands != null && this.NotOperands.Contains(m))
            {
                _builder.Append("NOT ");
            }
            _builder.Append("EXISTS(");
            _builder.Append(cmd.CommandText);

            if (((DbSelectCommand)cmd).WhereFragment.Length > 0)
            {
                _builder.Append(" AND ");
            }
            else
            {
                _builder.Append("WHERE ");
            }
            _builder.Append(cmd.SelectedColumnText);
            _builder.Append(" = ");

            // exists 不能用别名
            if (isDelete)
            {
                var typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo(((MemberExpression)m.Arguments[1]).Expression.Type);
                _builder.AppendTable(typeRuntime.TableSchema, typeRuntime.TableName, typeRuntime.IsTemporary);
                _builder.Append('.');
            }

            _visitor.Visit(m.Arguments[1]);
            _builder.Append(")");

            return(m);
        }
示例#10
0
        // 获取实例的属性值
        private static IDictionary <ValidationContext, object> GetPropertyValues(object instance, ValidationContext context)
        {
            IDictionary <ValidationContext, object> result = new Dictionary <ValidationContext, object>();
            TypeRuntimeInfo typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo(instance.GetType());

            foreach (var invoker in typeRuntime.Invokers)
            {
                if (invoker.MemberType == System.Reflection.MemberTypes.Property)
                {
                    ValidationContext context2 = XFrameworkValidator.CreateValidationContext(instance, context);
                    context2.MemberName = invoker.Member.Name;

                    if (XFrameworkValidator._store.GetPropertyValidationAttributes(context2).Any <ValidationAttribute>())
                    {
                        result.Add(context2, invoker.Invoke(instance));
                    }
                }
            }

            return(result);
        }
            // Cross Join
            private void AppendCrossJoin(ISqlBuilder builder, DbExpression exp, TableAliasCache aliases)
            {
                var  lambdaExp   = exp.Expressions[1] as LambdaExpression;
                Type type        = lambdaExp.Parameters[1].Type;
                var  typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo(type);
                bool withNoLock  = !typeRuntime.IsTemporary && _context.NoLock && !string.IsNullOrEmpty(_provider.WidthNoLock);

                builder.Append(' ');
                builder.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary);

                string alias = aliases.GetTableAlias(lambdaExp.Parameters[1]);

                builder.Append(' ');
                builder.Append(alias);
                builder.Append(' ');

                if (withNoLock)
                {
                    builder.Append(_provider.WidthNoLock);
                    builder.Append(' ');
                }
            }
示例#12
0
        // 创建 INSRT 命令
        protected override Command ParseInsertCommand <T>(DbQueryableInfo_Insert <T> nQueryInfo, ParserToken token)
        {
            ISqlBuilder     builder     = this.CreateSqlBuilder(token);
            TypeRuntimeInfo typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo <T>();
            TableAliasCache aliases     = new TableAliasCache();

            if (nQueryInfo.Entity != null)
            {
                object      entity         = nQueryInfo.Entity;
                ISqlBuilder columnsBuilder = this.CreateSqlBuilder(token);
                ISqlBuilder valuesBuilder  = this.CreateSqlBuilder(token);

                // 指定插入列
                Dictionary <string, MemberInvokerBase> invokers = typeRuntime.Invokers;
                if (nQueryInfo.EntityColumns != null && nQueryInfo.EntityColumns.Count > 0)
                {
                    invokers = new Dictionary <string, MemberInvokerBase>();
                    for (int i = 0; i < nQueryInfo.EntityColumns.Count; i++)
                    {
                        Expression curExpr = nQueryInfo.EntityColumns[i];
                        if (curExpr.NodeType == ExpressionType.Lambda)
                        {
                            curExpr = (curExpr as LambdaExpression).Body.ReduceUnary();
                        }
                        if (curExpr.NodeType != ExpressionType.MemberAccess)
                        {
                            throw new XFrameworkException("ERR {0}: Only support MemberAccess expression.", nQueryInfo.EntityColumns[i]);
                        }

                        MemberExpression member = curExpr as MemberExpression;
                        string           name   = member.Member.Name;
                        invokers[name] = typeRuntime.Invokers[name];
                    }
                }

                foreach (var kv in invokers)
                {
                    MemberInvokerBase invoker = kv.Value;
                    var column = invoker.Column;
                    if (column != null && column.NoMapped)
                    {
                        continue;
                    }
                    if (invoker.ForeignKey != null)
                    {
                        continue;
                    }
                    if (invoker.Column != null && column.DbType is SqlDbType && (SqlDbType)column.DbType == SqlDbType.Timestamp)
                    {
                        continue;                                                                                                          // 行版本号
                    }
                    if (invoker.Member.MemberType == System.Reflection.MemberTypes.Method)
                    {
                        continue;
                    }

                    if (invoker != nQueryInfo.AutoIncrement)
                    {
                        columnsBuilder.AppendMember(invoker.Member.Name);
                        columnsBuilder.Append(',');

                        var    value = invoker.Invoke(entity);
                        string seg   = builder.GetSqlValueWidthDefault(value, column);
                        valuesBuilder.Append(seg);
                        valuesBuilder.Append(',');
                    }
                }

                columnsBuilder.Length -= 1;
                valuesBuilder.Length  -= 1;

                if (nQueryInfo.Bulk == null || !nQueryInfo.Bulk.OnlyValue)
                {
                    builder.Append("INSERT INTO ");
                    builder.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary);
                    builder.Append('(');
                    builder.Append(columnsBuilder);
                    builder.Append(')');
                    builder.AppendNewLine();
                    builder.AppendNewTab();
                    builder.Append("VALUES");
                }

                if (nQueryInfo.Bulk != null && nQueryInfo.Bulk.OnlyValue)
                {
                    builder.AppendNewTab();
                }
                builder.Append('(');
                builder.Append(valuesBuilder);
                builder.Append(')');
                if (nQueryInfo.Bulk != null && !nQueryInfo.Bulk.IsEndPos)
                {
                    builder.Append(",");
                    builder.AppendNewLine();
                }

                if (nQueryInfo.Bulk == null && nQueryInfo.AutoIncrement != null)
                {
                    builder.AppendNewLine();
                    builder.Append("SELECT SCOPE_IDENTITY()");
                    builder.AppendAs(Constant.AUTOINCREMENTNAME);
                }
            }
            else if (nQueryInfo.SelectInfo != null)
            {
                builder.Append("INSERT INTO ");
                builder.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary);
                builder.Append('(');

                int           i    = 0;
                SelectCommand cmd2 = this.ParseSelectCommand(nQueryInfo.SelectInfo, 0, true, token) as SelectCommand;
                foreach (var kvp in cmd2.Columns)
                {
                    builder.AppendMember(kvp.Key);
                    if (i < cmd2.Columns.Count - 1)
                    {
                        builder.Append(',');
                    }
                    i++;
                }

                builder.Append(')');
                builder.AppendNewLine();
                builder.Append(cmd2.CommandText);
            }

            return(new Command(builder.ToString(), builder.Token != null ? builder.Token.Parameters : null, System.Data.CommandType.Text));
        }
示例#13
0
        // 创建 SELECT 命令
        protected override Command ParseSelectCommand <T>(DbQueryableInfo_Select <T> sQueryInfo, int indent, bool isOuter, ParserToken token)
        {
            // 说明:
            // 1.OFFSET 前必须要有 'ORDER BY',即 'Skip' 子句前必须使用 'OrderBy' 子句
            // 2.在有统计函数的<MAX,MIN...>情况下,如果有 'Distinct' 'GroupBy' 'Skip' 'Take' 子句,则需要使用嵌套查询
            // 3.'Any' 子句将翻译成 IF EXISTS...
            // 4.分组再分页时需要使用嵌套查询,此时子查询不需要 'OrderBy' 子句,但最外层则需要
            // 5.'Skip' 'Take' 子句视为语义结束符,在其之后的子句将使用嵌套查询
            // 6.导航属性中有 1:n 关系的,需要使用嵌套查询,否则分页查询会有问题

            // 导航属性中有1:n关系,只统计主表
            // 例:AccountList = a.Client.AccountList,
            var subQueryInfo = sQueryInfo.SubQueryInfo as DbQueryableInfo_Select <T>;

            if (sQueryInfo.HasManyNavigation && subQueryInfo != null && subQueryInfo.StatisExpression != null)
            {
                sQueryInfo = subQueryInfo;
            }

            bool useStatis = sQueryInfo.StatisExpression != null;
            // 没有统计函数或者使用 'Skip' 子句,则解析OrderBy
            // 导航属性如果使用嵌套,除非有 TOP 或者 OFFSET 子句,否则不能用ORDER BY
            string alias0      = token != null && !string.IsNullOrEmpty(token.TableAliasName) ? (token.TableAliasName + "0") : "t0";
            bool   useSubQuery = sQueryInfo.HaveDistinct || sQueryInfo.GroupByExpression != null || sQueryInfo.Skip > 0 || sQueryInfo.Take > 0;
            bool   useOrderBy  = (!useStatis || sQueryInfo.Skip > 0) && !sQueryInfo.HaveAny && (!sQueryInfo.ResultByManyNavigation || (sQueryInfo.Skip > 0 || sQueryInfo.Take > 0));

            IDbQueryable    dbQueryable = sQueryInfo.SourceQuery;
            TableAliasCache aliases     = this.PrepareAlias <T>(sQueryInfo, token);
            SelectCommand   cmd         = new SelectCommand(this, aliases, token)
            {
                HasManyNavigation = sQueryInfo.HasManyNavigation
            };
            ISqlBuilder jf = cmd.JoinFragment;
            ISqlBuilder wf = cmd.WhereFragment;

            jf.Indent = indent;

            #region 嵌套查询

            if (useStatis && useSubQuery)
            {
                // SELECT
                jf.Append("SELECT ");
                jf.AppendNewLine();

                // SELECT COUNT(1)
                var visitor2 = new StatisExpressionVisitor(this, aliases, sQueryInfo.StatisExpression, sQueryInfo.GroupByExpression, alias0);
                visitor2.Write(jf);
                cmd.AddNavMembers(visitor2.NavMembers);

                // SELECT COUNT(1) FROM
                jf.AppendNewLine();
                jf.Append("FROM ( ");

                indent   += 1;
                jf.Indent = indent;
            }

            #endregion

            #region  择子句

            // SELECT 子句
            if (jf.Indent > 0)
            {
                jf.AppendNewLine();
            }
            if (sQueryInfo.HaveAny)
            {
                jf.Append("IF EXISTS(");
                indent   += 1;
                jf.Indent = indent;
                jf.AppendNewLine();
            }

            jf.Append("SELECT ");

            if (useStatis && !useSubQuery)
            {
                // 如果有统计函数,并且不是嵌套的话,则直接使用SELECT <MAX,MIN...>,不需要解析选择的字段
                jf.AppendNewLine();
                var visitor2 = new StatisExpressionVisitor(this, aliases, sQueryInfo.StatisExpression, sQueryInfo.GroupByExpression);
                visitor2.Write(jf);
                cmd.AddNavMembers(visitor2.NavMembers);
            }
            else
            {
                // DISTINCT 子句
                if (sQueryInfo.HaveDistinct)
                {
                    jf.Append("DISTINCT ");
                }
                // TOP 子句
                if (sQueryInfo.Take > 0 && sQueryInfo.Skip == 0)
                {
                    jf.AppendFormat("TOP({0})", jf.GetSqlValue(sQueryInfo.Take));
                }
                // Any
                if (sQueryInfo.HaveAny)
                {
                    jf.Append("TOP 1 1");
                }

                #region 字段

                if (!sQueryInfo.HaveAny)
                {
                    // SELECT 范围
                    var visitor2 = new ColumnExpressionVisitor(this, aliases, sQueryInfo);
                    visitor2.Write(jf);

                    cmd.Columns     = visitor2.Columns;
                    cmd.Navigations = visitor2.Navigations;
                    cmd.AddNavMembers(visitor2.NavMembers);
                }

                #endregion
            }

            #endregion

            #region 顺序解析

            // FROM 子句
            jf.AppendNewLine();
            jf.Append("FROM ");
            if (sQueryInfo.SubQueryInfo != null)
            {
                // 子查询
                jf.Append('(');
                Command cmd2 = this.ParseSelectCommand <T>(sQueryInfo.SubQueryInfo as DbQueryableInfo_Select <T>, indent + 1, false, token);
                jf.Append(cmd2.CommandText);
                jf.AppendNewLine();
                jf.Append(") ");
                jf.Append(alias0);
                jf.Append(' ');
            }
            else
            {
                var typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo(sQueryInfo.FromType);
                jf.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary);
                jf.Append(' ');
                jf.Append(alias0);
                jf.Append(' ');
                SqlDbContext context = (SqlDbContext)dbQueryable.DbContext;
                if (context.NoLock && !string.IsNullOrEmpty(this._widthNoLock))
                {
                    jf.Append(this._widthNoLock);
                }
            }

            // LEFT<INNER> JOIN 子句
            ExpressionVisitorBase visitor = new JoinExpressionVisitor(this, aliases, sQueryInfo.Joins);
            visitor.Write(jf);

            wf.Indent = jf.Indent;

            // WHERE 子句
            visitor = new WhereExpressionVisitor(this, aliases, sQueryInfo.WhereExpression);
            visitor.Write(wf);
            cmd.AddNavMembers(visitor.NavMembers);

            // GROUP BY 子句
            visitor = new GroupByExpressionVisitor(this, aliases, sQueryInfo.GroupByExpression);
            visitor.Write(wf);
            cmd.AddNavMembers(visitor.NavMembers);

            // HAVING 子句
            visitor = new HavingExpressionVisitor(this, aliases, sQueryInfo.HavingExpression, sQueryInfo.GroupByExpression);
            visitor.Write(wf);
            cmd.AddNavMembers(visitor.NavMembers);

            // ORDER 子句
            if (sQueryInfo.OrderBys.Count > 0 && useOrderBy)
            {
                visitor = new OrderByExpressionVisitor(this, aliases, sQueryInfo.OrderBys, sQueryInfo.GroupByExpression);
                visitor.Write(wf);
                cmd.AddNavMembers(visitor.NavMembers);
            }

            #endregion

            #region 分页查询

            if (sQueryInfo.Skip > 0)
            {
                if (sQueryInfo.OrderBys.Count == 0)
                {
                    throw new XFrameworkException("The method 'OrderBy' must be called before 'Skip'.");
                }
                wf.AppendNewLine();
                wf.Append("OFFSET ");
                wf.Append(wf.GetSqlValue(sQueryInfo.Skip));
                wf.Append(" ROWS");

                if (sQueryInfo.Take > 0)
                {
                    wf.Append(" FETCH NEXT ");
                    wf.Append(wf.GetSqlValue(sQueryInfo.Take));
                    wf.Append(" ROWS ONLY ");
                }
            }

            #endregion

            #region 嵌套查询

            if (useStatis && useSubQuery)
            {
                cmd.Convergence();
                indent   -= 1;
                jf.Indent = indent;
                jf.AppendNewLine();
                jf.Append(") ");
                jf.Append(alias0);
                jf.Append(' ');
            }

            #endregion

            #region 嵌套导航

            if (sQueryInfo.HasManyNavigation && subQueryInfo.StatisExpression == null && subQueryInfo != null && subQueryInfo.OrderBys.Count > 0 && !(subQueryInfo.Skip > 0 || subQueryInfo.Take > 0))
            {
                // TODO Include 从表,没分页,OrderBy 报错
                cmd.Convergence();
                visitor = new OrderByExpressionVisitor(this, aliases, subQueryInfo.OrderBys);
                visitor.Write(jf);
            }

            #endregion

            #region 并集查询

            // UNION 子句
            if (sQueryInfo.Unions != null && sQueryInfo.Unions.Count > 0)
            {
                cmd.Convergence();
                for (int index = 0; index < sQueryInfo.Unions.Count; index++)
                {
                    jf.AppendNewLine();
                    jf.Append("UNION ALL");
                    if (indent == 0)
                    {
                        jf.AppendNewLine();
                    }
                    Command cmd2 = this.ParseSelectCommand <T>(sQueryInfo.Unions[index] as DbQueryableInfo_Select <T>, indent, isOuter, token);
                    jf.Append(cmd2.CommandText);
                }
            }

            #endregion

            #region Any 子句

            // 'Any' 子句
            if (sQueryInfo.HaveAny)
            {
                cmd.Convergence();
                indent   -= 1;
                jf.Indent = indent;
                jf.AppendNewLine();
                jf.Append(") SELECT 1 ELSE SELECT 0");
            }

            #endregion

            return(cmd);
        }
        /// <summary>
        /// 创建 UPDATE 命令
        /// </summary>
        /// <param name="dbQuery">查询语义</param>
        /// <param name="token">解析上下文</param>
        /// <returns></returns>
        protected override RawCommand ResolveUpdateCommand <T>(IDbQueryableInfo_Update dbQuery, ResolveToken token)
        {
            var         context     = (SqlServerDbContext)token.DbContext;
            ISqlBuilder builder     = this.CreateSqlBuilder(token);
            var         typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo <T>();

            builder.Append("UPDATE t0 SET");
            builder.AppendNewLine();

            if (dbQuery.Entity != null)
            {
                object      entity    = dbQuery.Entity;
                ISqlBuilder seg_Where = this.CreateSqlBuilder(token);
                bool        useKey    = false;
                int         length    = 0;

                foreach (var m in typeRuntime.Members)
                {
                    var column = m.Column;
                    if (column != null && column.IsIdentity)
                    {
                        goto gotoLabel;                                      // Fix issue# 自增列同时又是主键
                    }
                    if (column != null && column.NoMapped)
                    {
                        continue;
                    }
                    if (m.Column != null && column.DbType is SqlDbType && (SqlDbType)column.DbType == SqlDbType.Timestamp)
                    {
                        continue;                                                                                                    // 行版本号
                    }
                    if (m.ForeignKey != null)
                    {
                        continue;
                    }
                    if (m.Member.MemberType == System.Reflection.MemberTypes.Method)
                    {
                        continue;
                    }

                    builder.AppendMember("t0", m.Member.Name);
                    builder.Append(" = ");

gotoLabel:
                    var value = m.Invoke(entity);
                    var seg = this.DbValue.GetSqlValueWidthDefault(value, token, column);

                    if (column == null || !column.IsIdentity)
                    {
                        builder.Append(seg);
                        length = builder.Length;
                        builder.Append(',');
                        builder.AppendNewLine();
                    }

                    if (column != null && column.IsKey)
                    {
                        useKey = true;
                        seg_Where.AppendMember("t0", m.Member.Name);
                        seg_Where.Append(" = ");
                        seg_Where.Append(seg);
                        seg_Where.Append(" AND ");
                    }
                }

                if (!useKey)
                {
                    throw new XFrameworkException("Update<T>(T value) require entity must have key column.");
                }

                builder.Length    = length;
                seg_Where.Length -= 5;

                builder.AppendNewLine();
                builder.Append("FROM ");
                builder.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary);
                builder.Append(" t0");

                builder.AppendNewLine();
                builder.Append("WHERE ");
                builder.Append(seg_Where);
            }
            else if (dbQuery.Expression != null)
            {
                TableAlias            aliases = this.PrepareTableAlias(dbQuery.Query, token);
                ExpressionVisitorBase visitor = null;
                visitor = new UpdateExpressionVisitor(this, aliases, dbQuery.Expression);
                visitor.Write(builder);

                builder.AppendNewLine();
                builder.Append("FROM ");
                builder.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary);
                builder.AppendAs("t0");

                var cmd = new SqlServerMappingCommand(context, aliases, token)
                {
                    HasMany = dbQuery.Query.HasMany
                };

                visitor = new SqlServerJoinExpressionVisitor(context, aliases, dbQuery.Query.Joins);
                visitor.Write(cmd.JoinFragment);

                visitor = new WhereExpressionVisitor(this, aliases, dbQuery.Query.Where);
                visitor.Write(cmd.WhereFragment);
                cmd.AddNavMembers(visitor.NavMembers);

                builder.Append(cmd.CommandText);
            }

            return(new RawCommand(builder.ToString(), builder.Token != null ? builder.Token.Parameters : null, System.Data.CommandType.Text));
        }
        RawCommand ResolveSelectCommandImpl(IDbQueryableInfo_Select dbQuery, int indent, bool isOuter, ResolveToken token)
        {
            // 说明:
            // 1.OFFSET 前必须要有 'ORDER BY',即 'Skip' 子句前必须使用 'OrderBy' 子句
            // 2.在有聚合函数的<MAX,MIN...>情况下,如果有 'Distinct' 'GroupBy' 'Skip' 'Take' 子句,则需要使用嵌套查询
            // 3.'Any' 子句将翻译成 IF EXISTS...
            // 4.分组再分页时需要使用嵌套查询,此时子查询不需要 'OrderBy' 子句,但最外层则需要
            // 5.'Skip' 'Take' 子句视为语义结束符,在其之后的子句将使用嵌套查询
            // 6.导航属性中有 1:n 关系的,需要使用嵌套查询,否则分页查询会有问题
            // 8.如果只有 Skip 没有 Take,则使用 Row_Number() Over()分页语法,其它使用 LIMIT ## OFFSET 语法


            // 导航属性中有1:n关系,只统计主表
            // 例:AccountList = a.Client.AccountList,
            var subQuery = dbQuery.Subquery as IDbQueryableInfo_Select;

            if (dbQuery.HasMany && subQuery != null && subQuery.Aggregate != null)
            {
                dbQuery = subQuery;
            }

            bool useStatis = dbQuery.Aggregate != null;
            // 没有聚合函数或者使用 'Skip' 子句,则解析OrderBy
            // 导航属性如果使用嵌套,除非有 TOP 或者 OFFSET 子句,否则不能用ORDER BY
            string alias0      = token != null && !string.IsNullOrEmpty(token.AliasPrefix) ? (token.AliasPrefix + "0") : "t0";
            bool   useSubquery = dbQuery.HasDistinct || dbQuery.GroupBy != null || dbQuery.Skip > 0 || dbQuery.Take > 0;
            bool   useOrderBy  = (!useStatis || dbQuery.Skip > 0) && !dbQuery.HasAny && (!dbQuery.IsParsedByMany || (dbQuery.Skip > 0 || dbQuery.Take > 0));

            TableAlias aliases = this.PrepareTableAlias(dbQuery, token);
            var        result  = new MappingCommand(this, aliases, token)
            {
                HasMany = dbQuery.HasMany
            };
            ISqlBuilder jf = result.JoinFragment;
            ISqlBuilder wf = result.WhereFragment;
            ISqlBuilder sf = null;

            jf.Indent = indent;

            #region 嵌套查询

            if (useStatis && useSubquery)
            {
                // SELECT
                jf.Append("SELECT ");
                jf.AppendNewLine();

                // SELECT COUNT(1)
                var visitor_ = new AggregateExpressionVisitor(this, aliases, dbQuery.Aggregate, dbQuery.GroupBy, alias0);
                visitor_.Write(jf);
                result.AddNavMembers(visitor_.NavMembers);

                // SELECT COUNT(1) FROM
                jf.AppendNewLine();
                jf.Append("FROM ( ");

                indent   += 1;
                jf.Indent = indent;
            }

            #endregion

            #region  择子句

            // SELECT 子句
            if (jf.Indent > 0)
            {
                jf.AppendNewLine();
            }
            jf.Append("SELECT ");

            if (dbQuery.HasAny)
            {
                jf.Append("CASE WHEN COUNT(1) = 1 THEN 1 ELSE 0 END FROM (");
                indent   += 1;
                jf.Indent = indent;
                jf.AppendNewLine();
                jf.Append("SELECT 1 ");
            }

            if (useStatis && !useSubquery)
            {
                // 如果有聚合函数,并且不是嵌套的话,则直接使用SELECT <MAX,MIN...>,不需要解析选择的字段
                jf.AppendNewLine();
                var visitor_ = new AggregateExpressionVisitor(this, aliases, dbQuery.Aggregate, dbQuery.GroupBy);
                visitor_.Write(jf);
                result.AddNavMembers(visitor_.NavMembers);
            }
            else
            {
                // DISTINCT 子句
                if (dbQuery.HasDistinct)
                {
                    jf.Append("DISTINCT ");
                }

                #region  择字段

                if (!dbQuery.HasAny)
                {
                    // SELECT 范围
                    var visitor_ = new ColumnExpressionVisitor(this, aliases, dbQuery);
                    if (dbQuery.Skip > 0 && dbQuery.Take == 0)
                    {
                        sf        = this.CreateSqlBuilder(token);
                        sf.Indent = jf.Indent + 1;
                        visitor_.Write(sf);
                    }
                    else
                    {
                        visitor_.Write(jf);
                    }

                    result.PickColumns        = visitor_.PickColumns;
                    result.PickColumnText     = visitor_.PickColumnText;
                    result.PickNavDescriptors = visitor_.PickNavDescriptors;
                    result.AddNavMembers(visitor_.NavMembers);

                    if (sf != null)
                    {
                        // 第一层嵌套
                        int index = 0;
                        jf.AppendNewLine();
                        foreach (var column in result.PickColumns)
                        {
                            jf.AppendMember(alias0, column.Name);
                            jf.AppendAs(column.NewName);
                            index += 1;
                            if (index < result.PickColumns.Count)
                            {
                                jf.Append(',');
                                jf.AppendNewLine();
                            }
                        }

                        jf.AppendNewLine();
                        jf.Append("FROM(");

                        // 第二层嵌套
                        indent   += 1;
                        jf.Indent = indent;
                        jf.AppendNewLine();
                        jf.Append("SELECT");
                        jf.Append(sf);
                        jf.Append(',');
                        jf.AppendNewLine();

                        if (dbQuery.OrderBys.Count == 0)
                        {
                            throw new XFrameworkException("The method 'OrderBy' must be called before 'Skip'.");
                        }
                        jf.Append("ROW_NUMBER() OVER(");
                        var visitor3 = new OrderByExpressionVisitor(this, aliases, dbQuery.OrderBys, dbQuery.GroupBy);
                        visitor3.Write(jf, false);
                        result.AddNavMembers(visitor3.NavMembers);
                        jf.Append(") Row_Number0");
                    }
                }

                #endregion
            }

            #endregion

            #region 顺序解析

            // FROM 子句
            jf.AppendNewLine();
            jf.Append("FROM ");
            if (dbQuery.Subquery != null)
            {
                // 子查询
                jf.Append("(");
                var cmd = this.ResolveSelectCommandImpl(dbQuery.Subquery, indent + 1, false, token);
                jf.Append(cmd.CommandText);
                jf.AppendNewLine();
                jf.Append(") ");
                jf.Append(alias0);
                jf.Append(' ');
            }
            else
            {
                var typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo(dbQuery.FromType);
                jf.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary);
                jf.Append(' ');
                jf.Append(alias0);
                jf.Append(' ');
            }

            // LEFT<INNER> JOIN 子句
            ExpressionVisitorBase visitor = new JoinExpressionVisitor(this, aliases, dbQuery.Joins);
            visitor.Write(jf);

            wf.Indent = jf.Indent;

            // WHERE 子句
            visitor = new WhereExpressionVisitor(this, aliases, dbQuery.Where);
            visitor.Write(wf);
            result.AddNavMembers(visitor.NavMembers);

            // GROUP BY 子句
            visitor = new GroupByExpressionVisitor(this, aliases, dbQuery.GroupBy);
            visitor.Write(wf);
            result.AddNavMembers(visitor.NavMembers);

            // HAVING 子句
            visitor = new HavingExpressionVisitor(this, aliases, dbQuery.Having, dbQuery.GroupBy);
            visitor.Write(wf);
            result.AddNavMembers(visitor.NavMembers);

            // ORDER 子句
            if (dbQuery.OrderBys.Count > 0 && useOrderBy)// && !groupByPaging)
            {
                visitor = new OrderByExpressionVisitor(this, aliases, dbQuery.OrderBys, dbQuery.GroupBy);
                visitor.Write(wf);
                result.AddNavMembers(visitor.NavMembers);
            }

            #endregion

            #region 分页查询

            // LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。
            // LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。如果给定两个参数,第一个参数指定第一个返回记录行的偏移量,第二个参数指定返回记录行的最大数目。
            // 初始记录行的偏移量是 0(而不是 1): 为了与 PostgreSQL 兼容,MySQL 也支持句法: LIMIT # OFFSET #。
            // Limit n,-1 语法不支持,使用ROW_Number()语法代替

            if (dbQuery.Take > 0)
            {
                wf.AppendNewLine().AppendFormat("LIMIT {0}", this.DbValue.GetSqlValue(dbQuery.Take, token));
                wf.AppendFormat(" OFFSET {0}", this.DbValue.GetSqlValue(dbQuery.Skip, token));
            }

            #endregion

            #region 嵌套查询

            if (useStatis && useSubquery)
            {
                result.CombineFragments();
                indent   -= 1;
                jf.Indent = indent;
                jf.AppendNewLine();
                jf.Append(") ");
                jf.Append(alias0);
            }

            #endregion

            #region 嵌套导航

            // TODO Include 从表,没分页,OrderBy 报错
            if (dbQuery.HasMany && subQuery != null && subQuery.OrderBys.Count > 0 && subQuery.Aggregate == null && !(subQuery.Skip > 0 || subQuery.Take > 0))
            {
                result.CombineFragments();
                visitor = new OrderByExpressionVisitor(this, aliases, subQuery.OrderBys);//, null, "t0");
                visitor.Write(jf);
            }

            #endregion

            #region 并集查询

            // UNION 子句
            if (dbQuery.Unions != null && dbQuery.Unions.Count > 0)
            {
                result.CombineFragments();
                for (int index = 0; index < dbQuery.Unions.Count; index++)
                {
                    jf.AppendNewLine();
                    jf.Append("UNION ALL");
                    if (indent == 0)
                    {
                        jf.AppendNewLine();
                    }
                    RawCommand cmd = this.ResolveSelectCommandImpl(dbQuery.Unions[index], indent, isOuter, token);
                    jf.Append(cmd.CommandText);
                }
            }

            #endregion

            #region 分页查询

            if (sf != null)
            {
                // 合并 WHERE
                result.CombineFragments();

                indent   -= 1;
                jf.Indent = indent;
                jf.AppendNewLine();
                jf.Append(") ");
                jf.Append(alias0);

                jf.AppendNewLine();
                jf.Append("WHERE ");
                if (dbQuery.Skip > 0)
                {
                    jf.AppendMember(alias0, "Row_Number0");
                    jf.Append(" > ");
                    jf.Append(this.DbValue.GetSqlValue(dbQuery.Skip, token));
                }
            }

            #endregion

            #region Any 子句

            // 'Any' 子句
            if (dbQuery.HasAny)
            {
                // 产生 WHERE 子句
                result.CombineFragments();
                // 如果没有分页,则显式指定只查一笔记录
                if (dbQuery.Take == 0 && dbQuery.Skip == 0)
                {
                    jf.AppendNewLine();
                    jf.Append("LIMIT 0,1");
                }

                indent   -= 1;
                jf.Indent = indent;
                jf.AppendNewLine();
                jf.Append(") ");
                jf.Append(alias0);
            }

            #endregion

            return(result);
        }
示例#16
0
        // 创建 SELECT 命令
        Command ParseSelectCommandImpl <T>(DbQueryableInfo_Select <T> sQuery, int indent, bool isOuter, ParserToken token)
        {
            // 说明:
            // 1.OFFSET 前必须要有 'ORDER BY',即 'Skip' 子句前必须使用 'OrderBy' 子句
            // 2.在有统计函数的<MAX,MIN...>情况下,如果有 'Distinct' 'GroupBy' 'Skip' 'Take' 子句,则需要使用嵌套查询
            // 3.'Any' 子句将翻译成 IF EXISTS...
            // 4.分组再分页时需要使用嵌套查询,此时子查询不需要 'OrderBy' 子句,但最外层则需要
            // 5.'Skip' 'Take' 子句视为语义结束符,在其之后的子句将使用嵌套查询
            // 6.导航属性中有 1:n 关系的,需要使用嵌套查询,否则分页查询会有问题


            // 导航属性中有1:n关系,只统计主表
            // 例:AccountList = a.Client.AccountList,
            DbQueryableInfo_Select <T> subQuery = sQuery.SubQueryInfo as DbQueryableInfo_Select <T>;

            if (sQuery.HasManyNavigation && subQuery != null && subQuery.StatisExpression != null)
            {
                sQuery = subQuery;
            }

            bool   useStatis  = sQuery.StatisExpression != null;
            bool   useNesting = sQuery.HaveDistinct || sQuery.GroupByExpression != null || sQuery.Skip > 0 || sQuery.Take > 0;
            string alias0     = token != null && !string.IsNullOrEmpty(token.TableAliasName) ? (token.TableAliasName + "0") : "t0";
            // 没有统计函数或者使用 'Skip' 子句,则解析OrderBy
            // 导航属性如果使用嵌套,除非有 TOP 或者 OFFSET 子句,否则不能用ORDER BY
            bool useOrderBy = (!useStatis || sQuery.Skip > 0) && !sQuery.HaveAny && (!sQuery.ResultByManyNavigation || (sQuery.Skip > 0 || sQuery.Take > 0));

            IDbQueryable    dbQueryable = sQuery.SourceQuery;
            TableAliasCache aliases     = this.PrepareAlias <T>(sQuery, token);
            SelectCommand   cmd         = new SelectCommand(this, aliases, token)
            {
                HasManyNavigation = sQuery.HasManyNavigation
            };
            ISqlBuilder jf = cmd.JoinFragment;
            ISqlBuilder wf = cmd.WhereFragment;

            (jf as NpgSqlBuilder).IsOuter = isOuter;

            jf.Indent = indent;

            #region 嵌套查询

            if (useStatis && useNesting)
            {
                // SELECT
                jf.Append("SELECT ");
                jf.AppendNewLine();

                // SELECT COUNT(1)
                var visitor2 = new StatisExpressionVisitor(this, aliases, sQuery.StatisExpression, sQuery.GroupByExpression, alias0);
                visitor2.Write(jf);
                cmd.AddNavMembers(visitor2.NavMembers);

                // SELECT COUNT(1) FROM
                jf.AppendNewLine();
                jf.Append("FROM ( ");

                indent   += 1;
                jf.Indent = indent;
                (jf as NpgSqlBuilder).IsOuter = false;
            }

            #endregion

            #region  择子句

            // SELECT 子句
            if (jf.Indent > 0)
            {
                jf.AppendNewLine();
            }
            jf.Append("SELECT ");

            if (sQuery.HaveAny)
            {
                jf.Append("CASE WHEN COUNT(1) = 1 THEN 1 ELSE 0 END FROM (");
                indent   += 1;
                jf.Indent = indent;
                jf.AppendNewLine();
                jf.Append("SELECT 1 ");
            }

            if (useStatis && !useNesting)
            {
                // 如果有统计函数,并且不是嵌套的话,则直接使用SELECT <MAX,MIN...>,不需要解析选择的字段
                jf.AppendNewLine();
                var visitor2 = new StatisExpressionVisitor(this, aliases, sQuery.StatisExpression, sQuery.GroupByExpression);
                visitor2.Write(jf);
                cmd.AddNavMembers(visitor2.NavMembers);
            }
            else
            {
                // DISTINCT 子句
                if (sQuery.HaveDistinct)
                {
                    jf.Append("DISTINCT ");
                }

                #region  择字段

                if (!sQuery.HaveAny)
                {
                    // SELECT 范围
                    var visitor2 = new ColumnExpressionVisitor(this, aliases, sQuery);
                    visitor2.Write(jf);

                    cmd.Columns     = visitor2.Columns;
                    cmd.Navigations = visitor2.Navigations;
                    cmd.AddNavMembers(visitor2.NavMembers);
                }

                #endregion
            }

            #endregion

            #region 顺序解析

            // FROM 子句
            jf.AppendNewLine();
            jf.Append("FROM ");
            if (sQuery.SubQueryInfo != null)
            {
                // 子查询
                jf.Append('(');
                Command cmd2 = this.ParseSelectCommandImpl <T>(sQuery.SubQueryInfo as DbQueryableInfo_Select <T>, indent + 1, false, token);
                jf.Append(cmd2.CommandText);
                jf.AppendNewLine();
                jf.Append(") ");
                jf.Append(alias0);
                jf.Append(' ');
            }
            else
            {
                var typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo(sQuery.FromType);
                jf.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary);
                jf.Append(' ');
                jf.Append(alias0);
                jf.Append(' ');
            }

            // LEFT<INNER> JOIN 子句
            ExpressionVisitorBase visitor = new JoinExpressionVisitor(this, aliases, sQuery.Joins);
            visitor.Write(jf);

            wf.Indent = jf.Indent;

            // WHERE 子句
            visitor = new WhereExpressionVisitor(this, aliases, sQuery.WhereExpression);
            visitor.Write(wf);
            cmd.AddNavMembers(visitor.NavMembers);

            // GROUP BY 子句
            visitor = new GroupByExpressionVisitor(this, aliases, sQuery.GroupByExpression);
            visitor.Write(wf);
            cmd.AddNavMembers(visitor.NavMembers);

            // HAVING 子句
            visitor = new HavingExpressionVisitor(this, aliases, sQuery.HavingExpression, sQuery.GroupByExpression);
            visitor.Write(wf);
            cmd.AddNavMembers(visitor.NavMembers);

            // ORDER 子句
            if (sQuery.OrderBys.Count > 0 && useOrderBy)
            {
                visitor = new OrderByExpressionVisitor(this, aliases, sQuery.OrderBys, sQuery.GroupByExpression);
                visitor.Write(wf);
                cmd.AddNavMembers(visitor.NavMembers);
            }

            #endregion

            #region 分页查询

            if (sQuery.Take > 0)
            {
                wf.AppendNewLine().AppendFormat("LIMIT {0}", wf.GetSqlValue(sQuery.Take));
            }
            if (sQuery.Skip > 0)
            {
                wf.AppendFormat(" OFFSET {0}", wf.GetSqlValue(sQuery.Skip));
            }

            #endregion

            #region 嵌套查询

            if (useStatis && useNesting)
            {
                cmd.Convergence();
                indent   -= 1;
                jf.Indent = indent;
                jf.AppendNewLine();
                jf.Append(") ");
                jf.Append(alias0);
            }

            #endregion

            #region 嵌套导航

            // TODO Include 从表,没分页,OrderBy 报错
            if (sQuery.HasManyNavigation && subQuery != null && subQuery.OrderBys.Count > 0 && subQuery.StatisExpression == null && !(subQuery.Skip > 0 || subQuery.Take > 0))
            {
                cmd.Convergence();
                visitor = new OrderByExpressionVisitor(this, aliases, subQuery.OrderBys);//, null, "t0");
                visitor.Write(jf);
            }

            #endregion

            #region 并集查询

            // UNION 子句
            if (sQuery.Unions != null && sQuery.Unions.Count > 0)
            {
                cmd.Convergence();
                for (int index = 0; index < sQuery.Unions.Count; index++)
                {
                    jf.AppendNewLine();
                    jf.Append("UNION ALL");
                    if (indent == 0)
                    {
                        jf.AppendNewLine();
                    }
                    Command cmd2 = this.ParseSelectCommandImpl <T>(sQuery.Unions[index] as DbQueryableInfo_Select <T>, indent, isOuter, token);
                    jf.Append(cmd2.CommandText);
                }
            }

            #endregion

            #region Any 子句

            // 'Any' 子句
            if (sQuery.HaveAny)
            {
                // 产生 WHERE 子句
                cmd.Convergence();

                // 如果没有分页,则显式指定只查一笔记录
                if (sQuery.Take == 0 && sQuery.Skip == 0)
                {
                    jf.AppendNewLine();
                    jf.Append("LIMIT 1");
                }

                indent   -= 1;
                jf.Indent = indent;
                jf.AppendNewLine();
                jf.Append(") ");
                jf.Append(alias0);
            }

            #endregion

            return(cmd);
        }
示例#17
0
        /// <summary>
        /// 创建 DELETE 命令
        /// </summary>
        /// <param name="dbQuery">查询语义</param>
        /// <param name="token">解析上下文</param>
        /// <returns></returns>
        protected override RawCommand ResolveDeleteCommand <T>(IDbQueryableInfo_Delete dbQuery, ResolveToken token)
        {
            ISqlBuilder builder     = this.CreateSqlBuilder(token);
            var         typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo <T>();

            builder.Append("DELETE FROM ");
            builder.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary);
            builder.Append(" ");

            if (dbQuery.Entity != null)
            {
                if (typeRuntime.KeyMembers == null || typeRuntime.KeyMembers.Count == 0)
                {
                    throw new XFrameworkException("Delete<T>(T value) require entity must have key column.");
                }

                object entity = dbQuery.Entity;

                builder.AppendNewLine();
                builder.Append("WHERE ");

                foreach (var m in typeRuntime.KeyMembers)
                {
                    var column = m.Column;
                    var value  = m.Invoke(entity);
                    var seg    = this.DbValue.GetSqlValue(value, token, column);

                    builder.AppendMember(m.Member.Name);
                    builder.Append(" = ");
                    builder.Append(seg);
                    builder.Append(" AND ");
                }
                builder.Length -= 5;
            }
            else if (dbQuery.Query != null)
            {
                // 解析查询用来确定是否需要嵌套
                var cmd = this.ResolveSelectCommand(dbQuery.Query, 1, false, null) as MappingCommand;
                if (token != null && token.Extendsions == null)
                {
                    token.Extendsions = new Dictionary <string, object>();
                    if (!token.Extendsions.ContainsKey("SQLiteDelete"))
                    {
                        token.Extendsions.Add("SQLiteDelete", null);
                    }
                }

                if ((cmd.NavMembers != null && cmd.NavMembers.Count > 0) || dbQuery.Query.Joins.Count > 0)
                {
                    // 最外层仅选择 RowID 列
                    var outQuery = dbQuery.Query;
                    outQuery.Select = new DbExpression(DbExpressionType.Select, Expression.Constant("t0.RowId", typeof(string)));
                    var iterator = outQuery;
                    while (iterator.Subquery != null)
                    {
                        var subQuery = new SQLiteDbQueryableInfo_Select(iterator.Subquery);
                        iterator.Subquery = subQuery;
                        iterator          = subQuery;
                    }

                    // 解析成 RowId IN 结构
                    cmd = (MappingCommand)this.ResolveSelectCommand(dbQuery.Query, 1, false, token);
                    builder.Append("WHERE ");
                    builder.AppendMember("RowID");
                    builder.Append(" IN(");
                    builder.AppendNewLine(cmd.CommandText);
                    builder.Append(')');
                }
                else
                {
                    TableAlias            aliases = this.PrepareTableAlias(dbQuery.Query, token);
                    ExpressionVisitorBase visitor = null;

                    visitor = new JoinExpressionVisitor(this, aliases, dbQuery.Query.Joins);
                    visitor.Write(builder);

                    visitor = new WhereExpressionVisitor(this, null, dbQuery.Query.Where);
                    visitor.Write(builder);
                }
            }

            builder.Append(';');
            return(new RawCommand(builder.ToString(), builder.Token != null ? builder.Token.Parameters : null, System.Data.CommandType.Text));
        }
示例#18
0
        // 创建 SELECT 命令
        RawCommand ResolveSelectCommandImpl(IDbQueryableInfo_Select dbQuery, int indent, bool isOuter, ResolveToken token)
        {
            // 说明:
            // 1.OFFSET 前必须要有 'ORDER BY',即 'Skip' 子句前必须使用 'OrderBy' 子句
            // 2.在有聚合函数的<MAX,MIN...>情况下,如果有 'Distinct' 'GroupBy' 'Skip' 'Take' 子句,则需要使用嵌套查询
            // 3.'Any' 子句将翻译成 IF EXISTS...
            // 4.分组再分页时需要使用嵌套查询,此时子查询不需要 'OrderBy' 子句,但最外层则需要
            // 5.'Skip' 'Take' 子句视为语义结束符,在其之后的子句将使用嵌套查询
            // 6.导航属性中有 1:n 关系的,需要使用嵌套查询,否则分页查询会有问题


            // 导航属性中有1:n关系,只统计主表
            // 例:AccountList = a.Client.AccountList,
            var subQuery = dbQuery.Subquery as IDbQueryableInfo_Select;

            if (dbQuery.HasMany && subQuery != null && subQuery.Aggregate != null)
            {
                dbQuery = subQuery;
            }

            bool   useStatis  = dbQuery.Aggregate != null;
            bool   useNesting = dbQuery.HasDistinct || dbQuery.GroupBy != null || dbQuery.Skip > 0 || dbQuery.Take > 0;
            string alias0     = token != null && !string.IsNullOrEmpty(token.AliasPrefix) ? (token.AliasPrefix + "0") : "t0";
            // 没有聚合函数或者使用 'Skip' 子句,则解析OrderBy
            // 导航属性如果使用嵌套,除非有 TOP 或者 OFFSET 子句,否则不能用ORDER BY
            bool useOrderBy = (!useStatis || dbQuery.Skip > 0) && !dbQuery.HasAny && (!dbQuery.IsParsedByMany || (dbQuery.Skip > 0 || dbQuery.Take > 0));

            TableAlias aliases = this.PrepareTableAlias(dbQuery, token);
            var        result  = new MappingCommand(this, aliases, token)
            {
                HasMany = dbQuery.HasMany
            };
            ISqlBuilder jf = result.JoinFragment;
            ISqlBuilder wf = result.WhereFragment;

            jf.Indent = indent;

            #region 嵌套查询

            if (useStatis && useNesting)
            {
                // SELECT
                jf.Append("SELECT ");
                jf.AppendNewLine();

                // SELECT COUNT(1)
                var visitor_ = new AggregateExpressionVisitor(this, aliases, dbQuery.Aggregate, dbQuery.GroupBy, alias0);
                visitor_.Write(jf);
                result.AddNavMembers(visitor_.NavMembers);

                // SELECT COUNT(1) FROM
                jf.AppendNewLine();
                jf.Append("FROM ( ");

                indent   += 1;
                jf.Indent = indent;
            }

            #endregion

            #region  择子句

            // SELECT 子句
            if (jf.Indent > 0)
            {
                jf.AppendNewLine();
            }
            jf.Append("SELECT ");

            if (dbQuery.HasAny)
            {
                jf.Append("CASE WHEN COUNT(1) = 1 THEN 1 ELSE 0 END FROM (");
                indent   += 1;
                jf.Indent = indent;
                jf.AppendNewLine();
                jf.Append("SELECT 1 ");
            }

            if (useStatis && !useNesting)
            {
                // 如果有聚合函数,并且不是嵌套的话,则直接使用SELECT <MAX,MIN...>,不需要解析选择的字段
                jf.AppendNewLine();
                var visitor_ = new AggregateExpressionVisitor(this, aliases, dbQuery.Aggregate, dbQuery.GroupBy);
                visitor_.Write(jf);
                result.AddNavMembers(visitor_.NavMembers);
            }
            else
            {
                // DISTINCT 子句
                if (dbQuery.HasDistinct)
                {
                    jf.Append("DISTINCT ");
                }

                #region  择字段

                if (!dbQuery.HasAny)
                {
                    // SELECT 范围
                    var visitor2 = new SQLiteColumnExpressionVisitor(this, aliases, dbQuery);
                    visitor2.Write(jf);

                    result.PickColumns        = visitor2.PickColumns;
                    result.PickColumnText     = visitor2.PickColumnText;
                    result.PickNavDescriptors = visitor2.PickNavDescriptors;
                    result.AddNavMembers(visitor2.NavMembers);
                }

                #endregion
            }

            #endregion

            #region 顺序解析

            // FROM 子句
            jf.AppendNewLine();
            jf.Append("FROM ");
            if (dbQuery.Subquery != null)
            {
                // 子查询
                jf.Append('(');
                RawCommand cmd = this.ResolveSelectCommandImpl(dbQuery.Subquery, indent + 1, false, token);
                jf.Append(cmd.CommandText);
                jf.AppendNewLine();
                jf.Append(") ");
                jf.Append(alias0);
                jf.Append(' ');
            }
            else
            {
                var typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo(dbQuery.FromType);
                jf.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary);
                jf.Append(' ');
                jf.Append(alias0);
                jf.Append(' ');
            }

            // LEFT<INNER> JOIN 子句
            ExpressionVisitorBase visitor = new JoinExpressionVisitor(this, aliases, dbQuery.Joins);
            visitor.Write(jf);

            wf.Indent = jf.Indent;

            // WHERE 子句
            visitor = new WhereExpressionVisitor(this, aliases, dbQuery.Where);
            visitor.Write(wf);
            result.AddNavMembers(visitor.NavMembers);

            // GROUP BY 子句
            visitor = new GroupByExpressionVisitor(this, aliases, dbQuery.GroupBy);
            visitor.Write(wf);
            result.AddNavMembers(visitor.NavMembers);

            // HAVING 子句
            visitor = new HavingExpressionVisitor(this, aliases, dbQuery.Having, dbQuery.GroupBy);
            visitor.Write(wf);
            result.AddNavMembers(visitor.NavMembers);

            // ORDER 子句
            if (dbQuery.OrderBys.Count > 0 && useOrderBy)
            {
                visitor = new OrderByExpressionVisitor(this, aliases, dbQuery.OrderBys, dbQuery.GroupBy);
                visitor.Write(wf);
                result.AddNavMembers(visitor.NavMembers);
            }

            #endregion

            #region 分页查询

            if (dbQuery.Take > 0)
            {
                wf.AppendNewLine().AppendFormat("LIMIT {0}", this.DbValue.GetSqlValue(dbQuery.Take, token));
            }
            else if (dbQuery.Take == 0 && dbQuery.Skip > 0)
            {
                wf.AppendNewLine().AppendFormat("LIMIT {0}", this.DbValue.GetSqlValue(-1, token));
            }
            if (dbQuery.Skip > 0)
            {
                wf.AppendFormat(" OFFSET {0}", this.DbValue.GetSqlValue(dbQuery.Skip, token));
            }

            #endregion

            #region 嵌套查询

            if (useStatis && useNesting)
            {
                result.CombineFragments();
                indent   -= 1;
                jf.Indent = indent;
                jf.AppendNewLine();
                jf.Append(") ");
                jf.Append(alias0);
            }

            #endregion

            #region 嵌套导航

            // TODO Include 从表,没分页,OrderBy 报错
            if (dbQuery.HasMany && subQuery != null && subQuery.OrderBys.Count > 0 && subQuery.Aggregate == null && !(subQuery.Skip > 0 || subQuery.Take > 0))
            {
                result.CombineFragments();
                visitor = new OrderByExpressionVisitor(this, aliases, subQuery.OrderBys);//, null, "t0");
                visitor.Write(jf);
            }

            #endregion

            #region 并集查询

            // UNION 子句
            if (dbQuery.Unions != null && dbQuery.Unions.Count > 0)
            {
                result.CombineFragments();
                for (int index = 0; index < dbQuery.Unions.Count; index++)
                {
                    jf.AppendNewLine();
                    jf.Append("UNION ALL");
                    if (indent == 0)
                    {
                        jf.AppendNewLine();
                    }
                    RawCommand cmd = this.ResolveSelectCommand(dbQuery.Unions[index], indent, isOuter, token);
                    jf.Append(cmd.CommandText);
                }
            }

            #endregion

            #region Any 子句

            // 'Any' 子句
            if (dbQuery.HasAny)
            {
                // 产生 WHERE 子句
                result.CombineFragments();

                // 如果没有分页,则显式指定只查一笔记录
                if (dbQuery.Take == 0 && dbQuery.Skip == 0)
                {
                    jf.AppendNewLine();
                    jf.Append("LIMIT 1");
                }

                indent   -= 1;
                jf.Indent = indent;
                jf.AppendNewLine();
                jf.Append(") ");
                jf.Append(alias0);
            }

            #endregion

            return(result);
        }
示例#19
0
        // 创建 SELECT 命令
        protected override DbCommandDefinition ParseSelectCommand <T>(DbQueryableInfo_Select <T> sQuery, int indent = 0, bool isOuter = true, List <IDbDataParameter> parameters = null)
        {
            // 说明:
            // 1.OFFSET 前必须要有 'ORDER BY',即 'Skip' 子句前必须使用 'OrderBy' 子句
            // 2.在有统计函数的<MAX,MIN...>情况下,如果有 'Distinct' 'GroupBy' 'Skip' 'Take' 子句,则需要使用嵌套查询
            // 3.'Any' 子句将翻译成 IF EXISTS...
            // 4.分组再分页时需要使用嵌套查询,此时子查询不需要 'OrderBy' 子句,但最外层则需要
            // 5.'Skip' 'Take' 子句视为语义结束符,在其之后的子句将使用嵌套查询
            // 6.导航属性中有 1:n 关系的,需要使用嵌套查询,否则分页查询会有问题
            // 8.如果有分页,则使用嵌套
            // 9.如果有分页还有OrderBy,则使用嵌套的嵌套

            // 导航属性中有1:n关系,只统计主表
            // 例:AccountList = a.Client.AccountList,
            DbQueryableInfo_Select <T> innerQuery = sQuery.SubQueryInfo as DbQueryableInfo_Select <T>;

            if (sQuery.HaveListNavigation && innerQuery != null && innerQuery.Statis != null)
            {
                sQuery = innerQuery;
            }

            bool useNesting = sQuery.HaveDistinct || sQuery.GroupBy != null || sQuery.Skip > 0 || sQuery.Take > 0;
            bool useStatis  = sQuery.Statis != null;
            // 没有统计函数或者使用 'Skip' 子句,则解析OrderBy
            // 导航属性如果使用嵌套,除非有 TOP 或者 OFFSET 子句,否则不能用ORDER BY
            bool useOrderBy = (!useStatis || sQuery.Skip > 0) && !sQuery.HaveAny && (!sQuery.GenByListNavigation || (sQuery.Skip > 0 || sQuery.Take > 0));

            IDbQueryable               dbQueryable = sQuery.SourceQuery;
            TableAliasCache            aliases     = this.PrepareAlias <T>(sQuery);
            DbCommandDefinition_Select cmd         = new DbCommandDefinition_Select(this, aliases, parameters)
            {
                HaveListNavigation = sQuery.HaveListNavigation
            };
            ISqlBuilder jf = cmd.JoinFragment;
            ISqlBuilder wf = cmd.WhereFragment;

            (jf as OracleSqlBuilder).IsOuter = isOuter;

            jf.Indent = indent;

            #region 嵌套查询

            if (useStatis && useNesting)
            {
                // SELECT
                jf.Append("SELECT ");
                jf.AppendNewLine();

                // SELECT COUNT(1)
                var visitor2 = new StatisExpressionVisitor(this, aliases, sQuery.Statis, sQuery.GroupBy, "t0");
                visitor2.Write(jf);
                cmd.AddNavMembers(visitor2.NavMembers);

                // SELECT COUNT(1) FROM
                jf.AppendNewLine();
                jf.Append("FROM ( ");

                indent   += 1;
                jf.Indent = indent;
                (jf as OracleSqlBuilder).IsOuter = false;
            }

            #endregion

            #region  择子句

            // SELECT 子句
            if (jf.Indent > 0)
            {
                jf.AppendNewLine();
            }
            jf.Append("SELECT ");

            if (sQuery.HaveAny)
            {
                jf.Append("CASE WHEN COUNT(1) = 1 THEN 1 ELSE 0 END FROM (");
                indent   += 1;
                jf.Indent = indent;
                jf.AppendNewLine();
                jf.Append("SELECT 1 ");
            }

            if (useStatis && !useNesting)
            {
                // 如果有统计函数,并且不是嵌套的话,则直接使用SELECT <MAX,MIN...>,不需要解析选择的字段
                jf.AppendNewLine();
                var visitor2 = new StatisExpressionVisitor(this, aliases, sQuery.Statis, sQuery.GroupBy);
                visitor2.Write(jf);
                cmd.AddNavMembers(visitor2.NavMembers);
            }
            else
            {
                // DISTINCT 子句
                if (sQuery.HaveDistinct)
                {
                    jf.Append("DISTINCT ");
                }

                #region  择字段

                if (!sQuery.HaveAny)
                {
                    // SELECT 范围
                    ISqlBuilder sf = this.CreateSqlBuilder(jf.Parameters);
                    sf.Indent = jf.Indent + ((sQuery.Skip > 0 || sQuery.Take > 0) ? 2 : 0);
                    (sf as OracleSqlBuilder).IsOuter = (sQuery.Skip > 0 || sQuery.Take > 0) ? false : (jf as OracleSqlBuilder).IsOuter;

                    var visitor2 = new ColumnExpressionVisitor(this, aliases, sQuery);
                    visitor2.Write(sf);

                    cmd.Columns     = visitor2.Columns;
                    cmd.Navigations = visitor2.Navigations;
                    cmd.AddNavMembers(visitor2.NavMembers);

                    // 分页,产生两层嵌套
                    if (sQuery.Skip > 0 || sQuery.Take > 0)
                    {
                        // 第一层嵌套
                        int index = 0;
                        jf.AppendNewLine();
                        foreach (var entry in cmd.Columns)
                        {
                            jf.AppendMember("t0", entry.Key);
                            jf.AppendAs(entry.Key);
                            index += 1;
                            if (index < cmd.Columns.Count)
                            {
                                jf.Append(',');
                                jf.AppendNewLine();
                            }
                        }

                        jf.AppendNewLine();
                        jf.Append("FROM(");

                        // 第二层嵌套
                        indent   += 1;
                        isOuter   = false;
                        jf.Indent = indent;
                        (jf as OracleSqlBuilder).IsOuter = isOuter;
                        jf.AppendNewLine();

                        jf.Append("SELECT");
                        jf.AppendNewLine();

                        foreach (var entry in cmd.Columns)
                        {
                            jf.AppendMember("t0", entry.Key);
                            jf.AppendAs(entry.Key);
                            jf.Append(',');
                            jf.AppendNewLine();
                        }
                        jf.Append("ROWNUM AS Row_Number0");
                        jf.AppendNewLine();
                        jf.Append("FROM(");

                        // 第三层嵌套
                        indent   += 1;
                        jf.Indent = indent;
                        jf.AppendNewLine();
                        jf.Append("SELECT");
                    }

                    jf.Append(sf);
                }

                #endregion
            }

            #endregion

            #region 顺序解析

            // FROM 子句
            jf.AppendNewLine();
            jf.Append("FROM ");
            if (sQuery.SubQueryInfo != null)
            {
                // 子查询
                jf.Append('(');
                DbCommandDefinition cmd2 = this.ParseSelectCommand <T>(sQuery.SubQueryInfo as DbQueryableInfo_Select <T>, indent + 1, false, jf.Parameters);
                jf.Append(cmd2.CommandText);
                jf.AppendNewLine();
                jf.Append(") t0 ");
            }
            else
            {
                var typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo(sQuery.FromType);
                jf.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary);
                jf.Append(" t0 ");
                //if (dbQueryable.DbContext.NoLock && !string.IsNullOrEmpty(this.WidthNoLock)) jf.Append(this.WidthNoLock);
            }

            // LEFT<INNER> JOIN 子句
            ExpressionVisitorBase visitor = new JoinExpressionVisitor(this, aliases, sQuery.Join);
            visitor.Write(jf);

            wf.Indent = jf.Indent;

            // WHERE 子句
            visitor = new WhereExpressionVisitor(this, aliases, sQuery.Where);
            visitor.Write(wf);
            cmd.AddNavMembers(visitor.NavMembers);

            // GROUP BY 子句
            visitor = new GroupByExpressionVisitor(this, aliases, sQuery.GroupBy);
            visitor.Write(wf);
            cmd.AddNavMembers(visitor.NavMembers);

            // HAVING 子句
            visitor = new HavingExpressionVisitor(this, aliases, sQuery.Having, sQuery.GroupBy);
            visitor.Write(wf);
            cmd.AddNavMembers(visitor.NavMembers);

            // ORDER 子句
            if (sQuery.OrderBy.Count > 0 && useOrderBy)
            {
                visitor = new OrderByExpressionVisitor(this, aliases, sQuery.OrderBy, sQuery.GroupBy);
                visitor.Write(wf);
                cmd.AddNavMembers(visitor.NavMembers);
            }

            #endregion

            #region 嵌套查询

            if (useStatis && useNesting)
            {
                cmd.Convergence();
                indent   -= 1;
                jf.Indent = indent;
                jf.AppendNewLine();
                jf.Append(" ) t0");
            }

            #endregion

            #region 嵌套导航

            // TODO Include 从表,没分页,OrderBy 报错
            if (sQuery.HaveListNavigation && innerQuery != null && innerQuery.OrderBy.Count > 0 && innerQuery.Statis == null && !(innerQuery.Skip > 0 || innerQuery.Take > 0))
            {
                // OrderBy("a.CloudServer.CloudServerName");
                cmd.Convergence();
                visitor = new OrderByExpressionVisitor(this, aliases, innerQuery.OrderBy);//, null, "t0");
                visitor.Write(jf);
            }

            #endregion

            #region 并集查询

            // UNION 子句
            if (sQuery.Union != null && sQuery.Union.Count > 0)
            {
                cmd.Convergence();
                for (int index = 0; index < sQuery.Union.Count; index++)
                {
                    jf.AppendNewLine();
                    jf.Append("UNION ALL");
                    if (indent == 0)
                    {
                        jf.AppendNewLine();
                    }
                    DbCommandDefinition cmd2 = this.ParseSelectCommand <T>(sQuery.Union[index] as DbQueryableInfo_Select <T>, indent, isOuter, jf.Parameters);
                    jf.Append(cmd2.CommandText);
                }
            }

            #endregion

            #region 分页查询

            if (sQuery.Take > 0 || sQuery.Skip > 0)
            {
                // 合并 WHERE
                cmd.Convergence();

                indent   -= 1;
                jf.Indent = indent;
                jf.AppendNewLine();
                jf.Append(") t0");

                indent   -= 1;
                jf.Indent = indent;
                jf.AppendNewLine();
                jf.Append(") t0");

                jf.AppendNewLine();
                jf.Append("WHERE ");
                if (sQuery.Skip > 0)
                {
                    jf.Append("t0.Row_Number0 > ");
                    jf.Append(jf.GetSqlValue(sQuery.Skip));
                }
                if (sQuery.Take > 0)
                {
                    if (sQuery.Skip > 0)
                    {
                        jf.Append(" AND ");
                    }
                    jf.Append("t0.Row_Number0 <= ");
                    jf.Append(jf.GetSqlValue((sQuery.Skip + sQuery.Take)));
                }
            }

            #endregion

            #region Any 子句

            // 'Any' 子句
            if (sQuery.HaveAny)
            {
                // 产生 WHERE 子句
                cmd.Convergence();
                // 如果没有分页,则显式指定只查一笔记录
                if (sQuery.Take == 0 && sQuery.Skip == 0)
                {
                    if (sQuery.Where != null && sQuery.Where.Expressions != null)
                    {
                        jf.Append(" AND ROWNUM <= 1");
                    }
                    else
                    {
                        jf.AppendNewLine();
                        jf.Append("WHERE  ROWNUM <= 1");
                    }
                }

                indent   -= 1;
                jf.Indent = indent;
                jf.AppendNewLine();
                jf.Append(") t0");
            }

            #endregion

            return(cmd);
        }
示例#20
0
        /// <summary>
        /// 创建 UPDATE 命令
        /// </summary>
        /// <param name="dbQuery">查询语义</param>
        /// <param name="token">解析上下文</param>
        /// <returns></returns>
        protected override RawCommand ResolveUpdateCommand <T>(IDbQueryableInfo_Update dbQuery, ResolveToken token)
        {
            ISqlBuilder builder     = this.CreateSqlBuilder(token);
            var         typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo <T>();

            builder.Append("UPDATE ");
            builder.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary);
            builder.Append(" t0 SET");
            builder.AppendNewLine();

            if (dbQuery.Entity != null)
            {
                object      entity    = dbQuery.Entity;
                ISqlBuilder seg_Where = this.CreateSqlBuilder(token);
                bool        useKey    = false;
                int         length    = 0;


                foreach (var m in typeRuntime.Members)
                {
                    var column = m.Column;
                    if (column != null && column.IsIdentity)
                    {
                        goto gotoLabel;                                      // Fix issue# 自增列同时又是主键
                    }
                    if (column != null && column.NoMapped)
                    {
                        continue;
                    }
                    if (m.ForeignKey != null)
                    {
                        continue;
                    }
                    if (m.Member.MemberType == System.Reflection.MemberTypes.Method)
                    {
                        continue;
                    }

                    builder.AppendMember(m.Member.Name);
                    builder.Append(" = ");

gotoLabel:

                    if (column == null || !column.IsIdentity)
                    {
                        var value = m.Invoke(entity);
                        var seg   = this.DbValue.GetSqlValueWidthDefault(value, token, column);

                        builder.Append(seg);
                        length = builder.Length;
                        builder.Append(',');
                        builder.AppendNewLine();
                    }

                    if (column != null && column.IsKey)
                    {
                        useKey = true;
                    }
                }

                if (!useKey)
                {
                    throw new XFrameworkException("Update<T>(T value) require T must have key column.");
                }


                // ORACLE 需要注意参数顺序问题
                int index = -1;
                foreach (var m in typeRuntime.KeyMembers)
                {
                    var column = m.Column;
                    var value  = m.Invoke(entity);
                    var seg    = this.DbValue.GetSqlValueWidthDefault(value, token, column);
                    index += 1;

                    seg_Where.AppendMember(m.Member.Name);
                    seg_Where.Append(" = ");
                    seg_Where.Append(seg);
                    if (index < typeRuntime.KeyMembers.Count - 1)
                    {
                        seg_Where.Append(" AND ");
                    }
                }

                builder.Length = length;
                builder.AppendNewLine();
                builder.Append("WHERE ");
                builder.Append(seg_Where);
            }
            else if (dbQuery.Expression != null)
            {
                // SELECT 表达式
                LambdaExpression lambda = dbQuery.Expression as LambdaExpression;
                var        body         = lambda.Body;
                Expression expression   = null;
                if (body.NodeType == ExpressionType.MemberInit)
                {
                    var @init    = body as MemberInitExpression;
                    var bindings = new List <MemberBinding>(@init.Bindings);
                    foreach (var m in typeRuntime.KeyMembers)
                    {
                        var member  = Expression.MakeMemberAccess(lambda.Parameters[0], m.Member);
                        var binding = Expression.Bind(m.Member, member);
                        if (!bindings.Any(x => x.Member == m.Member))
                        {
                            bindings.Add(binding);
                        }
                    }
                    expression = Expression.MemberInit(@init.NewExpression, bindings);
                }
                else if (body.NodeType == ExpressionType.New)
                {
                    var newExpression = body as NewExpression;
                    var bindings      = new List <MemberBinding>();
                    for (int i = 0; i < newExpression.Members.Count; i++)
                    {
                        var m       = typeRuntime.GetMember(newExpression.Members[i].Name);
                        var binding = Expression.Bind(m.Member, newExpression.Arguments[i].Type != m.DataType
                            ? Expression.Convert(newExpression.Arguments[i], m.DataType)
                            : newExpression.Arguments[i]);
                        bindings.Add(binding);
                    }

                    foreach (var m in typeRuntime.KeyMembers)
                    {
                        var member  = Expression.MakeMemberAccess(lambda.Parameters[0], m.Member);
                        var binding = Expression.Bind(m.Member, member);
                        if (!bindings.Any(x => x.Member == m.Member))
                        {
                            bindings.Add(binding);
                        }
                    }

                    var newExpression2 = Expression.New(typeRuntime.Constructor.Constructor);
                    expression = Expression.MemberInit(newExpression2, bindings);
                }

                // 解析查询以确定是否需要嵌套
                dbQuery.Query.Select = new DbExpression(DbExpressionType.Select, expression);
                var cmd = (MappingCommand)this.ResolveSelectCommand(dbQuery.Query, 1, false, null);//, token);

                if ((cmd.NavMembers != null && cmd.NavMembers.Count > 0) || dbQuery.Query.Joins.Count > 0)
                {
                    // 无法使用 DISTINCT, GROUP BY 等子句从视图中选择 ROWID 或采样。UPDATE 不能用rowid
                    // 有导航属性或者关联查询,使用 MERGE INTO 语法。要求必须有主键

                    if (typeRuntime.KeyMembers == null || typeRuntime.KeyMembers.Count == 0)
                    {
                        throw new XFrameworkException("Update<T>(Expression<Func<T, object>> updateExpression) require entity must have key column.");
                    }

                    builder.Length = 0;
                    builder.Append("MERGE INTO ");
                    builder.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary);
                    builder.AppendNewLine(" t0");
                    builder.Append("USING (");

                    cmd = (MappingCommand)this.ResolveSelectCommand(dbQuery.Query, 1, false, token);
                    builder.AppendNewLine(cmd.CommandText);
                    builder.Append(") t1 ON (");
                    foreach (var m in typeRuntime.KeyMembers)
                    {
                        builder.AppendMember("t0", m.Name);
                        builder.Append(" = ");
                        builder.AppendMember("t1", m.Name);
                        builder.Append(" AND ");
                    }
                    builder.Length -= 5;
                    builder.Append(')');

                    // UPDATE
                    builder.AppendNewLine();
                    builder.AppendNewLine("WHEN MATCHED THEN UPDATE SET ");

                    // SET 字段
                    var visitor = new OracleUpdateExpressionVisitor(this, null, dbQuery.Expression);
                    visitor.Write(builder);
                }
                else
                {
                    // 直接 SQL 的 UPDATE 语法
                    TableAlias aliases = this.PrepareTableAlias(dbQuery.Query, token);
                    var        visitor = new UpdateExpressionVisitor(this, aliases, dbQuery.Expression);
                    visitor.Write(builder);

                    var visitor_ = new WhereExpressionVisitor(this, aliases, dbQuery.Query.Where);
                    visitor_.Write(builder);
                }
            }

            builder.Append(';');
            return(new RawCommand(builder.ToString(), builder.Token != null ? builder.Token.Parameters : null, System.Data.CommandType.Text));
        }
示例#21
0
        /// <summary>
        /// 创建 INSRT 命令
        /// </summary>
        /// <param name="dbQuery">查询语义</param>
        /// <param name="token">解析上下文</param>
        /// <returns></returns>
        protected override RawCommand ResolveInsertCommand <T>(IDbQueryableInfo_Insert dbQuery, ResolveToken token)
        {
            ISqlBuilder builder     = this.CreateSqlBuilder(token);
            TableAlias  aliases     = new TableAlias();
            var         typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo <T>();

            if (dbQuery.Entity != null)
            {
                // 如果没有Sequence列,使用 INSERT ALL INTO 语法,否则就一条一条逐行写入~~
                // 批量 INSERT,自增列不会自动赋值

                object      entity      = dbQuery.Entity;
                ISqlBuilder seg_Columns = this.CreateSqlBuilder(token);
                ISqlBuilder seg_Values  = this.CreateSqlBuilder(token);

                // 指定插入列
                MemberAccessorCollection memberAccessors = typeRuntime.Members;
                if (dbQuery.EntityColumns != null && dbQuery.EntityColumns.Count > 0)
                {
                    memberAccessors = new MemberAccessorCollection();
                    for (int i = 0; i < dbQuery.EntityColumns.Count; i++)
                    {
                        Expression curExpr = dbQuery.EntityColumns[i];
                        if (curExpr.NodeType == ExpressionType.Lambda)
                        {
                            curExpr = (curExpr as LambdaExpression).Body.ReduceUnary();
                        }
                        if (curExpr.NodeType != ExpressionType.MemberAccess)
                        {
                            throw new XFrameworkException("Can't read field name from expression {0}", dbQuery.EntityColumns[i]);
                        }

                        MemberExpression member = curExpr as MemberExpression;
                        string           name   = member.Member.Name;
                        memberAccessors[name] = typeRuntime.Members[name];
                    }
                }

                // 自增列参数
                IDbDataParameter seqParameter = null;
                // 自增列标记
                ColumnAttribute seqColumn = null;
                foreach (var m in memberAccessors)
                {
                    var column = m.Column;
                    if (column != null && column.NoMapped)
                    {
                        continue;
                    }
                    if (m.ForeignKey != null)
                    {
                        continue;
                    }
                    if (m.Member.MemberType == System.Reflection.MemberTypes.Method)
                    {
                        continue;
                    }

                    seg_Columns.AppendMember(m.Member.Name);
                    seg_Columns.Append(',');

                    if (m == typeRuntime.Identity)
                    {
                        seqColumn = column;
                        if (dbQuery.Bulk == null)
                        {
                            // 非批量INSERT,产生一个 OUTPUT 类型的参数
                            string pName    = string.Format("{0}p{1}", this.ParameterPrefix, token.Parameters.Count);
                            var    database = token.DbContext.Database;
                            seqParameter = database.CreateParameter(pName, -1, direction: ParameterDirection.Output);
                            token.Parameters.Add(seqParameter);
                            seg_Values.Append(seqParameter.ParameterName);
                            seg_Values.Append(',');
                        }
                        else
                        {
                            seg_Values.Append(((OracleColumnAttribute)column).SEQName);
                            seg_Values.Append(".NEXTVAL");
                            seg_Values.Append(',');
                        }
                    }
                    else
                    {
                        var    value = m.Invoke(entity);
                        string seg   = this.DbValue.GetSqlValueWidthDefault(value, token, column);
                        seg_Values.Append(seg);
                        seg_Values.Append(',');
                    }
                }
                seg_Columns.Length -= 1;
                seg_Values.Length  -= 1;

                if (dbQuery.Bulk == null)
                {
                    // 非批量INSERT,产生一个 OUTPUT 类型的参数
                    if (seqParameter != null)
                    {
                        seqParameter.Direction = ParameterDirection.Output;
                        seqParameter.DbType    = DbType.Int64;
                        builder.Append("SELECT ");
                        builder.Append(((OracleColumnAttribute)seqColumn).SEQName);
                        builder.Append(".NEXTVAL INTO ");
                        builder.Append(seqParameter.ParameterName);
                        builder.Append(" FROM DUAL;");
                        builder.AppendNewLine();
                        //useSEQ = true;
                    }
                    builder.Append("INSERT ");
                }
                else
                {
                    // 批量 INSERT
                    if (!dbQuery.Bulk.OnlyValue || seqColumn != null)
                    {
                        builder.Append("INSERT ");
                    }
                    // 如果有自增列则不使用 INSERT ALL INTO 语法
                    if (!dbQuery.Bulk.OnlyValue && seqColumn == null)
                    {
                        builder.Append("ALL ");
                    }
                }

                builder.Append("INTO ");
                builder.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary);
                builder.Append('(');
                builder.Append(seg_Columns);
                builder.Append(')');
                builder.AppendNewLine();
                builder.AppendTab();
                builder.Append("VALUES");
                builder.Append('(');
                builder.Append(seg_Values);
                builder.Append(')');

                if (dbQuery.Bulk == null)
                {
                    builder.Append(';');
                }
                else
                {
                    if (seqColumn != null)
                    {
                        if (dbQuery.Bulk.IsEndPos)
                        {
                            builder.Append(";");
                        }
                        else
                        {
                            builder.AppendNewLine(";");
                        }
                    }
                    else
                    {
                        builder.AppendNewLine();
                        if (dbQuery.Bulk.IsEndPos)
                        {
                            builder.Append("SELECT 1 FROM DUAL;");
                        }
                    }
                }
            }
            else if (dbQuery.Query != null)
            {
                builder.Append("INSERT INTO ");
                builder.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary);
                builder.Append('(');

                int            i   = 0;
                MappingCommand cmd = this.ResolveSelectCommand(dbQuery.Query, 0, false, token) as MappingCommand;
                foreach (var column in cmd.PickColumns)
                {
                    builder.AppendMember(column.Name);
                    if (i < cmd.PickColumns.Count - 1)
                    {
                        builder.Append(',');
                    }
                    i++;
                }

                builder.Append(')');
                builder.AppendNewLine();
                builder.Append(cmd.CommandText);
                builder.Append(';');
            }

            var result = new RawCommand(builder.ToString(), builder.Token != null ? builder.Token.Parameters : null, System.Data.CommandType.Text);

            return(result);
        }
示例#22
0
        // 创建 UPDATE 命令
        protected override Command ResolveUpdateCommand <T>(DbQueryableInfo_Update <T> dbQuery, ResolveToken token)
        {
            ISqlBuilder builder     = this.CreateSqlBuilder(token);
            var         typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo <T>();

            builder.Append("UPDATE ");
            builder.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary);
            builder.Append(" t0 SET");
            builder.AppendNewLine();

            if (dbQuery.Entity != null)
            {
                object      entity       = dbQuery.Entity;
                ISqlBuilder whereBuilder = this.CreateSqlBuilder(token);
                bool        useKey       = false;
                int         length       = 0;

                foreach (var invoker in typeRuntime.Invokers)
                {
                    var column = invoker.Column;
                    if (column != null && column.IsIdentity)
                    {
                        goto gotoLabel;                                      // fix issue# 自增列同时又是主键
                    }
                    if (column != null && column.NoMapped)
                    {
                        continue;
                    }
                    if (invoker.ForeignKey != null)
                    {
                        continue;
                    }
                    if (invoker.Member.MemberType == System.Reflection.MemberTypes.Method)
                    {
                        continue;
                    }

                    builder.AppendMember(invoker.Member.Name);
                    builder.Append(" = ");

gotoLabel:
                    var value = invoker.Invoke(entity);
                    var seg = this.DbValue.GetSqlValueWidthDefault(value, token, column);

                    if (column == null || !column.IsIdentity)
                    {
                        builder.Append(seg);
                        length = builder.Length;
                        builder.Append(',');
                        builder.AppendNewLine();
                    }

                    if (column != null && column.IsKey)
                    {
                        useKey = true;
                        whereBuilder.AppendMember(invoker.Member.Name);
                        whereBuilder.Append(" = ");
                        whereBuilder.Append(seg);
                        whereBuilder.Append(" AND ");
                    }
                }

                if (!useKey)
                {
                    throw new XFrameworkException("Update<T>(T value) require T must have key column.");
                }

                builder.Length       = length;
                whereBuilder.Length -= 5;

                builder.AppendNewLine();
                builder.Append("WHERE ");
                builder.Append(whereBuilder);
            }
            else if (dbQuery.Expression != null)
            {
                TableAliasCache       aliases = this.PrepareTableAlias <T>(dbQuery.SelectInfo, token);
                ExpressionVisitorBase visitor = null;
                visitor = new NpgUpdateExpressionVisitor(this, aliases, dbQuery.Expression);
                visitor.Write(builder);

                var cmd2 = new NpgMapperCommand(this, aliases, DbExpressionType.Update, token)
                {
                    HasMany = dbQuery.SelectInfo.HasMany
                };

                var visitor0 = new NpgJoinExpressionVisitor(this, aliases, dbQuery.SelectInfo.Joins, DbExpressionType.Update);
                visitor0.Write(cmd2);

                var visitor1 = new NpgWhereExpressionVisitor(this, aliases, dbQuery.SelectInfo.Condtion);
                visitor1.Write(cmd2.WhereFragment);
                cmd2.AddNavMembers(visitor1.NavMembers);
                builder.Append(cmd2.CommandText);
            }

            builder.Append(';');
            return(new Command(builder.ToString(), builder.Token != null ? builder.Token.Parameters : null, System.Data.CommandType.Text));
        }
示例#23
0
        // 创建 INSRT 命令
        protected override Command ResolveInsertCommand <T>(DbQueryableInfo_Insert <T> dbQuery, ResolveToken token)
        {
            ISqlBuilder     builder     = this.CreateSqlBuilder(token);
            TypeRuntimeInfo typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo <T>();
            TableAliasCache aliases     = new TableAliasCache();

            if (dbQuery.Entity != null)
            {
                object      entity         = dbQuery.Entity;
                ISqlBuilder columnsBuilder = this.CreateSqlBuilder(token);
                ISqlBuilder valuesBuilder  = this.CreateSqlBuilder(token);

                // 指定插入列
                MemberInvokerCollection invokers = typeRuntime.Invokers;
                if (dbQuery.EntityColumns != null && dbQuery.EntityColumns.Count > 0)
                {
                    invokers = new MemberInvokerCollection();
                    for (int i = 0; i < dbQuery.EntityColumns.Count; i++)
                    {
                        Expression curExpr = dbQuery.EntityColumns[i];
                        if (curExpr.NodeType == ExpressionType.Lambda)
                        {
                            curExpr = (curExpr as LambdaExpression).Body.ReduceUnary();
                        }
                        if (curExpr.NodeType != ExpressionType.MemberAccess)
                        {
                            throw new XFrameworkException("Can't read field name from expression {0}", dbQuery.EntityColumns[i]);
                        }

                        MemberExpression member = curExpr as MemberExpression;
                        string           name   = member.Member.Name;
                        invokers[name] = typeRuntime.Invokers[name];
                    }
                }

                foreach (var invoker in invokers)
                {
                    var column = invoker.Column;
                    if (column != null && column.NoMapped)
                    {
                        continue;
                    }
                    if (invoker.ForeignKey != null)
                    {
                        continue;
                    }
                    if (invoker.Member.MemberType == System.Reflection.MemberTypes.Method)
                    {
                        continue;
                    }

                    if (invoker != dbQuery.AutoIncrement)
                    {
                        columnsBuilder.AppendMember(invoker.Member.Name);
                        columnsBuilder.Append(',');

                        var    value = invoker.Invoke(entity);
                        string seg   = this.DbValue.GetSqlValueWidthDefault(value, token, column);
                        valuesBuilder.Append(seg);
                        valuesBuilder.Append(',');
                    }
                }
                columnsBuilder.Length -= 1;
                valuesBuilder.Length  -= 1;

                if (dbQuery.Bulk == null || !dbQuery.Bulk.OnlyValue)
                {
                    builder.Append("INSERT INTO ");
                    builder.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary);
                    builder.Append('(');
                    builder.Append(columnsBuilder);
                    builder.Append(')');
                    builder.AppendNewLine();
                    builder.AppendNewTab();
                    builder.Append("VALUES");
                }

                builder.Append('(');
                builder.Append(valuesBuilder);
                builder.Append(')');
                if (dbQuery.Bulk != null && !dbQuery.Bulk.IsEndPos)
                {
                    builder.Append(",");
                }

                if (dbQuery.Bulk == null && dbQuery.AutoIncrement != null)
                {
                    builder.Append(';');
                    builder.AppendNewLine();

                    // sequence,命名原则是 tablename_columnname_seq.
                    builder.AppendFormat("SELECT CURRVAL('{0}_{1}_seq')", typeRuntime.TableName, dbQuery.AutoIncrement.Member.Name);
                    builder.Append(" AS ");
                    builder.Append(this.QuotePrefix);
                    builder.Append(Constant.AUTOINCREMENTNAME);
                    builder.Append(this.QuoteSuffix);
                }
            }
            else if (dbQuery.SelectInfo != null)
            {
                builder.Append("INSERT INTO ");
                builder.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary);
                builder.Append('(');

                int           i    = 0;
                MapperCommand cmd2 = this.ResolveSelectCommandImpl(dbQuery.SelectInfo, 0, false, token) as MapperCommand;
                //for (int i = 0; i < seg.Columns.Count; i++)
                foreach (var column in cmd2.PickColumns)
                {
                    builder.AppendMember(column.Name);
                    if (i < cmd2.PickColumns.Count - 1)
                    {
                        builder.Append(',');
                    }
                    i++;
                }

                builder.Append(')');
                builder.AppendNewLine();
                builder.Append(cmd2.CommandText);
            }

            if (dbQuery.Bulk == null || dbQuery.Bulk.IsEndPos)
            {
                builder.Append(';');
            }
            return(new Command(builder.ToString(), builder.Token != null ? builder.Token.Parameters : null, System.Data.CommandType.Text));
        }
示例#24
0
        // 创建 DELETE 命令
        protected override Command ParseDeleteCommand <T>(DbQueryableInfo_Delete <T> dQueryInfo, ParserToken token)
        {
            TypeRuntimeInfo typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo <T>();
            ISqlBuilder     builder     = this.CreateSqlBuilder(token);
            bool            useKey      = false;

            builder.Append("DELETE t0 FROM ");
            builder.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary);
            builder.Append(" t0 ");

            if (dQueryInfo.Entity != null)
            {
                object entity = dQueryInfo.Entity;

                builder.AppendNewLine();
                builder.Append("WHERE ");

                foreach (var kv in typeRuntime.Invokers)
                {
                    MemberInvokerBase invoker = kv.Value;
                    var column = invoker.Column;

                    if (column != null && column.IsKey)
                    {
                        useKey = true;
                        var value = invoker.Invoke(entity);
                        var seg   = builder.GetSqlValue(value, column);
                        builder.AppendMember("t0", invoker.Member.Name);
                        builder.Append(" = ");
                        builder.Append(seg);
                        builder.Append(" AND ");
                    }
                    ;
                }
                builder.Length -= 5;

                if (!useKey)
                {
                    throw new XFrameworkException("Delete<T>(T value) require T must have key column.");
                }
            }
            else if (dQueryInfo.SelectInfo != null)
            {
                IDbQueryable    dbQueryable = dQueryInfo.SourceQuery;
                TableAliasCache aliases     = this.PrepareAlias <T>(dQueryInfo.SelectInfo, token);
                var             cmd2        = new SelectCommand(this, aliases, token)
                {
                    HasManyNavigation = dQueryInfo.SelectInfo.HasManyNavigation
                };

                ExpressionVisitorBase visitor = new JoinExpressionVisitor(this, aliases, dQueryInfo.SelectInfo.Joins);
                visitor.Write(cmd2.JoinFragment);

                visitor = new WhereExpressionVisitor(this, aliases, dQueryInfo.SelectInfo.WhereExpression);
                visitor.Write(cmd2.WhereFragment);
                cmd2.AddNavMembers(visitor.NavMembers);

                builder.Append(cmd2.CommandText);
            }

            return(new Command(builder.ToString(), builder.Token != null ? builder.Token.Parameters : null, System.Data.CommandType.Text));
        }
示例#25
0
        // 创建 UPDATE 命令
        protected override Command ParseUpdateCommand <T>(DbQueryableInfo_Update <T> uQueryInfo, ParserToken token)
        {
            ISqlBuilder builder     = this.CreateSqlBuilder(token);
            var         typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo <T>();

            builder.Append("UPDATE t0 SET");
            builder.AppendNewLine();

            if (uQueryInfo.Entity != null)
            {
                object      entity       = uQueryInfo.Entity;
                ISqlBuilder whereBuilder = this.CreateSqlBuilder(token);
                bool        useKey       = false;
                int         length       = 0;

                foreach (var kv in typeRuntime.Invokers)
                {
                    MemberInvokerBase invoker = kv.Value;
                    var column = invoker.Column;
                    if (column != null && column.IsIdentity)
                    {
                        goto gotoLabel;                                      // fix issue# 自增列同时又是主键
                    }
                    if (column != null && column.NoMapped)
                    {
                        continue;
                    }
                    if (invoker.Column != null && column.DbType is SqlDbType && (SqlDbType)column.DbType == SqlDbType.Timestamp)
                    {
                        continue;                                                                                                          // 行版本号
                    }
                    if (invoker.ForeignKey != null)
                    {
                        continue;
                    }
                    if (invoker.Member.MemberType == System.Reflection.MemberTypes.Method)
                    {
                        continue;
                    }

                    builder.AppendMember("t0", invoker.Member.Name);
                    builder.Append(" = ");

gotoLabel:
                    var value = invoker.Invoke(entity);
                    var seg = builder.GetSqlValueWidthDefault(value, column);

                    if (column == null || !column.IsIdentity)
                    {
                        builder.Append(seg);
                        length = builder.Length;
                        builder.Append(',');
                        builder.AppendNewLine();
                    }

                    if (column != null && column.IsKey)
                    {
                        useKey = true;
                        whereBuilder.AppendMember("t0", invoker.Member.Name);
                        whereBuilder.Append(" = ");
                        whereBuilder.Append(seg);
                        whereBuilder.Append(" AND ");
                    }
                }

                if (!useKey)
                {
                    throw new XFrameworkException("Update<T>(T value) require T must have key column.");
                }

                builder.Length       = length;
                whereBuilder.Length -= 5;

                builder.AppendNewLine();
                builder.Append("FROM ");
                builder.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary);
                builder.Append(" t0");


                builder.AppendNewLine();
                builder.Append("WHERE ");
                builder.Append(whereBuilder);
            }
            else if (uQueryInfo.Expression != null)
            {
                TableAliasCache       aliases = this.PrepareAlias <T>(uQueryInfo.SelectInfo, token);
                ExpressionVisitorBase visitor = null;
                visitor = new UpdateExpressionVisitor(this, aliases, uQueryInfo.Expression);
                visitor.Write(builder);

                builder.AppendNewLine();
                builder.Append("FROM ");
                builder.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary);
                builder.AppendAs("t0");

                var cmd2 = new SelectCommand(this, aliases, token)
                {
                    HasManyNavigation = uQueryInfo.SelectInfo.HasManyNavigation
                };

                visitor = new JoinExpressionVisitor(this, aliases, uQueryInfo.SelectInfo.Joins);
                visitor.Write(cmd2.JoinFragment);

                visitor = new WhereExpressionVisitor(this, aliases, uQueryInfo.SelectInfo.WhereExpression);
                visitor.Write(cmd2.WhereFragment);
                cmd2.AddNavMembers(visitor.NavMembers);

                builder.Append(cmd2.CommandText);
            }

            return(new Command(builder.ToString(), builder.Token != null ? builder.Token.Parameters : null, System.Data.CommandType.Text));
        }
示例#26
0
        // 创建 INSRT 命令
        protected override DbCommandDefinition ParseInsertCommand <T>(DbQueryableInfo_Insert <T> nQuery, List <IDbDataParameter> parameters = null)
        {
            ISqlBuilder     builder     = this.CreateSqlBuilder(parameters);
            TypeRuntimeInfo typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo <T>();
            TableAliasCache aliases     = new TableAliasCache();
            bool            useSEQ      = false;

            if (nQuery.Entity != null)
            {
                // 如果没有Sequence列,使用 INSERT ALL INTO 语法,否则就一条一条逐行写入~~
                // 批量 INSERT,自增列不会自动赋值

                object      entity         = nQuery.Entity;
                ISqlBuilder columnsBuilder = this.CreateSqlBuilder(builder.Parameters);
                ISqlBuilder valuesBuilder  = this.CreateSqlBuilder(builder.Parameters);

                // 指定插入列
                Dictionary <string, MemberInvokerBase> invokers = typeRuntime.Invokers;
                if (nQuery.EntityColumns != null && nQuery.EntityColumns.Count > 0)
                {
                    invokers = new Dictionary <string, MemberInvokerBase>();
                    for (int i = 0; i < nQuery.EntityColumns.Count; i++)
                    {
                        Expression curExpr = nQuery.EntityColumns[i];
                        if (curExpr.NodeType == ExpressionType.Lambda)
                        {
                            curExpr = (curExpr as LambdaExpression).Body.ReduceUnary();
                        }
                        if (curExpr.NodeType != ExpressionType.MemberAccess)
                        {
                            throw new XFrameworkException("Can't read field name from expression {0}", nQuery.EntityColumns[i]);
                        }

                        MemberExpression member = curExpr as MemberExpression;
                        string           name   = member.Member.Name;
                        invokers[name] = typeRuntime.Invokers[name];
                    }
                }

                // 自增列参数
                IDbDataParameter seqParameter = null;
                // 自增列标记
                ColumnAttribute seqColumn = null;
                foreach (var kv in invokers)
                {
                    MemberInvokerBase invoker = kv.Value;
                    var column = invoker.Column;
                    if (column != null && column.NoMapped)
                    {
                        continue;
                    }
                    if (invoker.ForeignKey != null)
                    {
                        continue;
                    }
                    if (invoker.Member.MemberType == System.Reflection.MemberTypes.Method)
                    {
                        continue;
                    }

                    columnsBuilder.AppendMember(invoker.Member.Name);
                    columnsBuilder.Append(',');

                    if (invoker == nQuery.AutoIncrement)
                    {
                        seqColumn = column;
                        if (nQuery.Bulk == null)
                        {
                            // 非批量INSERT,产生一个 OUTPUT 类型的参数
                            string pName    = string.Format("{0}p{1}", this.ParameterPrefix, builder.Parameters.Count);
                            var    database = nQuery.SourceQuery.DbContext.Database;
                            seqParameter = database.CreateParameter(pName, -1, direction: ParameterDirection.Output);
                            builder.Parameters.Add(seqParameter);
                            valuesBuilder.Append(seqParameter.ParameterName);
                            valuesBuilder.Append(',');
                        }
                        else
                        {
                            valuesBuilder.Append(((OracleColumnAttribute)column).SEQName);
                            valuesBuilder.Append(".NEXTVAL");
                            valuesBuilder.Append(',');
                        }
                    }
                    else
                    {
                        var    value = invoker.Invoke(entity);
                        string seg   = builder.GetSqlValueWidthDefault(value, column);
                        valuesBuilder.Append(seg);
                        valuesBuilder.Append(',');
                    }
                }
                columnsBuilder.Length -= 1;
                valuesBuilder.Length  -= 1;

                if (nQuery.Bulk == null)
                {
                    // 非批量INSERT,产生一个 OUTPUT 类型的参数
                    if (seqParameter != null)
                    {
                        seqParameter.Direction = ParameterDirection.Output;
                        seqParameter.DbType    = DbType.Int64;
                        builder.Append("SELECT ");
                        builder.Append(((OracleColumnAttribute)seqColumn).SEQName);
                        builder.Append(".NEXTVAL INTO ");
                        builder.Append(seqParameter.ParameterName);
                        builder.Append(" FROM DUAL;");
                        builder.AppendNewLine();
                        useSEQ = true;
                    }
                    builder.Append("INSERT ");
                }
                else
                {
                    // 批量 INSERT
                    if (!nQuery.Bulk.OnlyValue || seqColumn != null)
                    {
                        builder.Append("INSERT ");
                    }
                    // 如果有自增列则不使用 INSERT ALL INTO 语法
                    if (!nQuery.Bulk.OnlyValue && seqColumn == null)
                    {
                        builder.Append("ALL ");
                    }
                }

                builder.Append("INTO ");
                builder.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary);
                builder.Append('(');
                builder.Append(columnsBuilder);
                builder.Append(')');
                builder.AppendNewLine();
                builder.AppendNewTab();
                builder.Append("VALUES");
                builder.Append('(');
                builder.Append(valuesBuilder);
                builder.Append(')');

                if (nQuery.Bulk == null)
                {
                    builder.Append(';');
                }
                else
                {
                    if (seqColumn != null)
                    {
                        if (nQuery.Bulk.IsEndPos)
                        {
                            builder.Append(";");
                        }
                        else
                        {
                            builder.AppendNewLine(";");
                        }
                    }
                    else
                    {
                        builder.AppendNewLine();
                        if (nQuery.Bulk.IsEndPos)
                        {
                            builder.Append("SELECT 1 FROM DUAL;");
                        }
                    }
                }
            }
            else if (nQuery.SelectInfo != null)
            {
                builder.Append("INSERT INTO ");
                builder.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary);
                builder.Append('(');

                int i = 0;
                DbCommandDefinition_Select cmd2 = this.ParseSelectCommand(nQuery.SelectInfo, 0, false, builder.Parameters) as DbCommandDefinition_Select;
                foreach (var kvp in cmd2.Columns)
                {
                    builder.AppendMember(kvp.Key);
                    if (i < cmd2.Columns.Count - 1)
                    {
                        builder.Append(',');
                    }
                    i++;
                }

                builder.Append(')');
                builder.AppendNewLine();
                builder.Append(cmd2.CommandText);
                builder.Append(';');
            }

            var cmd = new OracleDbCommandDefinition_Insert(builder.ToString(), builder.Parameters, System.Data.CommandType.Text);

            cmd.HaveSEQ = useSEQ;
            return(cmd);
        }
示例#27
0
        /// <summary>
        /// 创建 INSRT 命令
        /// </summary>
        /// <param name="dbQuery">查询语义</param>
        /// <param name="token">解析上下文</param>
        /// <returns></returns>
        protected override RawCommand ResolveInsertCommand <T>(IDbQueryableInfo_Insert dbQuery, ResolveToken token)
        {
            TableAlias  aliases     = new TableAlias();
            ISqlBuilder builder     = this.CreateSqlBuilder(token);
            var         typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo <T>();

            if (dbQuery.Entity != null)
            {
                object      entity      = dbQuery.Entity;
                ISqlBuilder seg_Columns = this.CreateSqlBuilder(token);
                ISqlBuilder seg_Values  = this.CreateSqlBuilder(token);

                // 指定插入列
                MemberAccessorCollection memberAccessors = typeRuntime.Members;
                if (dbQuery.EntityColumns != null && dbQuery.EntityColumns.Count > 0)
                {
                    memberAccessors = new MemberAccessorCollection();
                    for (int i = 0; i < dbQuery.EntityColumns.Count; i++)
                    {
                        Expression curExpr = dbQuery.EntityColumns[i];
                        if (curExpr.NodeType == ExpressionType.Lambda)
                        {
                            curExpr = (curExpr as LambdaExpression).Body.ReduceUnary();
                        }
                        if (curExpr.NodeType != ExpressionType.MemberAccess)
                        {
                            throw new XFrameworkException("Can't read field name from expression {0}", dbQuery.EntityColumns[i]);
                        }

                        MemberExpression member = curExpr as MemberExpression;
                        string           name   = member.Member.Name;
                        memberAccessors[name] = typeRuntime.Members[name];
                    }
                }

                foreach (var m in memberAccessors)
                {
                    var column = m.Column;
                    if (column != null && column.NoMapped)
                    {
                        continue;
                    }
                    if (m.ForeignKey != null)
                    {
                        continue;
                    }
                    if (m.Member.MemberType == System.Reflection.MemberTypes.Method)
                    {
                        continue;
                    }

                    if (m != typeRuntime.Identity)
                    {
                        seg_Columns.AppendMember(m.Member.Name);
                        seg_Columns.Append(',');

                        var    value = m.Invoke(entity);
                        string seg   = this.DbValue.GetSqlValueWidthDefault(value, token, column);
                        seg_Values.Append(seg);
                        seg_Values.Append(',');
                    }
                }
                seg_Columns.Length -= 1;
                seg_Values.Length  -= 1;

                if (dbQuery.Bulk == null || !dbQuery.Bulk.OnlyValue)
                {
                    builder.Append("INSERT INTO ");
                    builder.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary);
                    builder.Append('(');
                    builder.Append(seg_Columns);
                    builder.Append(')');
                    builder.AppendNewLine();
                    builder.AppendTab();
                    builder.Append("VALUES");
                }

                builder.Append('(');
                builder.Append(seg_Values);
                builder.Append(')');
                if (dbQuery.Bulk != null && !dbQuery.Bulk.IsEndPos)
                {
                    builder.Append(",");
                }

                if (dbQuery.Bulk == null && typeRuntime.Identity != null)
                {
                    builder.Append(';');
                    builder.AppendNewLine();

                    builder.AppendFormat("SELECT LAST_INSERT_ROWID()");
                    builder.Append(" AS ");
                    builder.Append(this.QuotePrefix);
                    builder.Append(Constant.AUTO_INCREMENT_NAME);
                    builder.Append(this.QuoteSuffix);
                }
            }
            else if (dbQuery.Query != null)
            {
                builder.Append("INSERT INTO ");
                builder.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary);
                builder.Append('(');

                int            i   = 0;
                MappingCommand cmd = this.ResolveSelectCommandImpl(dbQuery.Query, 0, false, token) as MappingCommand;
                //for (int i = 0; i < seg.Columns.Count; i++)
                foreach (var column in cmd.PickColumns)
                {
                    builder.AppendMember(column.Name);
                    if (i < cmd.PickColumns.Count - 1)
                    {
                        builder.Append(',');
                    }
                    i++;
                }

                builder.Append(')');
                builder.AppendNewLine();
                builder.Append(cmd.CommandText);
            }

            if (dbQuery.Bulk == null || dbQuery.Bulk.IsEndPos)
            {
                builder.Append(';');
            }
            return(new RawCommand(builder.ToString(), builder.Token != null ? builder.Token.Parameters : null, System.Data.CommandType.Text));
        }
示例#28
0
        // 创建 UPDATE 命令
        protected override DbCommandDefinition ParseUpdateCommand <T>(DbQueryableInfo_Update <T> uQuery, List <IDbDataParameter> parameters = null)
        {
            ISqlBuilder builder     = this.CreateSqlBuilder(parameters);
            var         typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo <T>();

            builder.Append("UPDATE ");
            builder.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary);
            builder.Append(" t0 SET");
            builder.AppendNewLine();

            if (uQuery.Entity != null)
            {
                object      entity       = uQuery.Entity;
                ISqlBuilder whereBuilder = this.CreateSqlBuilder(builder.Parameters);
                bool        useKey       = false;
                int         length       = 0;


                foreach (var kv in typeRuntime.Invokers)
                {
                    MemberInvokerBase invoker = kv.Value;
                    var column = invoker.Column;
                    if (column != null && column.IsIdentity)
                    {
                        goto gotoLabel;                                      // fix issue# 自增列同时又是主键
                    }
                    if (column != null && column.NoMapped)
                    {
                        continue;
                    }
                    if (invoker.ForeignKey != null)
                    {
                        continue;
                    }
                    if (invoker.Member.MemberType == System.Reflection.MemberTypes.Method)
                    {
                        continue;
                    }

                    builder.AppendMember(invoker.Member.Name);
                    builder.Append(" = ");

gotoLabel:

                    if (column == null || !column.IsIdentity)
                    {
                        var value = invoker.Invoke(entity);
                        var seg   = builder.GetSqlValueWidthDefault(value, column);

                        builder.Append(seg);
                        length = builder.Length;
                        builder.Append(',');
                        builder.AppendNewLine();
                    }

                    if (column != null && column.IsKey)
                    {
                        useKey = true;
                    }
                }

                if (!useKey)
                {
                    throw new XFrameworkException("Update<T>(T value) require T must have key column.");
                }


                // ORACLE 需要注意参数顺序问题
                int index = -1;
                foreach (var kv in typeRuntime.KeyInvokers)
                {
                    MemberInvokerBase invoker = kv.Value;
                    var column = invoker.Column;
                    var value  = invoker.Invoke(entity);
                    var seg    = builder.GetSqlValueWidthDefault(value, column);
                    index += 1;

                    whereBuilder.AppendMember(invoker.Member.Name);
                    whereBuilder.Append(" = ");
                    whereBuilder.Append(seg);
                    if (index < typeRuntime.KeyInvokers.Count - 1)
                    {
                        whereBuilder.Append(" AND ");
                    }
                }

                builder.Length = length;
                builder.AppendNewLine();
                builder.Append("WHERE ");
                builder.Append(whereBuilder);
            }
            else if (uQuery.Expression != null)
            {
                TableAliasCache       aliases = this.PrepareAlias <T>(uQuery.SelectInfo);
                ExpressionVisitorBase visitor = null;
                visitor = new OracleUpdateExpressionVisitor(this, aliases, uQuery.Expression);
                visitor.Write(builder);

                var cmd2 = new OracleDbCommandDefinition_Delete(this, aliases, builder.Parameters);
                cmd2.HaveListNavigation = uQuery.SelectInfo.HaveListNavigation;

                var visitor0 = new OracleJoinExpressionVisitor(this, aliases, uQuery.SelectInfo.Join, uQuery.SelectInfo.Where);
                visitor0.Write(cmd2);

                var visitor1 = new OracleWhereExpressionVisitor(this, aliases, uQuery.SelectInfo.Where);
                visitor1.Write(cmd2.WhereFragment);
                cmd2.AddNavMembers(visitor1.NavMembers);
                builder.Append(cmd2.CommandText);
            }

            builder.Append(';');
            return(new DbCommandDefinition(builder.ToString(), builder.Parameters, System.Data.CommandType.Text));
        }
示例#29
0
        /// <summary>
        /// 创建 UPDATE 命令
        /// </summary>
        /// <param name="dbQuery">查询语义</param>
        /// <param name="token">解析上下文</param>
        /// <returns></returns>
        protected override RawCommand ResolveUpdateCommand <T>(IDbQueryableInfo_Update dbQuery, ResolveToken token)
        {
            ISqlBuilder builder     = this.CreateSqlBuilder(token);
            var         typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo <T>();

            builder.Append("UPDATE ");
            builder.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary);
            builder.Append(" SET");
            builder.AppendNewLine();

            if (dbQuery.Entity != null)
            {
                object      entity    = dbQuery.Entity;
                ISqlBuilder seg_Where = this.CreateSqlBuilder(token);
                bool        useKey    = false;
                int         length    = 0;

                foreach (var m in typeRuntime.Members)
                {
                    var column = m.Column;
                    if (column != null && column.IsIdentity)
                    {
                        goto gotoLabel;                                      // Fix issue# 自增列同时又是主键
                    }
                    if (column != null && column.NoMapped)
                    {
                        continue;
                    }
                    if (m.Column != null && column.DbType is SqlDbType && (SqlDbType)column.DbType == SqlDbType.Timestamp)
                    {
                        continue;                                                                                                    // 行版本号
                    }
                    if (m.ForeignKey != null)
                    {
                        continue;
                    }
                    if (m.Member.MemberType == System.Reflection.MemberTypes.Method)
                    {
                        continue;
                    }

                    builder.AppendMember(m.Member.Name);
                    builder.Append(" = ");

gotoLabel:
                    var value = m.Invoke(entity);
                    var seg = this.DbValue.GetSqlValueWidthDefault(value, token, column);

                    if (column == null || !column.IsIdentity)
                    {
                        builder.Append(seg);
                        length = builder.Length;
                        builder.Append(',');
                        builder.AppendNewLine();
                    }

                    if (column != null && column.IsKey)
                    {
                        useKey = true;
                        seg_Where.AppendMember(m.Member.Name);
                        seg_Where.Append(" = ");
                        seg_Where.Append(seg);
                        seg_Where.Append(" AND ");
                    }
                }

                if (!useKey)
                {
                    throw new XFrameworkException("Update<T>(T value) require entity must have key column.");
                }

                builder.Length    = length;
                seg_Where.Length -= 5;

                builder.AppendNewLine();
                builder.Append("WHERE ");
                builder.Append(seg_Where);
            }
            else if (dbQuery.Expression != null)
            {
                // SELECT 表达式
                LambdaExpression lambda = dbQuery.Expression as LambdaExpression;
                var        body         = lambda.Body;
                Expression expression   = null;
                if (body.NodeType == ExpressionType.MemberInit)
                {
                    var memberInit = body as MemberInitExpression;
                    var bindings   = new List <MemberBinding>(memberInit.Bindings);
                    foreach (var m in typeRuntime.KeyMembers)
                    {
                        var member  = Expression.MakeMemberAccess(lambda.Parameters[0], m.Member);
                        var binding = Expression.Bind(m.Member, member);
                        if (!bindings.Any(x => x.Member == m.Member))
                        {
                            bindings.Add(binding);
                        }
                    }
                    expression = Expression.MemberInit(memberInit.NewExpression, bindings);
                }
                else if (body.NodeType == ExpressionType.New)
                {
                    var newExpression = body as NewExpression;
                    var bindings      = new List <MemberBinding>();
                    for (int i = 0; i < newExpression.Members.Count; i++)
                    {
                        var m       = typeRuntime.GetMember(newExpression.Members[i].Name);
                        var binding = Expression.Bind(m.Member, newExpression.Arguments[i].Type != m.DataType
                            ? Expression.Convert(newExpression.Arguments[i], m.DataType)
                            : newExpression.Arguments[i]);
                        bindings.Add(binding);
                    }

                    foreach (var m in typeRuntime.KeyMembers)
                    {
                        var member  = Expression.MakeMemberAccess(lambda.Parameters[0], m.Member);
                        var binding = Expression.Bind(m.Member, member);
                        if (!bindings.Any(x => x.Member == m.Member))
                        {
                            bindings.Add(binding);
                        }
                    }

                    var newExpression2 = Expression.New(typeRuntime.Constructor.Constructor);
                    expression = Expression.MemberInit(newExpression2, bindings);
                }

                // 解析查询以确定是否需要嵌套
                dbQuery.Query.Select = new DbExpression(DbExpressionType.Select, expression);
                var cmd = (MappingCommand)this.ResolveSelectCommand(dbQuery.Query, 1, false, null);

                if ((cmd.NavMembers != null && cmd.NavMembers.Count > 0) || dbQuery.Query.Joins.Count > 0)
                {
                    if (typeRuntime.KeyMembers == null || typeRuntime.KeyMembers.Count == 0)
                    {
                        throw new XFrameworkException("Update<T>(Expression<Func<T, object>> updateExpression) require entity must have key column.");
                    }

                    // SET 字段
                    var visitor = new SQLiteUpdateExpressionVisitor(this, null, dbQuery, null);
                    visitor.ParseCommand = this.ResolveSelectCommand;
                    visitor.Write(builder);

                    // WHERE部分
                    builder.AppendNewLine();
                    builder.Append("WHERE EXISTS");
                    visitor.VisitArgument(dbQuery.Query.Select.Expressions[0], true);
                }
                else
                {
                    // 直接 SQL 的 UPDATE 语法
                    TableAlias aliases = this.PrepareTableAlias(dbQuery.Query, token);
                    var        visitor = new SQLiteUpdateExpressionVisitor(this, aliases, dbQuery, null);
                    visitor.ParseCommand = this.ResolveSelectCommand;
                    visitor.Write(builder);

                    var visitor2 = new WhereExpressionVisitor(this, null, dbQuery.Query.Where);
                    visitor2.Write(builder);
                }
            }

            builder.Append(';');
            return(new RawCommand(builder.ToString(), builder.Token != null ? builder.Token.Parameters : null, System.Data.CommandType.Text));
        }
        // 创建 SELECT 命令
        DbCommandDefinition ParseSelectCommandImpl <T>(DbQueryableInfo_Select <T> sQuery, int indent, bool isOuter, List <IDbDataParameter> parameters = null)
        {
            // 说明:
            // 1.OFFSET 前必须要有 'ORDER BY',即 'Skip' 子句前必须使用 'OrderBy' 子句
            // 2.在有统计函数的<MAX,MIN...>情况下,如果有 'Distinct' 'GroupBy' 'Skip' 'Take' 子句,则需要使用嵌套查询
            // 3.'Any' 子句将翻译成 IF EXISTS...
            // 4.分组再分页时需要使用嵌套查询,此时子查询不需要 'OrderBy' 子句,但最外层则需要
            // 5.'Skip' 'Take' 子句视为语义结束符,在其之后的子句将使用嵌套查询
            // 6.导航属性中有 1:n 关系的,需要使用嵌套查询,否则分页查询会有问题
            // 8.如果只有 Skip 没有 Take,则使用 Row_Number() Over()分页语法,其它使用 LIMIT ## OFFSET 语法


            // 导航属性中有1:n关系,只统计主表
            // 例:AccountList = a.Client.AccountList,
            DbQueryableInfo_Select <T> innerQuery = sQuery.SubQueryInfo as DbQueryableInfo_Select <T>;

            if (sQuery.HaveListNavigation && innerQuery != null && innerQuery.Statis != null)
            {
                sQuery = innerQuery;
            }

            bool useNesting = sQuery.HaveDistinct || sQuery.GroupBy != null || sQuery.Skip > 0 || sQuery.Take > 0;
            bool useStatis  = sQuery.Statis != null;
            // 没有统计函数或者使用 'Skip' 子句,则解析OrderBy
            // 导航属性如果使用嵌套,除非有 TOP 或者 OFFSET 子句,否则不能用ORDER BY
            bool useOrderBy = (!useStatis || sQuery.Skip > 0) && !sQuery.HaveAny && (!sQuery.GenByListNavigation || (sQuery.Skip > 0 || sQuery.Take > 0));

            IDbQueryable              dbQueryable = sQuery.SourceQuery;
            TableAliasCache           aliases     = this.PrepareAlias <T>(sQuery);
            SelectDbCommandDefinition cmd         = new SelectDbCommandDefinition(this, aliases, parameters)
            {
                HaveListNavigation = sQuery.HaveListNavigation
            };
            ISqlBuilder jf = cmd.JoinFragment;
            ISqlBuilder wf = cmd.WhereFragment;
            ISqlBuilder sf = null;

            jf.Indent = indent;

            #region 嵌套查询

            if (useStatis && useNesting)
            {
                // SELECT
                jf.Append("SELECT ");
                jf.AppendNewLine();

                // SELECT COUNT(1)
                var visitor2 = new StatisExpressionVisitor(this, aliases, sQuery.Statis, sQuery.GroupBy, "t0");
                visitor2.Write(jf);
                cmd.AddNavMembers(visitor2.NavMembers);

                // SELECT COUNT(1) FROM
                jf.AppendNewLine();
                jf.Append("FROM ( ");

                indent   += 1;
                jf.Indent = indent;
            }

            #endregion

            #region  择子句

            // SELECT 子句
            if (jf.Indent > 0)
            {
                jf.AppendNewLine();
            }
            jf.Append("SELECT ");

            if (sQuery.HaveAny)
            {
                jf.Append("CASE WHEN COUNT(1) = 1 THEN 1 ELSE 0 END FROM (");
                indent   += 1;
                jf.Indent = indent;
                jf.AppendNewLine();
                jf.Append("SELECT 1 ");
            }

            if (useStatis && !useNesting)
            {
                // 如果有统计函数,并且不是嵌套的话,则直接使用SELECT <MAX,MIN...>,不需要解析选择的字段
                jf.AppendNewLine();
                var visitor2 = new StatisExpressionVisitor(this, aliases, sQuery.Statis, sQuery.GroupBy);
                visitor2.Write(jf);
                cmd.AddNavMembers(visitor2.NavMembers);
            }
            else
            {
                // DISTINCT 子句
                if (sQuery.HaveDistinct)
                {
                    jf.Append("DISTINCT ");
                }

                #region  择字段

                if (!sQuery.HaveAny)
                {
                    // SELECT 范围
                    var visitor2 = new ColumnExpressionVisitor(this, aliases, sQuery);
                    if (sQuery.Skip > 0 && sQuery.Take == 0)
                    {
                        sf        = this.CreateSqlBuilder(parameters);
                        sf.Indent = jf.Indent + 1;
                        visitor2.Write(sf);
                    }
                    else
                    {
                        visitor2.Write(jf);
                    }

                    cmd.Columns     = visitor2.Columns;
                    cmd.Navigations = visitor2.Navigations;
                    cmd.AddNavMembers(visitor2.NavMembers);

                    if (sf != null)
                    {
                        // 第一层嵌套
                        int index = 0;
                        jf.AppendNewLine();
                        foreach (var entry in cmd.Columns)
                        {
                            jf.AppendMember("t0", entry.Key);
                            jf.AppendAs(entry.Key);
                            index += 1;
                            if (index < cmd.Columns.Count)
                            {
                                jf.Append(',');
                                jf.AppendNewLine();
                            }
                        }

                        jf.AppendNewLine();
                        jf.Append("FROM(");

                        // 第二层嵌套
                        indent   += 1;
                        jf.Indent = indent;
                        jf.AppendNewLine();
                        jf.Append("SELECT");
                        jf.Append(sf);
                        jf.Append(',');
                        jf.AppendNewLine();

                        if (sQuery.OrderBy.Count == 0)
                        {
                            throw new XFrameworkException("The method 'OrderBy' must be called before 'Skip'.");
                        }
                        jf.Append("ROW_NUMBER() OVER(");
                        var visitor3 = new OrderByExpressionVisitor(this, aliases, sQuery.OrderBy, sQuery.GroupBy);
                        visitor3.Write(jf, false);
                        cmd.AddNavMembers(visitor3.NavMembers);
                        jf.Append(") Row_Number0");
                    }
                }

                #endregion
            }

            #endregion

            #region 顺序解析

            // FROM 子句
            jf.AppendNewLine();
            jf.Append("FROM ");
            if (sQuery.SubQueryInfo != null)
            {
                // 子查询
                jf.Append("(");
                var cmd2 = this.ParseSelectCommandImpl <T>(sQuery.SubQueryInfo as DbQueryableInfo_Select <T>, indent + 1, false, jf.Parameters);
                jf.Append(cmd2.CommandText);
                jf.AppendNewLine();
                jf.Append(") t0 ");
            }
            else
            {
                var typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo(sQuery.FromType);
                jf.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary);
                jf.Append(" t0 ");
                //if (dbQueryable.DbContext.NoLock && !string.IsNullOrEmpty(this.WidthNoLock)) jf.Append(this.WidthNoLock);
            }

            // LEFT<INNER> JOIN 子句
            ExpressionVisitorBase visitor = new JoinExpressionVisitor(this, aliases, sQuery.Join);
            visitor.Write(jf);

            wf.Indent = jf.Indent;

            // WHERE 子句
            visitor = new WhereExpressionVisitor(this, aliases, sQuery.Where);
            visitor.Write(wf);
            cmd.AddNavMembers(visitor.NavMembers);

            // GROUP BY 子句
            visitor = new GroupByExpressionVisitor(this, aliases, sQuery.GroupBy);
            visitor.Write(wf);
            cmd.AddNavMembers(visitor.NavMembers);

            // HAVING 子句
            visitor = new HavingExpressionVisitor(this, aliases, sQuery.Having, sQuery.GroupBy);
            visitor.Write(wf);
            cmd.AddNavMembers(visitor.NavMembers);

            // ORDER 子句
            if (sQuery.OrderBy.Count > 0 && useOrderBy)// && !groupByPaging)
            {
                visitor = new OrderByExpressionVisitor(this, aliases, sQuery.OrderBy, sQuery.GroupBy);
                visitor.Write(wf);
                cmd.AddNavMembers(visitor.NavMembers);
            }

            #endregion

            #region 分页查询

            // LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。
            // LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。如果给定两个参数,第一个参数指定第一个返回记录行的偏移量,第二个参数指定返回记录行的最大数目。
            // 初始记录行的偏移量是 0(而不是 1): 为了与 PostgreSQL 兼容,MySQL 也支持句法: LIMIT # OFFSET #。
            // Limit n,-1 语法不支持,使用ROW_Number()语法代替

            if (sQuery.Take > 0)
            {
                wf.AppendNewLine().AppendFormat("LIMIT {0}", wf.GetSqlValue(sQuery.Take));
                wf.AppendFormat(" OFFSET {0}", wf.GetSqlValue(sQuery.Skip));
            }

            #endregion

            #region 嵌套查询

            if (useStatis && useNesting)
            {
                cmd.Convergence();
                indent   -= 1;
                jf.Indent = indent;
                jf.AppendNewLine();
                jf.Append(" ) t0");
            }

            #endregion

            #region 嵌套导航

            // TODO Include 从表,没分页,OrderBy 报错
            if (sQuery.HaveListNavigation && innerQuery != null && innerQuery.OrderBy.Count > 0 && innerQuery.Statis == null && !(innerQuery.Skip > 0 || innerQuery.Take > 0))
            {
                cmd.Convergence();
                visitor = new OrderByExpressionVisitor(this, aliases, innerQuery.OrderBy);//, null, "t0");
                visitor.Write(jf);
            }

            #endregion

            #region 并集查询

            // UNION 子句
            if (sQuery.Union != null && sQuery.Union.Count > 0)
            {
                cmd.Convergence();
                for (int index = 0; index < sQuery.Union.Count; index++)
                {
                    jf.AppendNewLine();
                    jf.Append("UNION ALL");
                    if (indent == 0)
                    {
                        jf.AppendNewLine();
                    }
                    DbCommandDefinition cmd2 = this.ParseSelectCommandImpl <T>(sQuery.Union[index] as DbQueryableInfo_Select <T>, indent, isOuter, jf.Parameters);
                    jf.Append(cmd2.CommandText);
                }
            }

            #endregion

            #region 分页查询

            if (sf != null)
            {
                // 合并 WHERE
                cmd.Convergence();

                indent   -= 1;
                jf.Indent = indent;
                jf.AppendNewLine();
                jf.Append(") t0");

                jf.AppendNewLine();
                jf.Append("WHERE ");
                if (sQuery.Skip > 0)
                {
                    jf.Append("t0.Row_Number0 > ");
                    jf.Append(jf.GetSqlValue(sQuery.Skip));
                }
            }

            #endregion

            #region Any 子句

            // 'Any' 子句
            if (sQuery.HaveAny)
            {
                // 产生 WHERE 子句
                cmd.Convergence();
                // 如果没有分页,则显式指定只查一笔记录
                if (sQuery.Take == 0 && sQuery.Skip == 0)
                {
                    jf.AppendNewLine();
                    jf.Append("LIMIT 0,1");
                }

                indent   -= 1;
                jf.Indent = indent;
                jf.AppendNewLine();
                jf.Append(") t0");
            }

            #endregion

            return(cmd);
        }