public void Select_From_Join_Where_GroupBy_Having_OrderBy_Offset()
{
IAlias person = sql.Alias("person");
IAlias dept = sql.Alias("dept");
IQuery query = sql.Query
.Select(dept["Id"], dept["Name"], SqlFn.Count())
.From(person)
.Join(dept)
.On(dept["Id"].Eq(person["DepartmentId"]))
.Where(person["Active"].Eq(true))
.GroupBy(dept["Id"], dept["Name"])
.Having(SqlFn.Count().Gt(10))
.OrderBy(x => x.Add(dept["Name"]))
.Offset(10, 20);
QueryResult result = engine.Compile(query);
Assert.Equal("SELECT \"dept\".\"Id\", \"dept\".\"Name\", COUNT(*) FROM \"person\" INNER JOIN \"dept\" "
+ "ON \"dept\".\"Id\" = \"person\".\"DepartmentId\" WHERE \"person\".\"Active\" = @p0 "
+ "GROUP BY \"dept\".\"Id\", \"dept\".\"Name\" HAVING COUNT(*) > @p1 ORDER BY \"dept\".\"Name\" "
+ "OFFSET @p2 ROWS FETCH NEXT @p3 ROWS ONLY", result.Sql);
Assert.Equal(new Dictionary <string, object>()
{
["@p0"] = true, ["@p1"] = 10, ["@p2"] = 10, ["@p3"] = 20
},
result.Parameters);
}
public void Count()
{
IFunction func = SqlFn.Count();
QueryResult result = engine.Compile(func);
Assert.Equal("COUNT(*)", result.Sql);
Assert.Equal(new Dictionary <string, object>(), result.Parameters);
}
public void Over()
{
ISelect select = sql.Select().Add(SqlFn.Count()).Over();
QueryResult result = engine.Compile(select);
Assert.Equal("SELECT COUNT(*) OVER()", result.Sql);
Assert.Equal(new Dictionary <string, object>(), result.Parameters);
}
public void Count_Column()
{
IAlias person = sql.Alias("person");
IFunction func = SqlFn.Count(person["Name"]);
QueryResult result = engine.Compile(func);
Assert.Equal("COUNT(\"person\".\"name\")", result.Sql);
Assert.Equal(new Dictionary <string, object>(), result.Parameters);
}
public void To_String()
{
IAlias person = sql.Alias("person");
IAlias dept = sql.Alias("dept");
IQuery query = sql.Query
.Select(dept["Id"], dept["Name"], SqlFn.Count())
.From(person)
.Join(dept)
.On(dept["Id"].Eq(person["DepartmentId"]))
.Where(person["Active"].Eq(true))
.GroupBy(dept["Id"], dept["Name"])
.Having(SqlFn.Count().Gt(10))
.OrderBy(x => x.Add(dept["Name"]))
.Offset(10, 20);
Assert.Equal("SELECT dept.Id, dept.Name, COUNT(*) FROM person INNER JOIN dept "
+ "ON dept.Id = person.DepartmentId WHERE person.Active = true "
+ "GROUP BY dept.Id, dept.Name HAVING COUNT(*) > 10 ORDER BY dept.Name "
+ "OFFSET 10 FETCH 20", query.ToString());
}
public void To_String_Over()
{
ISelect select = sql.Select().Add(SqlFn.Count()).Over();
Assert.Equal("SELECT COUNT(*) OVER()", select.ToString());
}