public void ComplexWhereClause()
{
#region expected
string expectedQ1 =
@"SELECT
[dbo].[Blogs].[Id]
FROM
[dbo].[Blogs]
WHERE
(([dbo].[Blogs].[Id] > 2) OR ([dbo].[Blogs].[Name] = N'Ken'))
";
#endregion
var from = new FromClause(SQL.Blogs);
var whereIdGraterThan2 = new WhereClause(SQL.Blogs.Id > 2);
var whereIdNameIsKen = new WhereClause(SQL.Blogs.Name == "Ken");
SQLQuery q1 = SQLQuery
.Select(SQL.Blogs.Id)
.From(from)
.Where(whereIdGraterThan2 || whereIdNameIsKen);
Assert.AreEqual(expectedQ1, q1.ToString());
}
public void ToString_WithAlias_GeneratesCorrectSQLClause()
{
Tables_Blogs table = new Tables_Blogs().As("MyTable");
var from = new FromClause(table);
string expected =
@"FROM
[dbo].[Blogs] AS [MyTable]
";
Assert.AreEqual(expected, from.ToString());
}
public void ToString_Always_GeneratesCorrectSQLClause()
{
var table = new Tables_Blogs();
var from = new FromClause(table);
string expected =
@"FROM
[dbo].[Blogs]
";
Assert.AreEqual(expected, from.ToString());
}
public void ToString_WithJoin_GeneratesCorrectSQLClause()
{
var blogs = new Tables_Blogs();
var posts = new Tables_Posts();
FromClause from = new FromClause(blogs).Join(posts, blogs.Id == posts.BlogId);
string expected =
@"FROM
[dbo].[Blogs]
JOIN [dbo].[Posts] ON
([dbo].[Blogs].[Id] = [dbo].[Posts].[BlogId])
";
Assert.AreEqual(expected, from.ToString());
}
public void ReusingClauses()
{
#region expected
string expectedQ1 =
@"SELECT
[dbo].[Blogs].[Id]
FROM
[dbo].[Blogs]
WHERE
([dbo].[Blogs].[Id] = 2)
";
string expectedQ2 =
@"SELECT
[dbo].[Blogs].[Name]
FROM
[dbo].[Blogs]
WHERE
([dbo].[Blogs].[Id] = 2)
";
#endregion
var from = new FromClause(SQL.Blogs);
var where = new WhereClause(SQL.Blogs.Id == 2);
SQLQuery q1 = SQLQuery
.Select(SQL.Blogs.Id)
.From(from)
.Where(where);
SQLQuery q2 = SQLQuery
.Select(SQL.Blogs.Name)
.From(from)
.Where(where);
Assert.AreEqual(expectedQ1, q1.ToString());
Assert.AreEqual(expectedQ2, q2.ToString());
}
