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