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)); } }
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"))) : ""); }
/// <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; }
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) + '%')")); }
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))")); }
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)); } }
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 ? ".*" : "")); }
/// <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]")); }
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 + "]" : ""); }
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 ? ".*" : ""); }
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); } }