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 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 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 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 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 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 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 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 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 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 GetSqlCommandGeneratesSelectClauseWithManyColumnsIfConstuctorWithMemberInitializationPassed() { var builder = new SqlCommandBuilder(new DefaultNameResolver()); var source = new ModelQuerySource(typeof(User), new DefaultNameResolver()); Expression<Func<User, object>> selectClause = user => new EmailInfo { 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 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 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 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 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)")); }
/// <summary> /// Creates a typed query for a given model class. /// </summary> /// <typeparam name="TModel"> /// The model class. /// </typeparam> /// <returns> /// The query instance. /// </returns> public IQuery <TModel> Table <TModel>() { var fromData = new ModelQuerySource(typeof(TModel), nameResolver); return(new SelectQuery <TModel>(queryDataFactory.CreateSelectQueryData(fromData), queryDataFactory)); }
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")); }