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 UpdateIfTrueCondition()
{
string expectedQuery = @"
UPDATE [TestTable]
SET
[TestTable].[Id] = @TestTable_Id,
[TestTable].[Title] = @TestTable_Title
";
Dictionary <string, object> expectedParameters = new Dictionary <string, object> {
{ "TestTable_Id", 1 },
{ "TestTable_Title", "testTitle" },
};
TSqlBuilder builder = new TSqlBuilder();
builder.Update <TestTable>(
upd => upd
.Set(f => f.Id, 1, z => z != null)
.Set(f => f.Title, "testTitle")
);
TSqlQuery actualQuery = builder.CompileQuery();
Assert.AreEqual(NormalizeSqlQuery(expectedQuery), NormalizeSqlQuery(actualQuery.Query));
CollectionAssert.AreEquivalent(expectedParameters, 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 InsertAndSelectScopeIdentity()
{
string expectedQuery = @"
INSERT [TestTable] (Title, FloatVal, DecimalVal, CreationDate)
VALUES(
@TestTable_Title,
@TestTable_FloatVal,
@TestTable_DecimalVal,
@TestTable_CreationDate
)
SELECT SCOPE_IDENTITY()
";
TSqlBuilder builder = new TSqlBuilder();
builder.Insert <TestTable>(
ins => ins
.Set(f => f.Title, "testTitle")
.Set(f => f.FloatVal, 3.14F)
.Set(f => f.DecimalVal, 2.71m)
.Set(f => f.CreationDate, new DateTime(2016, 12, 19, 11, 46, 59))
);
builder.SelectScopeIdentity();
TSqlQuery actualQuery = builder.CompileQuery();
Assert.AreEqual(NormalizeSqlQuery(expectedQuery), NormalizeSqlQuery(actualQuery.Query));
}
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 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 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 CompoundAssignment()
{
string expectedQuery = @"
UPDATE [TestTable]
SET
[TestTable].[Id] *= @TestTable_Id,
[TestTable].[Title] += @TestTable_Title,
[TestTable].[DecimalVal] = @TestTable_DecimalVal
WHERE [TestTable].[Id] = @TestTable_Id_1
";
Dictionary <string, object> expectedParameters = new Dictionary <string, object> {
{ "TestTable_Id", 1 },
{ "TestTable_Title", "testTitle" },
{ "TestTable_Id_1", 10 },
{ "TestTable_DecimalVal", 3.14m }
};
TSqlBuilder builder = new TSqlBuilder();
builder.Update <TestTable>(
upd => upd
.Set(f => f.Id, 1, AssignmentOperator.Multiplication)
.Set(f => f.Title, "testTitle", AssignmentOperator.Addition)
.Set(f => f.DecimalVal, 3.14m)
).Where(t => t.Id == 10);
TSqlQuery actualQuery = builder.CompileQuery();
Assert.AreEqual(NormalizeSqlQuery(expectedQuery), NormalizeSqlQuery(actualQuery.Query));
CollectionAssert.AreEquivalent(expectedParameters, actualQuery.Parameters);
}
public void Insert()
{
string expectedQuery = @"
INSERT [TestTable] (Title, FloatVal, DecimalVal, CreationDate)
VALUES(
@TestTable_Title,
@TestTable_FloatVal,
@TestTable_DecimalVal,
@TestTable_CreationDate
)
";
Dictionary <string, object> expectedParameters = new Dictionary <string, object> {
{ "TestTable_Title", "testTitle" },
{ "TestTable_FloatVal", 3.14F },
{ "TestTable_DecimalVal", 2.71m },
{ "TestTable_CreationDate", new DateTime(2016, 12, 19, 11, 46, 59) }
};
TSqlBuilder builder = new TSqlBuilder();
builder.Insert <TestTable>(
ins => ins
.Set(f => f.Title, "testTitle")
.Set(f => f.FloatVal, 3.14F)
.Set(f => f.DecimalVal, 2.71m)
.Set(f => f.CreationDate, new DateTime(2016, 12, 19, 11, 46, 59))
);
TSqlQuery actualQuery = builder.CompileQuery();
Assert.AreEqual(NormalizeSqlQuery(expectedQuery), NormalizeSqlQuery(actualQuery.Query));
CollectionAssert.AreEquivalent(expectedParameters, 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 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 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 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 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 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 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 UpdateWithTsqlStaement()
{
string expectedQuery = @"
UPDATE [TestTable]
SET
[TestTable].[Title] = @TestTable_Title,
[TestTable].[CreationDate] = SYSUTCDATETIME()
";
Dictionary <string, object> expectedParameters = new Dictionary <string, object> {
{ "TestTable_Title", "testTitle" },
};
TSqlBuilder builder = new TSqlBuilder();
builder.Update <TestTable>(
upd => upd
.Set(f => f.Title, "testTitle")
.Set(f => f.CreationDate, TSqlStatement.SysUtcDateTimeCall)
);
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 UpdateWithDeclarationAsParameter()
{
string expectedQuery = @"
UPDATE [TestTable]
SET
[TestTable].[Id] = @TestTable_Id,
[TestTable].[Title] = @TestTable_Title
";
Dictionary <string, object> expectedParameters = new Dictionary <string, object> {
{ "TestTable_Id", 1 },
{ "TestTable_Title", "testTitle" },
};
TSqlBuilder builder = new TSqlBuilder();
UpdateDeclaration <TestTable> updDeclaration = new UpdateDeclaration <TestTable>();
updDeclaration.Set(f => f.Id, 1);
updDeclaration.Set(f => f.Title, "testTitle");
builder.Update <TestTable>(updDeclaration);
TSqlQuery actualQuery = builder.CompileQuery();
Assert.AreEqual(NormalizeSqlQuery(expectedQuery), NormalizeSqlQuery(actualQuery.Query));
CollectionAssert.AreEquivalent(expectedParameters, actualQuery.Parameters);
}
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 InsertEnumAsInt()
{
string expectedQuery = @"
INSERT [TableWithEnum] (Id, Name, Enum)
VALUES(
@TableWithEnum_Id,
@TableWithEnum_Name,
@TableWithEnum_Enum
)
";
Dictionary <string, object> expectedParameters = new Dictionary <string, object> {
{ "TableWithEnum_Id", 10 },
{ "TableWithEnum_Name", "Test" },
{ "TableWithEnum_Enum", (int)TestEnum.One }
};
TSqlBuilder builder = new TSqlBuilder();
builder.Insert <TableWithEnum>(
ins => ins
.Set(f => f.Id, 10)
.Set(f => f.Name, "Test")
.Set(f => f.Enum, (int)TestEnum.One)
);
TSqlQuery actualQuery = builder.CompileQuery();
Assert.AreEqual(NormalizeSqlQuery(expectedQuery), NormalizeSqlQuery(actualQuery.Query));
CollectionAssert.AreEquivalent(expectedParameters, actualQuery.Parameters);
}
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 InsertWithDeclarationAsParameter()
{
string expectedQuery = @"
INSERT [TestTable] (Title, CreationDate)
VALUES(
@TestTable_Title,
SYSUTCDATETIME()
)
";
Dictionary <string, object> expectedParameters = new Dictionary <string, object> {
{ "TestTable_Title", "testTitle" },
};
TSqlBuilder builder = new TSqlBuilder();
InsertDeclaration <TestTable> insertDecalration = new InsertDeclaration <TestTable>();
insertDecalration.Set(f => f.Title, "testTitle");
insertDecalration.Set(f => f.CreationDate, TSqlStatement.SysUtcDateTimeCall);
builder.Insert <TestTable>(insertDecalration);
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 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 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 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 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);
}
