public void MultipleJoins()
{
var u = new UserTable();
var p1 = new PostTable("p1");
var p2 = new PostTable("p2");
var tv = new CustomTableValuedFunction();
var q = new SharpQuery();
q.Select(u.AllColumns)
.From(u)
.InnerJoin(p1, p1.UserId.IsEqualTo(u.Id))
.InnerJoin(p2, Predicate.TRUE)
.CrossApply(tv, Predicate.TRUE)
.LeftOuterJoin(p2, Predicate.TRUE)
.InnerJoin(p2, Predicate.TRUE)
.CrossApply(tv, Predicate.TRUE)
.LeftOuterJoin(p2, Predicate.TRUE)
.InnerJoin(p2, Predicate.TRUE)
.InnerJoin(p2, Predicate.TRUE)
.InnerJoin(p2, Predicate.TRUE)
.InnerJoin(p2, Predicate.TRUE)
.InnerJoin(p2, Predicate.TRUE)
.InnerJoin(p2, Predicate.TRUE)
.EndStatement();
}
public void InnerJoin()
{
var u = new UserTable();
var p = new PostTable();
var q = new SharpQuery();
q.Select(p.AllColumns)
.From(p)
.InnerJoin(u, u.Id.IsEqualTo(p.UserId))
.Where(u.FirstName.IsEqualTo("Mike"))
.EndStatement();
var script = q.ToString();
TSqlAssert.ScriptsAreEqual(script, @"
SELECT
[dbo].[Post].[Id]
, [dbo].[Post].[UserId]
, [dbo].[Post].[Title]
, [dbo].[Post].[Text]FROM
[dbo].[Post]
INNER JOIN [dbo].[User] ON [dbo].[User].[Id] = [dbo].[Post].[UserId]WHERE
[dbo].[User].[Name] = @p0
");
}
public void CodeUsingSharpQL()
{
var u = new UserTable();
var p = new PostTable();
var q = new SharpQuery();
var condition = Predicate.TRUE;
if (string.IsNullOrWhiteSpace(this.Email)) {
condition &= u.Email.IsEqualTo(this.Email);
}
if (this.Date.HasValue) {
condition &= p.PostDate.IsGreaterThanOrEqualTo(this.Date);
}
q.Select(p.Id, p.Title, p.Text, p.PostDate, u.Email.As("UserMail"))
.From(p)
.InnerJoin(u, p.UserId.IsEqualTo(u.Id))
.Where(condition)
.EndStatement()
.OrderByDesc(p.PostDate)
.EndStatement();
using (var connection = new SqlConnection("connectionString")) {
var cmd = q.CreateCommand(connection);
using (var reader = cmd.ExecuteReader()) {
// code to read data goes here
}
}
}
public void Having()
{
var q = new SharpQuery();
var u = new UserTable();
var p = new PostTable();
int userId = 1;
q.Select(u.Id, Sql.Count(1))
.From(u)
.InnerJoin(p, u.Id.IsEqualTo(p.UserId))
.Where(u.Id.IsEqualTo(userId) & p.PostDate.IsGreaterThanOrEqualTo(DateTime.Now.AddDays(-7)))
.GroupBy(u.Id)
.Having(Sql.Count(1).IsGreaterThan(10))
.EndStatement();
TSqlAssert.ScriptsAreEqual(q.ToString(), @"
SELECT [dbo].[User].[Id],
COUNT(@p0)
FROM [dbo].[User]
INNER JOIN
[dbo].[Post]
ON [dbo].[User].[Id] = [dbo].[Post].[UserId]
WHERE ([dbo].[User].[Id] = @p0
AND [dbo].[Post].[PostDate] >= @p1)
GROUP BY [dbo].[User].[Id]
HAVING COUNT(@p0) > @p2;
");
}
public void QueryAlwaysGeneratesTheSameScript()
{
var q = new SharpQuery();
var u = new UserTable();
var p = new PostTable();
q.Select(u.FirstName, u.Email, p.Title).From(u).LeftOuterJoin(p, p.UserId.IsEqualTo(u.Id)).EndStatement();
var script1 = q.ToString();
var script2 = q.ToString();
TSqlAssert.ScriptsAreEqual(script1, script2);
}
public void Test1()
{
var q = new SharpQuery();
var u = new UserTable();
var p = new PostTable();
q.Select(u.Id, Sql.Count(1))
.From(u)
.InnerJoin(p, p.UserId.IsEqualTo(u.Id))
.GroupBy(u.Id)
.EndStatement();
var query = q.ToString();
TSqlAssert.ScriptsAreEqual(query, @"
SELECT
[dbo].[User].[Id]
, COUNT( @p0 )
FROM [dbo].[User]
INNER JOIN [dbo].[Post] ON [dbo].[Post].[UserId] = [dbo].[User].[Id]
GROUP BY
[dbo].[User].[Id]");
}
public void RightOuterJoin()
{
var u = new UserTable();
var p = new PostTable();
var q = new SharpQuery();
q.Select(u.FirstName, p.Title).From(u).RightOuterJoin(p, p.UserId.IsEqualTo(u.Id)).EndStatement();
var script = q.ToString();
TSqlAssert.ScriptsAreEqual(script, @"
SELECT
[dbo].[User].[Name]
, [dbo].[Post].[Title]FROM
[dbo].[User]RIGHT OUTER JOIN
[dbo].[Post] ON [dbo].[Post].[UserId] = [dbo].[User].[Id]
");
}
