Beispiel #1
0
        public SelectWriterResult GenerateSql <T>(SelectQuery <T> selectQuery, AutoNamingDynamicParameters parameters = null, bool enforceAlias = false)
            where T : class, new()
        {
            // get fetch tree structure
            var rootNode = this.fetchTreeParser.GetFetchTree(selectQuery, out _, out var numberCollectionFetches);

            return(this.InnerGenerateSql(selectQuery, parameters ?? new AutoNamingDynamicParameters(), enforceAlias, rootNode, numberCollectionFetches, new StringBuilder(), false));
        }
Beispiel #2
0
        private SqlWriterResult InnerGenerateCountSql <T>(SelectQuery <T> selectQuery, FetchNode rootNode, int numberCollectionFetches)
            where T : class, new()   // add where clause
        {
            var whereSql   = new StringBuilder();
            var parameters = new AutoNamingDynamicParameters();

            this.AddWhereClause(selectQuery.WhereClauses, whereSql, parameters, ref rootNode);

            // add in the tables
            var columnSql = new StringBuilder();
            var tableSql  = new StringBuilder();

            this.AddTables(selectQuery, tableSql, columnSql, rootNode, false);

            var sql = new StringBuilder(15 + tableSql.Length + whereSql.Length);

            sql.Append("select count(");

            if (numberCollectionFetches == 0)
            {
                sql.Append("1");
            }
            else
            {
                sql.Append("distinct ");

                if (rootNode != null)
                {
                    sql.Append(rootNode.Alias);
                    sql.Append('.');
                }

                this.Dialect.AppendQuotedName(
                    sql,
                    this.Configuration.GetMap <T>()
                    .PrimaryKey.DbName);
            }

            sql.Append(")");
            sql.Append(tableSql);
            sql.Append(whereSql);

            return(new SqlWriterResult(sql.ToString(), parameters));
        }
Beispiel #3
0
        public SelectWriterResult GenerateSql <T>(IEnumerable <Expression <Func <T, bool> > > whereClauses, FetchNode rootNode, AutoNamingDynamicParameters parameters)
        {
            if (whereClauses.IsEmpty())
            {
                return(new SelectWriterResult(string.Empty, null, rootNode));
            }

            this.InitVariables();
            this.autoNamingDynamicParameters = parameters;
            this.modifiedRootNode            = rootNode;

            foreach (var whereClause in whereClauses)
            {
                this.VisitWhereClause(whereClause);
                this.sqlElements.Enqueue(new StringElement(" and "));
            }

            this.InferInnerJoins();
            return(new SelectWriterResult(this.GetSql(), this.autoNamingDynamicParameters, this.modifiedRootNode));
        }
Beispiel #4
0
        public SqlWriterResult GenerateBulkSql <T>(Action <T> updateAction, IEnumerable <Expression <Func <T, bool> > > predicates)
            where T : class, new()
        {
            var predicateArray = predicates as Expression <Func <T, bool> >[] ?? predicates?.ToArray();

            // add where clause
            var       whereSql   = new StringBuilder();
            var       parameters = new AutoNamingDynamicParameters();
            FetchNode rootNode   = null;

            if (predicateArray != null)
            {
                this.AddWhereClause(predicateArray, whereSql, parameters, ref rootNode);
            }

            // run the update
            var entity = new T();

            ((ISetLogger)entity).EnableSetLogging();
            updateAction(entity);

            // find the set properties
            var setLogger = (ISetLogger)entity;
            var setProps  = setLogger.GetSetProperties();

            if (!setProps.Any())
            {
                return(new SqlWriterResult(string.Empty, parameters));
            }

            if (rootNode == null)
            {
                // the where clauses on are on the root table
                return(new SqlWriterResult(this.GetSimpleUpdateQuery(setProps, entity, parameters, whereSql), parameters));
            }

            // cross table where clause
            return(new SqlWriterResult(this.GetMultiTableUpdateQuery(setProps, entity, parameters, whereSql, rootNode), parameters));
        }
Beispiel #5
0
        public SqlWriterResult GenerateBulkSql <T>(IEnumerable <Expression <Func <T, bool> > > predicates)
        {
            var predicateArray = predicates as Expression <Func <T, bool> >[] ?? predicates?.ToArray();

            // add where clause
            var       whereSql   = new StringBuilder();
            var       parameters = new AutoNamingDynamicParameters();
            FetchNode rootNode   = null;

            if (predicateArray != null)
            {
                this.AddWhereClause(predicateArray, whereSql, parameters, ref rootNode);
            }

            if (rootNode == null)
            {
                // the where clauses were all on the root table
                return(new SqlWriterResult(this.GetSimpleDeleteQuery <T>(whereSql), parameters));
            }

            // cross table where clause
            return(new SqlWriterResult(this.GetMultiTableDeleteQuery <T>(whereSql, rootNode), parameters));
        }
Beispiel #6
0
        internal void AddWhereClause <T>(IList <Expression <Func <T, bool> > > whereClauses, StringBuilder sql, AutoNamingDynamicParameters parameters, ref FetchNode rootNode)
        {
            var whereClauseWriter = new WhereClauseWriter(this.Dialect, this.Configuration);
            var result            = whereClauseWriter.GenerateSql(whereClauses, rootNode, parameters);

            if (result.Sql.Length > 0)
            {
                sql.Append(result.Sql);
            }

            rootNode = result.FetchTree;
        }
Beispiel #7
0
        private FetchNode GenerateNoPagingSql <T>(SelectQuery <T> selectQuery, bool enforceAlias, FetchNode rootNode, StringBuilder sql, int numberCollectionFetches, AutoNamingDynamicParameters parameters, bool isProjectedQuery)
            where T : class, new()
        {
            var columnSql = new StringBuilder();
            var tableSql  = new StringBuilder();
            var whereSql  = new StringBuilder();
            var orderSql  = new StringBuilder();

            if (rootNode == null && enforceAlias)
            {
                rootNode = new FetchNode();
            }

            // add where clause
            this.AddWhereClause(selectQuery.WhereClauses, whereSql, parameters, ref rootNode);

            // add select columns
            this.AddRootColumns(selectQuery, columnSql, rootNode, isProjectedQuery); // do columns second as we may not be fetching but need joins for the where clause

            // add in the tables
            this.AddTables(selectQuery, tableSql, columnSql, rootNode, isProjectedQuery);

            // add order by
            if (selectQuery.OrderClauses.Any())
            {
                var containsPrimaryKeyClause = this.AddOrderByClause(selectQuery.OrderClauses, orderSql, rootNode);
                if (numberCollectionFetches > 0 && !containsPrimaryKeyClause)
                {
                    this.AppendDefaultOrderBy <T>(rootNode, orderSql, isFirstOrderClause: false);
                }
            }
            else if (numberCollectionFetches > 0 || selectQuery.SkipN > 0 || selectQuery.TakeN > 0)
            {
                // need to add a default order on the sort clause
                this.AppendDefaultOrderBy <T>(rootNode, orderSql);
            }

            // construct the query
            sql.Append("select ");
            sql.Append(columnSql);
            sql.Append(tableSql);
            sql.Append(whereSql);
            sql.Append(orderSql);
            //// if anything is added after orderSql then the paging will probably need changing

            // apply paging
            // only add paging to the query if it doesn't have any collection fetches
            if (selectQuery.TakeN > 0 || selectQuery.SkipN > 0)
            {
                this.Dialect.ApplySkipTake(sql, orderSql, selectQuery.TakeN, selectQuery.SkipN);
                if (selectQuery.TakeN > 0)
                {
                    parameters.Add("@take", selectQuery.TakeN);
                }

                if (selectQuery.SkipN > 0)
                {
                    parameters.Add("@skip", selectQuery.SkipN);
                }
            }

            if (selectQuery.IsForUpdate)
            {
                this.Dialect.AppendForUpdateOnQueryFinish(sql);
            }

            return(rootNode);
        }
Beispiel #8
0
        private FetchNode GeneratePagingCollectionSql <T>(SelectQuery <T> selectQuery, bool enforceAlias, FetchNode rootNode, StringBuilder sql, int numberCollectionFetches, AutoNamingDynamicParameters parameters, bool isProjectedQuery)
            where T : class, new()
        {
            // we write a subquery for the root type and all Many-to-One coming off it, we apply paging to that
            // we then left join to all of the collection columns
            // we need to apply the order by outside of the join as well
            var whereSql = new StringBuilder();

            this.AddWhereClause(selectQuery.WhereClauses, whereSql, parameters, ref rootNode);

            // add root columns
            var innerColumnSql = new StringBuilder();

            this.AddRootColumns(selectQuery, innerColumnSql, rootNode, isProjectedQuery);
            var innerColLength         = innerColumnSql.Length;
            var innerColLengthMinusOne = innerColLength - 1;
            var outerColumnSqlTemp     = new char[innerColLength];

            for (var i = 0; i < innerColLength; i++)
            {
                if (innerColumnSql[i] == 't' && i < innerColLengthMinusOne && (i == 0 || innerColumnSql[i - 1] == ' ') && (innerColumnSql[i + 1] == '.' || innerColumnSql[i + 1] == '_'))
                {
                    outerColumnSqlTemp[i] = 'i';
                }
                else
                {
                    outerColumnSqlTemp[i] = innerColumnSql[i];
                }
            }

            var outerColumnSql = new StringBuilder(new string(outerColumnSqlTemp)); // outer columns are the same but reference subquery aliased as i

            var innerTableSql = new StringBuilder();
            var outerTableSql = new StringBuilder();

            this.AddTablesForPagedCollection(selectQuery, innerTableSql, outerTableSql, innerColumnSql, outerColumnSql, rootNode, isProjectedQuery);

            // add order by
            var innerOrderSql = new StringBuilder();
            var orderClauses  = new Queue <OrderClause <T> >(selectQuery.OrderClauses); // clone the queue for use in the outer clause

            if (selectQuery.OrderClauses.Any())
            {
                this.AddOrderByClause(selectQuery.OrderClauses, innerOrderSql, rootNode);
            }
            else
            {
                this.AppendDefaultOrderBy <T>(rootNode, innerOrderSql);
            }

            // construct the query
            var innerSql = new StringBuilder("select ");

            innerSql.Append(innerColumnSql)
            .Append(innerTableSql)
            .Append(whereSql)
            .Append(innerOrderSql);
            //// if anything is added after orderSql then the paging will probably need changing

            this.Dialect.ApplySkipTake(innerSql, innerOrderSql, selectQuery.TakeN, selectQuery.SkipN);
            if (selectQuery.TakeN > 0)
            {
                parameters.Add("@take", selectQuery.TakeN);
            }

            if (selectQuery.SkipN > 0)
            {
                parameters.Add("@skip", selectQuery.SkipN);
            }

            if (selectQuery.IsForUpdate)
            {
                this.Dialect.AppendForUpdateOnQueryFinish(innerSql);
            }

            // now construct the outer query
            sql.Append("select ")
            .Append(outerColumnSql)
            .Append(" from (")
            .Append(innerSql)
            .Append(") as i")
            .Append(outerTableSql);
            var outerOrderSql = new StringBuilder();

            if (orderClauses.Any())
            {
                var containsPrimaryKeyClause = this.AddOrderByClause(orderClauses, outerOrderSql, rootNode, (c, n) => "i", (c, n) => c.DbName);
                if (!containsPrimaryKeyClause)
                {
                    this.AppendDefaultOrderBy <T>(rootNode, outerOrderSql, "i", isFirstOrderClause: false);
                }
            }
            else
            {
                this.AppendDefaultOrderBy <T>(rootNode, outerOrderSql, "i");
            }

            sql.Append(outerOrderSql);
            return(rootNode);
        }
Beispiel #9
0
        private FetchNode GenerateNoPagingUnionSql <T>(SelectQuery <T> selectQuery, bool enforceAlias, FetchNode rootNode, StringBuilder sql, int numberCollectionFetches, AutoNamingDynamicParameters parameters, bool isProjectedQuery)
            where T : class, new()
        {
            var numQueries = rootNode.Children.Count(c => c.Value.Column.Relationship == RelationshipType.OneToMany || c.Value.ContainedCollectionfetchesCount > 0);
            var whereSql   = new StringBuilder();

            this.AddWhereClause(selectQuery.WhereClauses, whereSql, parameters, ref rootNode);

            var subQueryColumnSqls = new StringBuilder[numQueries];
            var subQueryTableSqls  = new StringBuilder[numQueries];

            for (var i = 0; i < numQueries; i++)
            {
                subQueryColumnSqls[i] = new StringBuilder();
                subQueryTableSqls[i]  = new StringBuilder();
            }

            var outerQueryColumnSql = new StringBuilder();

            // add root columns
            foreach (var column in GetColumnsWithIncludesAndExcludes(rootNode?.IncludedColumns, rootNode?.ExcludedColumns, this.Configuration.GetMap <T>(), selectQuery.FetchAllProperties, isProjectedQuery)
                     .Where(
                         c => !rootNode.Children.ContainsKey(c.Name) || !rootNode.Children[c.Name]
                         .IsFetched))
            {
                foreach (var subQuery in subQueryColumnSqls)
                {
                    this.AddColumn(subQuery, column, rootNode.Alias, column.DbName + rootNode.Alias);
                    subQuery.Append(", ");
                }

                outerQueryColumnSql.Append("i.");
                this.Dialect.AppendQuotedName(outerQueryColumnSql, column.DbName + rootNode.Alias);
                outerQueryColumnSql.Append(" as ");
                this.Dialect.AppendQuotedName(outerQueryColumnSql, column.DbName);
                outerQueryColumnSql.Append(", ");
            }

            // remove extraneous ,
            outerQueryColumnSql.Remove(outerQueryColumnSql.Length - 2, 2);
            foreach (var subQuery in subQueryColumnSqls)
            {
                subQuery.Remove(subQuery.Length - 2, 2);
            }

            this.AddTablesForNoPagingUnion(selectQuery, outerQueryColumnSql, subQueryColumnSqls, subQueryTableSqls, rootNode, isProjectedQuery);

            // add order by
            var orderSql = new StringBuilder();

            if (selectQuery.OrderClauses.Any())
            {
                var containsPrimaryKeyClause = this.AddOrderByClause(selectQuery.OrderClauses, orderSql, rootNode, (c, n) => "i", (c, n) => c.DbName + n.Alias);
                if (!containsPrimaryKeyClause)
                {
                    this.AppendDefaultOrderBy <T>(
                        rootNode,
                        orderSql,
                        "i",
                        this.Configuration.GetMap <T>()
                        .PrimaryKey.DbName + rootNode.Alias,
                        false);
                }
            }
            else
            {
                this.AppendDefaultOrderBy <T>(
                    rootNode,
                    orderSql,
                    "i",
                    this.Configuration.GetMap <T>()
                    .PrimaryKey.DbName + rootNode.Alias);
            }

            // now create the query
            sql.Append("select ")
            .Append(outerQueryColumnSql)
            .Append(" from (");
            for (var i = 0; i < numQueries; i++)
            {
                sql.Append("select ")
                .Append(subQueryColumnSqls[i])
                .Append(subQueryTableSqls[i]);
                if (whereSql.Length > 0)
                {
                    sql.Append(whereSql);
                }

                if (selectQuery.IsForUpdate)
                {
                    this.Dialect.AppendForUpdateOnQueryFinish(sql);
                }

                if (i < numQueries - 1)
                {
                    sql.Append(" union all ");
                }
            }

            sql.Append(") as i");
            sql.Append(orderSql);
            return(rootNode);
        }
Beispiel #10
0
        private SelectWriterResult InnerGenerateSql <T>(SelectQuery <T> selectQuery, AutoNamingDynamicParameters parameters, bool enforceAlias, FetchNode rootNode, int numberCollectionFetches, StringBuilder sql, bool isProjectedQuery)
            where T : class, new()   // add in any includes or excludes
        {
            if ((selectQuery.Includes != null && selectQuery.Includes.Any()) || (selectQuery.Excludes != null && selectQuery.Excludes.Any()))
            {
                var parser = new IncludeExcludeParser(this.Configuration);
                rootNode = rootNode ?? new FetchNode();
                GetIncludeExcludes <T>(selectQuery.Includes, parser, rootNode, true);
                GetIncludeExcludes <T>(selectQuery.Excludes, parser, rootNode, false);
            }

            if (numberCollectionFetches > 0)
            {
                if (numberCollectionFetches > 1 && (rootNode.Children.Count(c => c.Value.Column.Relationship == RelationshipType.OneToMany || c.Value.ContainedCollectionfetchesCount > 0) > 1))
                {
                    // multiple one to many branches so we'll perform a union query
                    if (selectQuery.TakeN > 0 || selectQuery.SkipN > 0)
                    {
                        // TODO this is temporary, should generate union query similar to next
                        rootNode = this.GeneratePagingCollectionSql(selectQuery, enforceAlias, rootNode, sql, numberCollectionFetches, parameters, isProjectedQuery);
                    }
                    else
                    {
                        rootNode = this.GenerateNoPagingUnionSql(selectQuery, enforceAlias, rootNode, sql, numberCollectionFetches, parameters, isProjectedQuery);
                    }
                }
                else
                {
                    if (selectQuery.TakeN > 0 || selectQuery.SkipN > 0)
                    {
                        // we're sub-selecting so need to use a subquery
                        rootNode = this.GeneratePagingCollectionSql(selectQuery, enforceAlias, rootNode, sql, numberCollectionFetches, parameters, isProjectedQuery);
                    }
                    else
                    {
                        // we're fetching all things
                        rootNode = this.GenerateNoPagingSql(selectQuery, enforceAlias, rootNode, sql, numberCollectionFetches, parameters, isProjectedQuery);
                    }
                }
            }
            else
            {
                // no collection fetches
                // see if we can transform to union query for non-root disjunctions
                var nonRootDisjunctionTransformationSucceeded = false;
                if (selectQuery.TakeN == 0 && selectQuery.SkipN == 0)
                {
                    var outerJoinDisjunctionTransformer = new OuterJoinDisjunctionTransformer(this.Configuration);
                    int substitutedWhereClauseIndex     = -1;
                    Expression <Func <T, bool> > substitutedWhereClause       = null;
                    IEnumerable <Expression <Func <T, bool> > > substitutions = null;
                    foreach (var whereClauseEntry in selectQuery.WhereClauses.AsSmartEnumerable())
                    {
                        var whereClause = whereClauseEntry.Value;
                        var result      = outerJoinDisjunctionTransformer.AttemptGetOuterJoinDisjunctions(whereClause);
                        if (result.ContainsOuterJoinDisjunction)
                        {
                            if (substitutedWhereClause != null)
                            {
                                // we'll bail out here as we're not supporting multiple disjunctions
                                substitutedWhereClause = null;
                                break;
                            }

                            substitutedWhereClauseIndex = whereClauseEntry.Index;
                            substitutedWhereClause      = whereClause;
                            substitutions = result.UnionWhereClauses;
                        }
                    }

                    if (substitutedWhereClause != null)
                    {
                        // we don't want to order the unioned queries, we'll order them subsequently
                        var originalOrderClauses = selectQuery.OrderClauses;
                        selectQuery.OrderClauses = new Queue <OrderClause <T> >();

                        // we need to copy the fetch node and re-use it inside every query
                        var originalRootNode = rootNode != null
                                                   ? rootNode.Clone()
                                                   : new FetchNode(); // we force the unions to have the same alias

                        foreach (var substitution in substitutions.AsSmartEnumerable())
                        {
                            // swap out the original where clause for the substitute
                            selectQuery.WhereClauses.RemoveAt(substitutedWhereClauseIndex);
                            selectQuery.WhereClauses.Insert(substitutedWhereClauseIndex, substitution.Value);
                            var substitutionRootNode = originalRootNode.Clone();
                            rootNode = this.GenerateNoPagingSql(selectQuery, enforceAlias, substitutionRootNode, sql, numberCollectionFetches, parameters, isProjectedQuery);
                            if (!substitution.IsLast)
                            {
                                sql.Append(" union ");
                            }
                        }

                        if (originalOrderClauses != null && originalOrderClauses.Any())
                        {
                            this.AddOrderByClause(originalOrderClauses, sql, rootNode);
                        }

                        nonRootDisjunctionTransformationSucceeded = true;
                    }
                }

                if (!nonRootDisjunctionTransformationSucceeded)
                {
                    rootNode = this.GenerateNoPagingSql(selectQuery, enforceAlias, rootNode, sql, numberCollectionFetches, parameters, isProjectedQuery);
                }
            }

            return(new SelectWriterResult(sql.ToString(), parameters, rootNode)
            {
                NumberCollectionsFetched = numberCollectionFetches
            });
        }
Beispiel #11
0
        private void AddSetClauses <T>(IEnumerable <string> setProps, IMap <T> map, StringBuilder sql, T entity, AutoNamingDynamicParameters parameters, bool includeRootAlias)
        {
            foreach (var updatedPropEntry in setProps.AsSmartEnumerable())
            {
                if (includeRootAlias)
                {
                    sql.Append("t.");
                }

                var column = map.Columns[updatedPropEntry.Value];
                this.Dialect.AppendQuotedName(sql, column.DbName);
                var paramName     = "@" + updatedPropEntry.Value;
                var propertyValue = map.GetColumnValue(entity, column);
                if (propertyValue == null)
                {
                    parameters.Add(paramName, null);
                }
                else
                {
                    parameters.Add(paramName, this.GetValueOrPrimaryKey(column, propertyValue));
                }

                sql.Append(" = ");
                sql.Append(paramName);
                if (!updatedPropEntry.IsLast)
                {
                    sql.Append(", ");
                }
            }
        }
Beispiel #12
0
        private string GetSimpleUpdateQuery <T>(IEnumerable <string> setProps, T entity, AutoNamingDynamicParameters parameters, StringBuilder whereSql)
        {
            var map = this.Configuration.GetMap <T>();
            var sql = new StringBuilder();

            sql.Append("update ");
            this.Dialect.AppendQuotedTableName(sql, map);
            sql.Append(" set ");

            this.AddSetClauses(setProps, map, sql, entity, parameters, false);
            sql.Append(whereSql);
            return(sql.ToString());
        }
Beispiel #13
0
        private string GetMultiTableUpdateQuery <T>(IEnumerable <string> setProps, T entity, AutoNamingDynamicParameters parameters, StringBuilder whereSql, FetchNode rootNode)
        {
            var map = this.Configuration.GetMap <T>();
            var sql = new StringBuilder();

            sql.Append("update t set ");

            this.AddSetClauses(setProps, map, sql, entity, parameters, true);

            sql.Append(" from ");
            this.Dialect.AppendQuotedTableName(sql, map);
            sql.Append(" as t");

            foreach (var node in rootNode.Children)
            {
                this.AddNode(node.Value, sql);
            }

            sql.Append(whereSql);
            return(sql.ToString());
        }