public void To_String()
{
IAlias person = sql.Alias("person");
ICte cte = sql.Cte("cte").As(sql.Query.Select(person["Id"], person["Name"]).From(person));
Assert.Equal("cte AS (SELECT person.Id, person.Name FROM person)", cte.ToString());
}
public void With_Select_From_Join_Where()
{
IAlias person = sql.Alias("person");
IAlias dept = sql.Alias("dept");
IAlias personCte = sql.Alias("", "personCte");
ICte cte = sql.Cte("cte").As(sql.Query
.Select(person.All)
.From(person)
.Where(person["Active"].Eq(true)));
IQuery query = sql.Query
.With(cte)
.Select(personCte.All, dept.All)
.From(cte, personCte)
.Join(dept)
.On(dept["Id"].Eq(personCte["DepartmentId"]));
QueryResult result = engine.Compile(query);
Assert.Equal("WITH \"cte\" AS (SELECT \"person\".* FROM \"person\" WHERE \"person\".\"Active\" = @p0) "
+ "SELECT \"personCte\".*, \"dept\".* FROM \"cte\" AS \"personCte\" INNER JOIN \"dept\" "
+ "ON \"dept\".\"Id\" = \"personCte\".\"DepartmentId\"", result.Sql);
Assert.Equal(new Dictionary <string, object>()
{
["@p0"] = true
}, result.Parameters);
}
public void To_String_With()
{
IAlias person = sql.Alias("person");
ICte cte = sql.Cte("personCte").As(sql.Query.Select(person.All).From(person));
IQuery query = sql.Query.With(cte);
Assert.Equal("WITH personCte AS (SELECT person.* FROM person)", query.ToString());
}
public void With_ToString()
{
IAlias person = sql.Alias("person");
ICte cte = sql.Cte("cte1").As(sql.Query.Select(person["Id"], person["Name"]).From(person));
IWith with = sql.With.Add(cte);
Assert.Equal("WITH cte1 AS (SELECT person.Id, person.Name FROM person)", with.ToString());
}
public void From_Cte_String()
{
IAlias person = sql.Alias("person");
ICte cte = sql.Cte("cte").As(sql.Query.Select(person.All).From(person));
IFrom from = sql.From(cte, "personCte");
QueryResult result = engine.Compile(from);
Assert.Equal("FROM \"cte\" AS \"personCte\"", result.Sql);
}
public void Cte_RawQuery()
{
IAlias person = sql.Alias("person");
ICte cte = sql.Cte("cte").As(sql.RawQuery("SELECT {0}, {1} FROM {2}", person["Id"], person["Name"], person));
QueryResult result = engine.Compile(cte);
Assert.Equal("\"cte\" AS (SELECT \"person\".\"Id\", \"person\".\"Name\" FROM \"person\")",
result.Sql);
}
public void With_Raw()
{
IAlias person = sql.Alias("person");
ICte cte = sql.Cte("personCte").As(sql.Query.Select(person.All).From(person));
IQuery query = sql.Query.With(sql.Raw("WITH {0}", cte));
QueryResult result = engine.Compile(query);
Assert.Equal("WITH \"personCte\" AS (SELECT \"person\".* FROM \"person\")", result.Sql);
}
public void Cte_Without_Columns()
{
IAlias person = sql.Alias("person");
ICte cte = sql.Cte("cte").As(sql.Query.Select(person["Id"], person["Name"]).From(person));
QueryResult result = engine.Compile(cte);
Assert.Equal("\"cte\" AS (SELECT \"person\".\"Id\", \"person\".\"Name\" FROM \"person\")",
result.Sql);
}
public void Join_Cte_String()
{
IAlias person = sql.Alias("person");
ICte cte = sql.Cte("cte").As(sql.Query.Select(person.All).From(person));
IJoin join = sql.Join(cte, "personCte");
QueryResult result = engine.Compile(join);
Assert.Equal("INNER JOIN \"cte\" AS \"personCte\"", result.Sql);
}
public void Cte_With_Columns_Expression()
{
Person person = null;
ICte cte = sql.Cte("cte").Add(() => person.Id, () => person.Name)
.As(sql.Query.Select(() => person.Id, () => person.Name).From(() => person));
QueryResult result = engine.Compile(cte);
Assert.Equal("\"cte\" (\"Id\", \"Name\") AS (SELECT \"person\".\"Id\", \"person\".\"Name\" "
+ "FROM \"Person\" AS \"person\")", result.Sql);
}
public void Join_Cte_Expression()
{
Person person = null;
Person personCte = null;
ICte cte = sql.Cte("cte").As(sql.Query.Select(() => person).From(() => person));
IJoin join = sql.Join(cte, () => personCte);
QueryResult result = engine.Compile(join);
Assert.Equal("INNER JOIN \"cte\" AS \"personCte\"", result.Sql);
}
public void From_Cte_Expression()
{
Person person = null;
Person personCte = null;
ICte cte = sql.Cte("cte").As(sql.Query.Select(() => person).From(() => person));
IFrom from = sql.From(cte, () => personCte);
QueryResult result = engine.Compile(from);
Assert.Equal("FROM \"cte\" AS \"personCte\"", result.Sql);
}
public void With_Params()
{
IAlias person = sql.Alias("person");
IAlias dept = sql.Alias("dept");
ICte cte1 = sql.Cte("personCte").As(sql.Query.Select(person.All).From(person));
ICte cte2 = sql.Cte("deptCte").As(sql.Query.Select(dept.All).From(dept));
IQuery query = sql.Query.With(cte1, cte2);
QueryResult result = engine.Compile(query);
Assert.Equal("WITH \"personCte\" AS (SELECT \"person\".* FROM \"person\"), "
+ "\"deptCte\" AS (SELECT \"dept\".* FROM \"dept\")", result.Sql);
}
public void Add_Params()
{
IAlias person = sql.Alias("person");
IAlias dept = sql.Alias("dept");
ICte cte1 = sql.Cte("cte1").As(sql.Query.Select(person.All).From(person));
ICte cte2 = sql.Cte("cte2").As(sql.Query.Select(dept.All).From(dept));
IWith with = sql.With.Add(cte1, cte2);
QueryResult result = engine.Compile(with);
Assert.Equal("WITH \"cte1\" AS (SELECT \"person\".* FROM \"person\"), "
+ "\"cte2\" AS (SELECT \"dept\".* FROM \"dept\")", result.Sql);
}
public void Add_Expression_Enumerable()
{
Person person = null;
ICte cte = sql.Cte("cte")
.Add(new List <Expression <Func <object> > > {
() => person.Id, () => person.Name
})
.As(sql.Query.Select(() => person.Id, () => person.Name).From(() => person));
QueryResult result = engine.Compile(cte);
Assert.Equal("\"cte\" (\"Id\", \"Name\") AS (SELECT \"person\".\"Id\", \"person\".\"Name\" "
+ "FROM \"Person\" AS \"person\")", result.Sql);
}
public void Add_Enumerable()
{
IAlias person = sql.Alias("person");
ICte cte = sql.Cte("cte")
.Add(new List <IColumn> {
person["Id"], person["Name"]
})
.As(sql.Query.Select(person["Id"], person["Name"]).From(person));
QueryResult result = engine.Compile(cte);
Assert.Equal("\"cte\" (\"Id\", \"Name\") AS (SELECT \"person\".\"Id\", \"person\".\"Name\" "
+ "FROM \"person\")", result.Sql);
}
public void Add_All_Columns()
{
Person person = null;
ICte cte = sql.Cte("cte")
.Add(() => person)
.As(sql.Query.Select(() => person).From(() => person));
QueryResult result = engine.Compile(cte);
Assert.Equal("\"cte\" (\"Id\", \"Active\", \"Name\", \"SurName\", \"AddressStreet\", \"AddressCity\", "
+ "\"Salary\", \"DateCreated\", \"DepartmentId\", \"Image\") AS (SELECT \"person\".\"Id\", \"person\".\"Active\", "
+ "\"person\".\"Name\", \"person\".\"SurName\", \"person\".\"AddressStreet\", \"person\".\"AddressCity\", "
+ "\"person\".\"Salary\", \"person\".\"DateCreated\", \"person\".\"DepartmentId\", \"person\".\"Image\" "
+ "FROM \"Person\" AS \"person\")", result.Sql);
}
public void With_Recursive()
{
engine.Options.WithRecursive = true;
IAlias person = sql.Alias("person");
IAlias dept = sql.Alias("dept");
ICte cte1 = sql.Cte("cte1").As(sql.Query.Select(person.All).From(person));
ICte cte2 = sql.Cte("cte2").As(sql.Query.Select(dept.All).From(dept));
IWith with = sql.With.Add(cte1).Add(cte2);
QueryResult result = engine.Compile(with);
Assert.Equal("WITH RECURSIVE \"cte1\" AS (SELECT \"person\".* FROM \"person\"), "
+ "\"cte2\" AS (SELECT \"dept\".* FROM \"dept\")", result.Sql);
}
public void Cte_Name()
{
ICte cte = sql.Cte("cte");
Assert.Equal("cte", cte.Name);
}