Example #1
0
        private string DetermineMainTableAlias(SelectQueryData data)
        {
            var join = data.Joins.FirstOrDefault();

            if (join != null)
            {
                var alias  = subQueryPrefix + join.ResultSelector.Parameters[0].Name;
                var member = FindMemberSetByParameter(join, 0);
                if (member != null)
                {
                    aliases[member] = alias;
                    lastTableAlias  = alias;
                }
                return(alias);
            }
            else
            {
                var allParams = new[] { data.SelectClause, data.GroupByKey, data.GroupByElement }
                .Where(c => c != null)
                .Union(data.WhereClauses)
                .Union(data.OrderByProperties.Select(o => o.Item1))
                .SelectMany(c => c.Parameters)
                .GroupBy(p => p.Type)
                .ToDictionary(g => g.Key, g => subQueryPrefix + g.First().Name);
                return(DetermineAlias(data.ModelType, allParams, data.Joins.Count() + 1));
            }
        }
Example #2
0
 protected virtual string GetOrderByClauseText(SelectQueryData data)
 {
     return(data.OrderByProperties.Any()
         ? " ORDER BY " + string.Join(", ", data.OrderByProperties
                                      .Select(prop => ExpressionToSql(prop.Item1.Body, false) + (prop.Item2 ? "" : " DESC")))
         : "");
 }
Example #3
0
 /// <summary>
 /// Initializes a new instance of the <see cref="c:SelectQuery"/> class.
 /// </summary>
 /// <param name="data">
 /// The query specification data object.
 /// </param>
 /// <param name="dataFactory">
 /// The factory for specification data creation.
 /// </param>
 public SelectQuery(SelectQueryData data, IQueryDataFactory dataFactory)
 {
     Check.NotNull(data, "data");
     Check.NotNull(dataFactory, "dataFactory");
     this.data        = data;
     this.dataFactory = dataFactory;
 }
Example #4
0
        private string GetOffsetFetchClauseText(SelectQueryData data)
        {
            var offsetFetchClause = (data.SkipRows != 0 || data.TakeRows != 0) ? string.Format(" OFFSET {0} ROWS", data.SkipRows) : "";

            if (data.TakeRows != 0)
            {
                offsetFetchClause += string.Format(" FETCH NEXT {0} ROWS ONLY", data.TakeRows);
            }
            return(offsetFetchClause);
        }
 public void GetSqlCommandDateTimeConstructorIsTranslatedIntoDateTimeFromParts()
 {
     var builder = new SqlCommandBuilder(new DefaultNameResolver());
     var source = new ModelQuerySource(typeof(User), new DefaultNameResolver());
     Expression<Func<User, bool>> whereClause = user => user.BirthDate == new DateTime(1968, 06, 02);
     var data = new SelectQueryData(builder, source);
     data.WhereClauses.Add(whereClause);
     var command = builder.GetSelectCommand(data);
     Assert.That(command.Command, Is.EqualTo("SELECT * FROM [User] [user] WHERE ([user].[BirthDate]) = (DATETIMEFROMPARTS(@p0, @p1, @p2, 0, 0, 0, 0))"));
 }
 public void GetSqlCommandContainsMethodIsTranslatedToLikeOperator()
 {
     var builder = new SqlCommandBuilder(new DefaultNameResolver());
     var source = new ModelQuerySource(typeof(User), new DefaultNameResolver());
     Expression<Predicate<User>> whereClause = user => user.Login.Contains("cent");
     var data = new SelectQueryData(builder, source);
     data.WhereClauses.Add(whereClause);
     var command = builder.GetSelectCommand(data);
     Assert.That(command.Command, Is.EqualTo("SELECT * FROM [User] [user] WHERE ([user].[Login]) LIKE ('%' + (@p0) + '%')"));
 }
Example #7
0
        private string GetAliasedTableName(SelectQueryData data)
        {
            string alias       = DetermineMainTableAlias(data);
            var    subQuerySql = data.FromData.GetSqlCommandOrTableName("sq" + (lastSubQueryId++) + "_");

            foreach (var param in subQuerySql.Parameters)
            {
                parameters[param.Key] = param.Value;
            }
            return(subQuerySql.Command + (alias != null ? " [" + alias + "]" : ""));
        }
 public void GetSqlCommandCreatesParameterWhenConstantIsUsedInExpression()
 {
     var builder = new SqlCommandBuilder(new DefaultNameResolver());
     var source = new ModelQuerySource(typeof(User), new DefaultNameResolver());
     Expression<Predicate<User>> whereClause = user => user.Login == "jacenty";
     var data = new SelectQueryData(builder, source);
     data.WhereClauses.Add(whereClause);
     var command = builder.GetSelectCommand(data);
     Assert.That(command.Command, Is.EqualTo("SELECT * FROM [User] [user] WHERE ([user].[Login]) = (@p0)"));
     Assert.That(command.Parameters["@p0"], Is.EqualTo("jacenty"));
 }
 public void GetSqlCommandAndOperatorIsTranslatedCorrectly()
 {
     var builder = new SqlCommandBuilder(new DefaultNameResolver());
     var source = new ModelQuerySource(typeof(User), new DefaultNameResolver());
     Expression<Predicate<User>> whereClause = user => user.FirstName == "Jacek" && user.LastName == "Hełka";
     var data = new SelectQueryData(builder, source);
     data.WhereClauses.Add(whereClause);
     var command = builder.GetSelectCommand(data);
     Assert.That(
         command.Command,
         Is.EqualTo("SELECT * FROM [User] [user] WHERE (([user].[FirstName]) = (@p0)) AND (([user].[LastName]) = (@p1))"));
 }
Example #10
0
        private object GetGroupClauseText(SelectQueryData data)
        {
            if (data.GroupByKey == null)
            {
                return("");
            }
            var newExpr = data.GroupByKey.Body as NewExpression;

            if (newExpr != null)
            {
                return(string.Format(" GROUP BY {0}", string.Join(", ", newExpr.Arguments.Select(expr => ExpressionToSql(expr, false)))));
            }
            else
            {
                groupKeySql = ExpressionToSql(data.GroupByKey.Body, false);
                return(string.Format(" GROUP BY {0}", groupKeySql));
            }
        }
Example #11
0
        private string GetSelectClauseText(SelectQueryData data)
        {
            var selectClause = data.SelectClause ?? data.Joins.Select(j => j.ResultSelector).LastOrDefault();

            if (selectClause == null)
            {
                return("*");
            }
            var memberInitExpr = selectClause.Body as MemberInitExpression;

            if (memberInitExpr != null)
            {
                return(string.Join(", ", memberInitExpr.Bindings.Select(MemberBindingToSql)));
            }
            var newExpr = selectClause.Body as NewExpression;

            if (newExpr != null)
            {
                return(ConstructorCallToSql(newExpr.Members, newExpr.Arguments));
            }
            return(ExpressionToSql(selectClause.Body, false) + (selectClause.Body is ParameterExpression ? ".*" : ""));
        }
Example #12
0
        /// <summary>
        /// Generates an SQL command.
        /// </summary>
        /// <param name="data">
        /// The data collected by a query instance, needed for the command generation.
        /// </param>
        /// <param name="subQueryPrefix">
        /// The prefix used for parameter and alias name construction in subqueries.
        /// </param>
        /// <returns>
        /// The SQL command, generated from QueryData instance.
        /// </returns>
        public virtual ParameterizedSql GetSelectCommand(SelectQueryData data, string subQueryPrefix = "")
        {
            Check.NotNull(data, "data");
            Check.NotNull(data.ModelType, "data.ModelType");
            this.subQueryPrefix = subQueryPrefix;
            lastSubQueryId      = 0;
            lastTableAlias      = null;
            //parameters = new Dictionary<string, object>();
            parameters = new System.Dynamic.ExpandoObject();
            aliases    = new Dictionary <MemberInfo, string>();
            var    aliasedTableName  = GetAliasedTableName(data);
            var    joinClauses       = GetAllJoinsText(data);
            var    whereClause       = GetWhereClauseText(data);
            var    groupClause       = GetGroupClauseText(data);
            var    havingClause      = GetHavingClauseText(data);
            var    orderByClause     = GetOrderByClauseText(data);
            var    distinctClause    = data.Distinct ? "DISTINCT " : "";
            var    selectClauseExpr  = data.SelectClause ?? data.Joins.Select(j => j.ResultSelector).LastOrDefault();
            var    selectClause      = GetSelectClauseText(data);
            var    offsetFetchClause = GetOffsetFetchClauseText(data);
            string command           = string.Format("SELECT {0}{1} FROM {2}{3}{4}{5}{6}{7}{8}",
                                                     distinctClause,
                                                     selectClause,
                                                     aliasedTableName,
                                                     joinClauses,
                                                     whereClause,
                                                     groupClause,
                                                     havingClause,
                                                     orderByClause,
                                                     offsetFetchClause);

            return(new ParameterizedSql
            {
                Command = command,
                Parameters = parameters
            });
        }
 public void GetSqlCommandGeneratesSelectWithWhereClauseIfWhereClauseSpecified()
 {
     var builder = new SqlCommandBuilder(new DefaultNameResolver());
     var source = new ModelQuerySource(typeof(User), new DefaultNameResolver());
     Expression<Predicate<User>> whereClause = user => user.Login == user.FirstName;
     var data = new SelectQueryData(builder, source);
     data.WhereClauses.Add(whereClause);
     var command = builder.GetSelectCommand(data);
     Assert.That(command.Command, Is.EqualTo("SELECT * FROM [User] [user] WHERE ([user].[Login]) = ([user].[FirstName])"));
 }
 public void GetSqlCommandGeneratesOrderByIfOrderByWhenManyOrderByPropertiesAdded()
 {
     var builder = new SqlCommandBuilder(new DefaultNameResolver());
     var source = new ModelQuerySource(typeof(User), new DefaultNameResolver());
     var data = new SelectQueryData(builder, source);
     Expression<Func<User, object>> orderSpec1 = user => user.LastName;
     Expression<Func<User, object>> orderSpec2 = user => user.FirstName;
     data.OrderByProperties.Add(Tuple.Create<LambdaExpression, bool>(orderSpec1, true));
     data.OrderByProperties.Add(Tuple.Create<LambdaExpression, bool>(orderSpec2, true));
     var command = builder.GetSelectCommand(data);
     Assert.That(command.Command, Is.EqualTo("SELECT * FROM [User] [user] ORDER BY [user].[LastName], [user].[FirstName]"));
 }
Example #15
0
 protected virtual string GetWhereClauseText(SelectQueryData data)
 {
     return(data.WhereClauses.Count > 0
         ? " WHERE " + string.Join(" AND ", data.WhereClauses.Select(c => ExpressionToSql(c.Body, true)))
         : "");
 }
 public void GetSqlCommandGeneratesOffsetAndFetchWhenSkipAndTakeUsed()
 {
     var builder = new SqlCommandBuilder(new DefaultNameResolver());
     var source = new ModelQuerySource(typeof(User), new DefaultNameResolver());
     var data = new SelectQueryData(builder, source)
     {
         SkipRows = 10,
         TakeRows = 20
     };
     Expression<Func<User, object>> orderSpec1 = user => user.LastName;
     Expression<Func<User, object>> orderSpec2 = user => user.FirstName;
     data.OrderByProperties.Add(Tuple.Create<LambdaExpression, bool>(orderSpec1, true));
     data.OrderByProperties.Add(Tuple.Create<LambdaExpression, bool>(orderSpec2, true));
     var command = builder.GetSelectCommand(data);
     Assert.That(
         command.Command,
         Is.EqualTo("SELECT * FROM [User] [user] ORDER BY [user].[LastName], [user].[FirstName] OFFSET 10 ROWS FETCH NEXT 20 ROWS ONLY"));
 }
 public void GetSqlCommandGeneratesOrderByDescendingIfOrderByPropertyWithDescendingAdded()
 {
     var builder = new SqlCommandBuilder(new DefaultNameResolver());
     var source = new ModelQuerySource(typeof(User), new DefaultNameResolver());
     var data = new SelectQueryData(builder, source);
     Expression<Func<User, object>> orderSpec = user => user.Login;
     data.OrderByProperties.Add(Tuple.Create<LambdaExpression, bool>(orderSpec, false));
     var command = builder.GetSelectCommand(data);
     Assert.That(command.Command, Is.EqualTo("SELECT * FROM [User] [user] ORDER BY [user].[Login] DESC"));
 }
 private string GetOffsetFetchClauseText(SelectQueryData data)
 {
     var offsetFetchClause = (data.SkipRows != 0 || data.TakeRows != 0) ? string.Format(" OFFSET {0} ROWS", data.SkipRows) : "";
     if (data.TakeRows != 0)
         offsetFetchClause += string.Format(" FETCH NEXT {0} ROWS ONLY", data.TakeRows);
     return offsetFetchClause;
 }
 protected virtual string GetOrderByClauseText(SelectQueryData data)
 {
     return data.OrderByProperties.Any()
         ? " ORDER BY " + string.Join(", ", data.OrderByProperties
             .Select(prop => ExpressionToSql(prop.Item1.Body, false) + (prop.Item2 ? "" : " DESC"))) 
         : "";
 }
 public void GetSqlCommandTranslatesSubtractDaysToDateDiffDays()
 {
     var builder = new SqlCommandBuilder(new DefaultNameResolver());
     var source = new ModelQuerySource(typeof(User), new DefaultNameResolver());
     Expression<Func<User, bool>> whereClause = user => DateTime.Today.Subtract(user.BirthDate).Days > 100;
     var data = new SelectQueryData(builder, source);
     data.WhereClauses.Add(whereClause);
     var command = builder.GetSelectCommand(data);
     Assert.That(command.Command, Is.EqualTo("SELECT * FROM [User] [user] WHERE (DATEDIFF(day, [user].[BirthDate], GETDATE())) > (@p0)"));
 }
 /// <summary>
 /// Generates an SQL command.
 /// </summary>
 /// <param name="data">
 /// The data collected by a query instance, needed for the command generation.
 /// </param>
 /// <param name="subQueryPrefix">
 /// The prefix used for parameter and alias name construction in subqueries.
 /// </param>
 /// <returns>
 /// The SQL command, generated from QueryData instance.
 /// </returns>
 public virtual ParameterizedSql GetSelectCommand(SelectQueryData data, string subQueryPrefix = "")
 {
     Check.NotNull(data, "data");
     Check.NotNull(data.ModelType, "data.ModelType");
     this.subQueryPrefix = subQueryPrefix;
     lastSubQueryId = 0;
     lastTableAlias = null;
     //parameters = new Dictionary<string, object>();
     parameters = new System.Dynamic.ExpandoObject();
     aliases = new Dictionary<MemberInfo, string>();
     var aliasedTableName = GetAliasedTableName(data);
     var joinClauses = GetAllJoinsText(data);
     var whereClause = GetWhereClauseText(data);
     var groupClause = GetGroupClauseText(data);
     var havingClause = GetHavingClauseText(data);
     var orderByClause = GetOrderByClauseText(data);
     var distinctClause = data.Distinct ? "DISTINCT " : "";
     var selectClauseExpr = data.SelectClause ?? data.Joins.Select(j => j.ResultSelector).LastOrDefault();
     var selectClause = GetSelectClauseText(data);
     var offsetFetchClause = GetOffsetFetchClauseText(data);
     string command = string.Format("SELECT {0}{1} FROM {2}{3}{4}{5}{6}{7}{8}",
         distinctClause, 
         selectClause, 
         aliasedTableName, 
         joinClauses, 
         whereClause, 
         groupClause, 
         havingClause, 
         orderByClause, 
         offsetFetchClause);
     return new ParameterizedSql
     {
         Command = command,
         Parameters = parameters
     };
 }
 public void GetSqlCommandStringLengthIsTranslatedToLen()
 {
     var builder = new SqlCommandBuilder(new DefaultNameResolver());
     var source = new ModelQuerySource(typeof(User), new DefaultNameResolver());
     Expression<Predicate<User>> whereClause = user => user.Login.Length > 3;
     var data = new SelectQueryData(builder, source);
     data.WhereClauses.Add(whereClause);
     var command = builder.GetSelectCommand(data);
     Assert.That(command.Command, Is.EqualTo("SELECT * FROM [User] [user] WHERE (LEN([user].[Login])) > (@p0)"));
 }
 public void GetSqlCommandTranslatesConditionalOperatorCorrectly()
 {
     var builder = new SqlCommandBuilder(new DefaultNameResolver());
     var source = new ModelQuerySource(typeof(User), new DefaultNameResolver());
     Expression<Predicate<User>> whereClause = user => (user.Login != null ? user.Login : user.FirstName).StartsWith("jac");
     var data = new SelectQueryData(builder, source);
     data.WhereClauses.Add(whereClause);
     var command = builder.GetSelectCommand(data);
     Assert.That(
         command.Command,
         Is.EqualTo("SELECT * FROM [User] [user] WHERE (IIF(([user].[Login]) IS NOT (NULL), [user].[Login], [user].[FirstName])) LIKE ((@p0) + '%')"));
 }
 private string GetJoinClauseText(SelectQueryData.JoinSpec join)
 {            
     var alias = subQueryPrefix + join.ResultSelector.Parameters[1].Name;
     var member = FindMemberSetByParameter(join, 1);
     if (member != null)
         aliases[member] = alias;
     string innerData = "[" + nameResolver.ResolveTableName(join.InnerData.ModelType) + "]";
     var clause = string.Format(" {0} JOIN {1} [{2}] ON {3} = {4}", 
         join.JoinType,
         GetJoinSourceSql(join.InnerData),
         alias,
         GetOuterKeyColumn(join.OuterKeySelector.Body),
         GetInnerKeyColumn(join.InnerKeySelector.Body, alias));
     if (lastTableAlias != null)
         lastTableAlias = alias;
     return clause;
 }
 private string GetAllJoinsText(SelectQueryData data)
 {
     return string.Join("", data.Joins.Select(GetJoinClauseText));
 }
 private string GetAliasedTableName(SelectQueryData data)
 {
     string alias = DetermineMainTableAlias(data);
     var subQuerySql = data.FromData.GetSqlCommandOrTableName("sq" + (lastSubQueryId++) + "_");
     foreach (var param in subQuerySql.Parameters)
         parameters[param.Key] = param.Value;
     return subQuerySql.Command + (alias != null ? " [" + alias + "]" : "");
 }
Example #27
0
 private object GetHavingClauseText(SelectQueryData data)
 {
     return(data.HavingClauses.Count > 0
         ? " HAVING " + string.Join(" AND ", data.HavingClauses.Select(c => ExpressionToSql(c.Body, true)))
         : "");
 }
 protected virtual string GetWhereClauseText(SelectQueryData data)
 {
     return data.WhereClauses.Count > 0
         ? " WHERE " + string.Join(" AND ", data.WhereClauses.Select(c => ExpressionToSql(c.Body, true)))
         : "";
 }
 private MemberInfo FindMemberSetByParameter(SelectQueryData.JoinSpec join, int paramIndex)
 {
     var newExpr = join.ResultSelector.Body as NewExpression;
     if (newExpr != null)
     {
         var index = newExpr.Arguments.TakeWhile(a => a != join.ResultSelector.Parameters[paramIndex]).Count();
         if (index < newExpr.Members.Count)
             return newExpr.Members[index];
     }
     return null;
 }
 private object GetGroupClauseText(SelectQueryData data)
 {
     if (data.GroupByKey == null)
         return "";
     var newExpr = data.GroupByKey.Body as NewExpression;
     if (newExpr != null)
         return string.Format(" GROUP BY {0}", string.Join(", ", newExpr.Arguments.Select(expr => ExpressionToSql(expr, false))));
     else
     {
         groupKeySql = ExpressionToSql(data.GroupByKey.Body, false);
         return string.Format(" GROUP BY {0}", groupKeySql);
     }
 }
 public void GetSqlCommandSubstringMethodIsTranslatedToSubstring()
 {
     var builder = new SqlCommandBuilder(new DefaultNameResolver());
     var source = new ModelQuerySource(typeof(User), new DefaultNameResolver());
     Expression<Predicate<User>> whereClause = user => user.Login.Substring(1, 3) == "ace";
     var data = new SelectQueryData(builder, source);
     data.WhereClauses.Add(whereClause);
     var command = builder.GetSelectCommand(data);
     Assert.That(command.Command, Is.EqualTo("SELECT * FROM [User] [user] WHERE (SUBSTRING([user].[Login], @p0 + 1, @p1)) = (@p2)"));
 }
 public void GetSqlCommandUsesNameResolverForTableAndColumnNames2()
 {
     var builder = new SqlCommandBuilder(new PrefixBasedNameResolver());
     var source = new ModelQuerySource(typeof(User), new PrefixBasedNameResolver());
     var data = new SelectQueryData(builder, source);
     Expression<Func<User, object>> orderSpec = user => user.Login;
     Expression<Func<User, object>> selectSpec = user => new { user.FirstName, user.LastName };
     data.OrderByProperties.Add(Tuple.Create<LambdaExpression, bool>(orderSpec, true));
     data.SelectClause = selectSpec;
     var command = builder.GetSelectCommand(data);
     Assert.That(
         command.Command,
         Is.EqualTo(
             "SELECT [user].[col_FirstName] AS [FirstName], [user].[col_LastName] AS [LastName] " +
             "FROM [TBL_User] [user] " +
             "ORDER BY [user].[col_Login]"));
 }
 public void GetSqlCommandTranslatesNegateOperatorCorrectly()
 {
     var builder = new SqlCommandBuilder(new DefaultNameResolver());
     var source = new ModelQuerySource(typeof(User), new DefaultNameResolver());
     Expression<Predicate<User>> whereClause = user => -user.Id < 10;
     var data = new SelectQueryData(builder, source);
     data.WhereClauses.Add(whereClause);
     var command = builder.GetSelectCommand(data);
     Assert.That(command.Command, Is.EqualTo("SELECT * FROM [User] [user] WHERE (- ([user].[Id])) < (@p0)"));
 }
 private object GetHavingClauseText(SelectQueryData data)
 {
     return data.HavingClauses.Count > 0
         ? " HAVING " + string.Join(" AND ", data.HavingClauses.Select(c => ExpressionToSql(c.Body, true)))
         : "";
 }
 private string GetSelectClauseText(SelectQueryData data)
 {
     var selectClause = data.SelectClause ?? data.Joins.Select(j => j.ResultSelector).LastOrDefault();
     if (selectClause == null)
         return "*";
     var memberInitExpr = selectClause.Body as MemberInitExpression;
     if (memberInitExpr != null)
         return string.Join(", ", memberInitExpr.Bindings.Select(MemberBindingToSql));
     var newExpr = selectClause.Body as NewExpression;
     if (newExpr != null)
         return ConstructorCallToSql(newExpr.Members, newExpr.Arguments);
     return ExpressionToSql(selectClause.Body, false) + (selectClause.Body is ParameterExpression ? ".*" : "");
 }
Example #36
0
 private string GetAllJoinsText(SelectQueryData data)
 {
     return(string.Join("", data.Joins.Select(GetJoinClauseText)));
 }
 public void GetSqlCommandWhenWhereClauseIsPresentTableAliasIsTakenFromIt()
 {
     var builder = new SqlCommandBuilder(new DefaultNameResolver());
     var source = new ModelQuerySource(typeof(User), new DefaultNameResolver());
     Expression<Func<User, bool>> whereClause = user => user.Login == "jacenty";
     var data = new SelectQueryData(builder, source);
     data.WhereClauses.Add(whereClause);
     var command = builder.GetSelectCommand(data);
     Assert.That(command.Command, Is.EqualTo("SELECT * FROM [User] [user] WHERE ([user].[Login]) = (@p0)"));
 }
 private string DetermineMainTableAlias(SelectQueryData data)
 {
     var join = data.Joins.FirstOrDefault();
     if (join != null)
     {
         var alias = subQueryPrefix + join.ResultSelector.Parameters[0].Name;
         var member = FindMemberSetByParameter(join, 0);
         if (member != null)
         {
             aliases[member] = alias;
             lastTableAlias = alias;
         }
         return alias;
     }
     else
     {
         var allParams = new[] { data.SelectClause, data.GroupByKey, data.GroupByElement }
             .Where(c => c != null)
             .Union(data.WhereClauses)
             .Union(data.OrderByProperties.Select(o => o.Item1))
             .SelectMany(c => c.Parameters)
             .GroupBy(p => p.Type)
             .ToDictionary(g => g.Key, g => subQueryPrefix + g.First().Name);
         return DetermineAlias(data.ModelType, allParams, data.Joins.Count() + 1);
     }
 }