public void GetSqlCommandGeneratesSimplestSelectIfNoAdditionalArgumentsPassed()
{
var builder = new SqlCommandBuilder(new DefaultNameResolver());
var source = new ModelQuerySource(typeof(User), new DefaultNameResolver());
var command = builder.GetSelectCommand(new SelectQueryData(builder, source));
Assert.That(command.Command, Is.EqualTo("SELECT * FROM [User]"));
}
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)"));
}
public void GetSqlCommandWhenSelectClauseIsPresentTableAliasIsTakenFromIt()
{
var builder = new SqlCommandBuilder(new DefaultNameResolver());
var source = new ModelQuerySource(typeof(User), new DefaultNameResolver());
Expression <Func <User, object> > selectClause = user => user.Email;
var command = builder.GetSelectCommand(new SelectQueryData(builder, source)
{
SelectClause = selectClause
});
Assert.That(command.Command, Is.EqualTo("SELECT [user].[Email] FROM [User] [user]"));
}
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 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"));
}
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)"));
}
public void GetSqlCommandGeneratesSelectClauseWithOneColumnIfSingleExpressionSpecified()
{
var builder = new SqlCommandBuilder(new DefaultNameResolver());
var source = new ModelQuerySource(typeof(User), new DefaultNameResolver());
Expression <Func <User, object> > selectClause = user => user.Login;
var command = builder.GetSelectCommand(new SelectQueryData(builder, source)
{
SelectClause = selectClause
});
Assert.That(command.Command, Is.EqualTo("SELECT [user].[Login] FROM [User] [user]"));
}
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)"));
}
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 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 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) + '%')"));
}
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 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]"));
}
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))"));
}
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) + '%')"));
}
public void GetSqlCommandGeneratesSelectClauseWithManyColumnsIfConstuctorOfDynamicTypeWithMemberInitializationPassed()
{
var builder = new SqlCommandBuilder(new DefaultNameResolver());
var source = new ModelQuerySource(typeof(User), new DefaultNameResolver());
Expression <Func <User, object> > selectClause = user => new
{
UserName = user.FirstName + " " + user.LastName,
Email = user.Email
};
var command = builder.GetSelectCommand(new SelectQueryData(builder, source)
{
SelectClause = selectClause
});
Assert.That(
command.Command,
Is.EqualTo("SELECT (([user].[FirstName]) + (@p0)) + ([user].[LastName]) AS [UserName], [user].[Email] AS [Email] FROM [User] [user]"));
}
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 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"));
}
