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);
}
}
