public void SelectAllFieldsWithDeclarationAsParameter() { string expectedQuery = @" SELECT [TestTable].[Id], [TestTable].[Title], [TestTable].[FloatVal], [TestTable].[DecimalVal], [TestTable].[CreationDate], [TestTable].[NullableId] FROM [TestTable] "; SelectDeclaration <TestTable> selectDeclaration = new SelectDeclaration <TestTable>(); selectDeclaration.AllFields(); TSqlBuilder builder = new TSqlBuilder(); builder .Select <TestTable>(selectDeclaration); TSqlQuery actualQuery = builder.CompileQuery(); Assert.AreEqual(NormalizeSqlQuery(expectedQuery), NormalizeSqlQuery(actualQuery.Query)); CollectionAssert.IsEmpty(actualQuery.Parameters); }
public void MultipleJoin() { string expectedQuery = $@" SELECT [TestTable].[Id], [TestTable].[Title] FROM [TestTable] INNER JOIN [AnotherTable] ON [AnotherTable].[Name] = [TestTable].[Title] INNER JOIN [ThirdTable] ON [ThirdTable].[Id] = [TestTable].[Id] "; TSqlBuilder builder = new TSqlBuilder(); builder .Select <TestTable>( f => f.Id, f => f.Title ) .Join <AnotherTable>((joined, main) => joined.Name == main.Title) .Join <ThirdTable>((joined, main) => joined.Id == main.Id); TSqlQuery actualQuery = builder.CompileQuery(); Assert.AreEqual(NormalizeSqlQuery(expectedQuery), NormalizeSqlQuery(actualQuery.Query)); }
public void DecimalParameter() { decimal decimalVal = 3.14m; string expectedQuery = $@" SELECT [TestTable].[Id], [TestTable].[Title] FROM [TestTable] WHERE [TestTable].[DecimalVal] = @TestTable_DecimalVal "; Dictionary <string, object> expectedParameters = new Dictionary <string, object> { { "TestTable_DecimalVal", decimalVal } }; TSqlBuilder builder = new TSqlBuilder(); builder .Select <TestTable>( f => f.Id, f => f.Title ).Where(f => f.DecimalVal == decimalVal); TSqlQuery actualQuery = builder.CompileQuery(); Assert.AreEqual(NormalizeSqlQuery(expectedQuery), NormalizeSqlQuery(actualQuery.Query)); CollectionAssert.AreEquivalent(expectedParameters, actualQuery.Parameters); }
public void VariableParameter() { int id = 1; string expectedQuery = @" SELECT [TestTable].[Id], [TestTable].[Title] FROM [TestTable] WHERE [TestTable].[Id] = @TestTable_Id "; Dictionary <string, object> expectedParameters = new Dictionary <string, object> { { "TestTable_Id", id } }; TSqlBuilder builder = new TSqlBuilder(); builder .Select <TestTable>( f => f.Id, f => f.Title ).Where( f => f.Id == id ); TSqlQuery actualQuery = builder.CompileQuery(); Assert.AreEqual(NormalizeSqlQuery(expectedQuery), NormalizeSqlQuery(actualQuery.Query)); CollectionAssert.AreEquivalent(expectedParameters, actualQuery.Parameters); }
public void WhereCondifionalOperation() { string expectedQuery = @" SELECT [TestTable].[Id], [TestTable].[Title] FROM [TestTable] WHERE [TestTable].[CreationDate] >= sysutcdatetime() "; bool condition = true; var comparisionOperator = condition ? ComparisonOperator.GreaterOrEqual : ComparisonOperator.Less; ComparisonClause <TestTable> clause = new ComparisonClause <TestTable>( comparisionOperator, f => f.CreationDate, TSqlStatement.SysUtcDateTimeCall ); TSqlBuilder builder = new TSqlBuilder(); builder .Select <TestTable>( f => f.Id, f => f.Title ).Where(clause); TSqlQuery actualQuery = builder.CompileQuery(); Assert.AreEqual(NormalizeSqlQuery(expectedQuery), NormalizeSqlQuery(actualQuery.Query)); }
public void OrderByMultipleColumns() { string expectedQuery = @" SELECT [TestTable].[Id], [TestTable].[Title] FROM [TestTable] ORDER BY [TestTable].[Id] DESC, [TestTable].[CreationDate] ASC "; TSqlBuilder builder = new TSqlBuilder(); builder .Select <TestTable>( f => f.Id, f => f.Title ) .OrderBy( ord => ord .Desc(f => f.Id) .Asc(f => f.CreationDate) ); TSqlQuery actualQuery = builder.CompileQuery(); Assert.AreEqual(NormalizeSqlQuery(expectedQuery), NormalizeSqlQuery(actualQuery.Query)); CollectionAssert.IsEmpty(actualQuery.Parameters); }
public void OffsetFetch() { string expectedQuery = @" SELECT [TestTable].[Id], [TestTable].[Title] FROM [TestTable] ORDER BY [TestTable].[Id] DESC OFFSET 5 ROWS FETCH NEXT 100 ROWS ONLY "; TSqlBuilder builder = new TSqlBuilder(); builder .Select <TestTable>( f => f.Id, f => f.Title ) .OrderBy( ord => ord .Desc(f => f.Id) ) .OffsetFetch(5, 100); TSqlQuery actualQuery = builder.CompileQuery(); Assert.AreEqual(NormalizeSqlQuery(expectedQuery), NormalizeSqlQuery(actualQuery.Query)); CollectionAssert.IsEmpty(actualQuery.Parameters); }
public void WhereWithDeclarationAsParameter() { string expectedQuery = @" SELECT [TestTable].[Id], [TestTable].[Title] FROM [TestTable] WHERE [TestTable].[Id] = @TestTable_Id "; Dictionary <string, object> expectedParameters = new Dictionary <string, object> { { "TestTable_Id", 1 } }; WhereDeclaration <TestTable> whereDeclaration = new WhereDeclaration <TestTable>(); whereDeclaration.Comparison(f => f.Id == 1); TSqlBuilder builder = new TSqlBuilder(); builder .Select <TestTable>( f => f.Id, f => f.Title ).Where(whereDeclaration); TSqlQuery actualQuery = builder.CompileQuery(); Assert.AreEqual(NormalizeSqlQuery(expectedQuery), NormalizeSqlQuery(actualQuery.Query)); CollectionAssert.AreEquivalent(expectedParameters, actualQuery.Parameters); }
public void WhereWithKeywords() { string expectedQuery = @" SELECT [ThirdTable].[Key], [ThirdTable].[Index] FROM [ThirdTable] WHERE [ThirdTable].[Index] = @ThirdTable_Index "; Dictionary <string, object> expectedParameters = new Dictionary <string, object> { { "ThirdTable_Index", 1 } }; TSqlBuilder builder = new TSqlBuilder(); builder .Select <ThirdTable>( f => f.Key, f => f.Index ).Where( f => f.Index == 1 ); TSqlQuery actualQuery = builder.CompileQuery(); Assert.AreEqual(NormalizeSqlQuery(expectedQuery), NormalizeSqlQuery(actualQuery.Query)); CollectionAssert.AreEquivalent(expectedParameters, actualQuery.Parameters); }
public void CountAggregation() { string expectedQuery = @" SELECT COUNT(*), COUNT(*) AS [Count], COUNT([TestTable].[Id]), COUNT([TestTable].[Id]) AS [CountById], COUNT([AnotherTable].[Id]), COUNT([AnotherTable].[Id]) AS [AnotherTableCountById] FROM [TestTable] INNER JOIN [AnotherTable] ON [AnotherTable].[Id] = [TestTable].[Id] "; TSqlBuilder builder = new TSqlBuilder(); builder .Select <TestTable>( sel => sel .Count() .Count("Count") .Count(f => f.Id) .Count(f => f.Id, "CountById") .Count <AnotherTable>(f => f.Id) .Count <AnotherTable>(f => f.Id, "AnotherTableCountById") ) .Join <AnotherTable>((joined, main) => joined.Id == main.Id); TSqlQuery actualQuery = builder.CompileQuery(); Assert.AreEqual(NormalizeSqlQuery(expectedQuery), NormalizeSqlQuery(actualQuery.Query)); CollectionAssert.IsEmpty(actualQuery.Parameters); }
public void StringParameter() { string title = "sometitle"; string expectedQuery = $@" SELECT [TestTable].[Id], [TestTable].[Title] FROM [TestTable] WHERE [TestTable].[Title] = @TestTable_Title "; Dictionary <string, object> expectedParameters = new Dictionary <string, object> { { "TestTable_Title", title } }; TSqlBuilder builder = new TSqlBuilder(); builder .Select <TestTable>( f => f.Id, f => f.Title ).Where(f => f.Title == title); TSqlQuery actualQuery = builder.CompileQuery(); Assert.AreEqual(NormalizeSqlQuery(expectedQuery), NormalizeSqlQuery(actualQuery.Query)); CollectionAssert.AreEquivalent(expectedParameters, actualQuery.Parameters); }
public void WhereWithTsqlStatement() { string expectedQuery = @" SELECT [TestTable].[Id], [TestTable].[Title] FROM [TestTable] WHERE [TestTable].[Id] = SCOPE_IDENTITY() "; TSqlBuilder builder = new TSqlBuilder(); builder .Select <TestTable>( f => f.Id, f => f.Title ).Where( whr => whr .Comparison(f => f.Id, ComparisonOperator.Equal, TSqlStatement.ScopeIdentityCall) ); TSqlQuery actualQuery = builder.CompileQuery(); Assert.AreEqual(NormalizeSqlQuery(expectedQuery), NormalizeSqlQuery(actualQuery.Query)); CollectionAssert.IsEmpty(actualQuery.Parameters); }
public void WhereIsNullWithParameter() { string expectedQuery = @" SELECT [TestTable].[Id], [TestTable].[Title] FROM [TestTable] WHERE [TestTable].[Title] IS NULL "; string title = null; TSqlBuilder builder = new TSqlBuilder(); builder .Select <TestTable>( f => f.Id, f => f.Title ).Where( f => f.Title == title ); TSqlQuery actualQuery = builder.CompileQuery(); Assert.AreEqual(NormalizeSqlQuery(expectedQuery), NormalizeSqlQuery(actualQuery.Query)); CollectionAssert.IsEmpty(actualQuery.Parameters); }
public void SelectSomeFieldsFromMainTableAndAllFromJoined() { string expectedQuery = $@" SELECT [TestTable].[Id], [TestTable].[Title], [TestTable].[FloatVal], [TestTable].[DecimalVal], [TestTable].[CreationDate], [TestTable].[NullableId], [AnotherTable].[Name], [AnotherTable].[SomeNullableId] FROM [AnotherTable] INNER JOIN [TestTable] ON [TestTable].[Id] = [AnotherTable].[Id] "; TSqlBuilder builder = new TSqlBuilder(); builder .Select <AnotherTable>( sel => sel .AllFields <TestTable>() .Field <AnotherTable>(f => f.Name) .Field <AnotherTable>(f => f.SomeNullableId) ) .Join <TestTable>((joined, main) => joined.Id == main.Id); TSqlQuery actualQuery = builder.CompileQuery(); Assert.AreEqual(NormalizeSqlQuery(expectedQuery), NormalizeSqlQuery(actualQuery.Query)); }
public void WhereIn() { int[] values = new int[] { 1, 2, 3, 4, 5 }; string expectedQuery = $@" SELECT [TestTable].[Id], [TestTable].[Title] FROM [TestTable] WHERE [TestTable].[Id] IN @TestTable_Id "; Dictionary <string, object> expectedParameters = new Dictionary <string, object> { { "TestTable_Id", values } }; TSqlBuilder builder = new TSqlBuilder(); builder .Select <TestTable>( f => f.Id, f => f.Title ).Where( d => d.In(f => f.Id, values) ); TSqlQuery actualQuery = builder.CompileQuery(); Assert.AreEqual(NormalizeSqlQuery(expectedQuery), NormalizeSqlQuery(actualQuery.Query)); CollectionAssert.AreEquivalent(expectedParameters, actualQuery.Parameters); }
public void SelectDistictFromJoined() { string expectedQuery = @" SELECT DISTINCT [TestTable].[Id], [AnotherTable].[Name] FROM [TestTable] INNER JOIN [AnotherTable] ON [AnotherTable].[Name] = [TestTable].[Title] "; TSqlBuilder builder = new TSqlBuilder(); builder .Select <TestTable>( sel => sel .Distinct() .Field(f => f.Id) .Field <AnotherTable>(f => f.Name) ) .Join <AnotherTable>((joined, main) => joined.Name == main.Title); TSqlQuery actualQuery = builder.CompileQuery(); Assert.AreEqual(NormalizeSqlQuery(expectedQuery), NormalizeSqlQuery(actualQuery.Query)); CollectionAssert.IsEmpty(actualQuery.Parameters); }
public void WhereWithDerivedClass() { string expectedQuery = @" SELECT [TestTableDerived].[Title] FROM [TestTableDerived] WHERE [TestTableDerived].[Id] = @TestTableDerived_Id "; Dictionary <string, object> expectedParameters = new Dictionary <string, object> { { "TestTableDerived_Id", 1 } }; TSqlBuilder builder = new TSqlBuilder(); builder .Select <TestTableDerived>( f => f.Title ).Where( f => f.Id == 1 ); TSqlQuery actualQuery = builder.CompileQuery(); Assert.AreEqual(NormalizeSqlQuery(expectedQuery), NormalizeSqlQuery(actualQuery.Query)); CollectionAssert.AreEquivalent(expectedParameters, actualQuery.Parameters); }
public void SelectFromJoinedTableWithAlias() { string expectedQuery = $@" SELECT [TestTable].[Id], [TestTable].[Title], [AnotherTable].[Id] AS [Alias], [AnotherTable].[Name] FROM [TestTable] INNER JOIN [AnotherTable] ON [AnotherTable].[Name] = [TestTable].[Title] "; TSqlBuilder builder = new TSqlBuilder(); builder .Select <TestTable>( sel => sel .Field(t => t.Id) .Field(t => t.Title) .Field <AnotherTable>(t => t.Id, "Alias") .Field <AnotherTable>(t => t.Name) ) .Join <AnotherTable>((joined, main) => joined.Name == main.Title); TSqlQuery actualQuery = builder.CompileQuery(); Assert.AreEqual(NormalizeSqlQuery(expectedQuery), NormalizeSqlQuery(actualQuery.Query)); }
public void JoinWithJoinedWithHintAndJoinType() { string expectedQuery = $@" SELECT [TestTable].[Id], [TestTable].[Title] FROM [TestTable] INNER JOIN [AnotherTable] WITH(NOLOCK) ON [AnotherTable].[Name] = [TestTable].[Title] LEFT JOIN [ThirdTable] WITH(NOLOCK) ON [ThirdTable].[Id] = [AnotherTable].[Id] "; TSqlBuilder builder = new TSqlBuilder(); builder .Select <TestTable>( f => f.Id, f => f.Title ) .Join <AnotherTable>(TableHint.NoLock, (joined, main) => joined.Name == main.Title) .Join <ThirdTable, AnotherTable>(TableHint.NoLock, (joined, main) => joined.Id == main.Id, JoinType.Left); TSqlQuery actualQuery = builder.CompileQuery(); Assert.AreEqual(NormalizeSqlQuery(expectedQuery), NormalizeSqlQuery(actualQuery.Query)); }
public void MaxAggregation() { string expectedQuery = @" SELECT MAX([TestTable].[Id]), MAX([TestTable].[FloatVal]) AS [Max], MAX([AnotherTable].[Id]), MAX([AnotherTable].[Id]) AS [AnotherTableMax] FROM [TestTable] INNER JOIN [AnotherTable] ON [AnotherTable].[Id] = [TestTable].[Id] "; TSqlBuilder builder = new TSqlBuilder(); builder .Select <TestTable>( sel => sel .Max(f => f.Id) .Max(f => f.FloatVal, "Max") .Max <AnotherTable>(f => f.Id) .Max <AnotherTable>(f => f.Id, "AnotherTableMax") ) .Join <AnotherTable>((joined, main) => joined.Id == main.Id); TSqlQuery actualQuery = builder.CompileQuery(); Assert.AreEqual(NormalizeSqlQuery(expectedQuery), NormalizeSqlQuery(actualQuery.Query)); CollectionAssert.IsEmpty(actualQuery.Parameters); }
public void DateParameter() { DateTime date = new DateTime(2016, 1, 1); string expectedQuery = $@" SELECT [TestTable].[Id], [TestTable].[Title] FROM [TestTable] WHERE [TestTable].[CreationDate] = @TestTable_CreationDate "; Dictionary <string, object> expectedParameters = new Dictionary <string, object> { { "TestTable_CreationDate", date } }; TSqlBuilder builder = new TSqlBuilder(); builder .Select <TestTable>( f => f.Id, f => f.Title ).Where(f => f.CreationDate == date); TSqlQuery actualQuery = builder.CompileQuery(); Assert.AreEqual(NormalizeSqlQuery(expectedQuery), NormalizeSqlQuery(actualQuery.Query)); CollectionAssert.AreEquivalent(expectedParameters, actualQuery.Parameters); }
public void And_NewNotation() { int id = 10; string title = "sometitle"; float floatVal = 3.14F; decimal decimalVal = 2.71m; DateTime date = new DateTime(2016, 1, 1); string expectedQuery = $@" SELECT [TestTable].[Id], [TestTable].[Title] FROM [TestTable] WHERE ( ( ( ( [TestTable].[CreationDate] = @TestTable_CreationDate AND [TestTable].[Id] = @TestTable_Id ) AND [TestTable].[Title] = @TestTable_Title ) AND [TestTable].[FloatVal] = @TestTable_FloatVal ) AND [TestTable].[DecimalVal] = @TestTable_DecimalVal ) "; Dictionary <string, object> expectedParameters = new Dictionary <string, object> { { "TestTable_Id", id }, { "TestTable_Title", title }, { "TestTable_FloatVal", floatVal }, { "TestTable_DecimalVal", decimalVal }, { "TestTable_CreationDate", date } }; TSqlBuilder builder = new TSqlBuilder(); builder .Select <TestTable>( f => f.Id, f => f.Title ).Where( f => f.CreationDate == date && f.Id == id && f.Title == title && f.FloatVal == floatVal && f.DecimalVal == decimalVal ); TSqlQuery actualQuery = builder.CompileQuery(); Assert.AreEqual(NormalizeSqlQuery(expectedQuery), NormalizeSqlQuery(actualQuery.Query)); CollectionAssert.AreEquivalent(expectedParameters, actualQuery.Parameters); }
public void JoinWithOnNullablePropertyInBoth() { TSqlBuilder builder = new TSqlBuilder(); builder .Select <TestTable>( f => f.Id ) .Join <AnotherTable>((joined, main) => joined.SomeNullableId == main.NullableId); TSqlQuery actualQuery = builder.CompileQuery(); }
public void SelectTopLessThenOne() { TSqlBuilder builder = new TSqlBuilder(); Assert.Throws <ArgumentException>(() => { builder .Select <TestTable>( sel => sel .Top(-1) .Field(f => f.Id) .Field(f => f.Title) ); }); }
public void FetchLessThanOne() { TSqlBuilder builder = new TSqlBuilder(); Assert.Throws <ArgumentException>(() => { builder .Select <TestTable>() .OrderBy( ord => ord .Desc(f => f.Id) ) .OffsetFetch(0, 0); }); }
public void InvalidOperatorWithNull() { TSqlBuilder builder = new TSqlBuilder(); builder .Select <TestTable>( f => f.Id, f => f.Title ).Where( f => f.FloatVal > null ); Assert.Throws <InvalidOperationException>(() => { builder.CompileQuery(); }); }
public void AnotherDerivedTableInWhere() { int id = 10; string title = "someName"; string expectedQuery = $@" SELECT [TestTable].[Id], [TestTable].[Title] FROM [TestTable] INNER JOIN [TestTableDerived] ON [TestTableDerived].[Id] = [TestTable].[Id] WHERE ( [TestTable].[Id] = @TestTable_Id AND [TestTableDerived].[Title] = @TestTableDerived_Title ) "; Dictionary <string, object> expectedParameters = new Dictionary <string, object> { { "TestTable_Id", id }, { "TestTableDerived_Title", title }, }; TSqlBuilder builder = new TSqlBuilder(); builder .Select <TestTable>( f => f.Id, f => f.Title ) .Join <TestTableDerived>((joined, main) => joined.Id == main.Id) .Where( b => b.And( f => f.Comparison <TestTable>(n => n.Id == id), f => f.Comparison <TestTableDerived>(n => n.Title == title) ) ); TSqlQuery actualQuery = builder.CompileQuery(); Assert.AreEqual(NormalizeSqlQuery(expectedQuery), NormalizeSqlQuery(actualQuery.Query)); CollectionAssert.AreEquivalent(expectedParameters, actualQuery.Parameters); }
public void JoinWithDerivedClass() { string expectedQuery = $@" SELECT [TestTable].[Id] FROM [TestTable] INNER JOIN [TestTableDerived] ON [TestTableDerived].[Id] = [TestTable].[Id] "; TSqlBuilder builder = new TSqlBuilder(); builder .Select <TestTable>( f => f.Id ) .Join <TestTableDerived>((joined, main) => joined.Id == main.Id); TSqlQuery actualQuery = builder.CompileQuery(); Assert.AreEqual(NormalizeSqlQuery(expectedQuery), NormalizeSqlQuery(actualQuery.Query)); }
public void SelectAggregationByFieldWithAlias() { string expectedQuery = @" SELECT AVG([TestTable].[DecimalVal]) AS [CustomName] FROM [TestTable] "; TSqlBuilder builder = new TSqlBuilder(); builder .Select <TestTable>( sel => sel .Avg(f => f.DecimalVal, "CustomName") ); TSqlQuery actualQuery = builder.CompileQuery(); Assert.AreEqual(NormalizeSqlQuery(expectedQuery), NormalizeSqlQuery(actualQuery.Query)); CollectionAssert.IsEmpty(actualQuery.Parameters); }
public void SelectAggregation() { string expectedQuery = @" SELECT COUNT(*) FROM [TestTable] "; TSqlBuilder builder = new TSqlBuilder(); builder .Select <TestTable>( sel => sel .Count() ); TSqlQuery actualQuery = builder.CompileQuery(); Assert.AreEqual(NormalizeSqlQuery(expectedQuery), NormalizeSqlQuery(actualQuery.Query)); CollectionAssert.IsEmpty(actualQuery.Parameters); }