public void ItCanDoASimpleUnion()
{
var expr =
SetOperations.Union(
Select.Star <Person>()
.From <Person>()
.Where <Person>(p => p.Age > 42),
Select.Star <Person>()
.From <Person>()
.Where <Person>(p => p.Name == "Kalle")
);
var result = expr.ToSqlExpression();
Assert.That(result, Is.EqualTo(TokenGeneration_SetOperationTests_Results.simpleUnionExpression));
}
public void ItCanDoAUnionsWithCommonTableExpressions()
{
var identifier = Table <Person> .WithTableAlias("cte");
var expr =
With.Table(
identifier,
SetOperations.Union(
Select.Star <Person>().From <Person>().Where <Person>(p => p.Age > 42),
Select.Star <Person>().From <Person>().Where <Person>(p => p.Name == "Kalle")))
.Query(
SetOperations.Union(
Select.Star(identifier).From(identifier).Where <Person>(identifier, p => p.Age == 50),
Select.Star(identifier).From(identifier).Where <Person>(identifier, p => p.Name == "Kalle")));
var result = expr.ToSqlExpression();
var sql = SqlGen.SqlifyExpression(AnsiSql.Dialect, result);
var sql2 = SqlGen.SqlifyExpression(AnsiSql.Dialect, TokenGeneration_SetOperationTests_Results.unionsInACommonTableExpression);
Assert.That(result, Is.EqualTo(TokenGeneration_SetOperationTests_Results.unionsInACommonTableExpression));
}
public void ItShouldBePossibleToMapUnions()
{
var factory = new HyperboliqConnectionFactory(SqlLite.Dialect, "Data source=:memory:");
using (var con = factory.OpenDbConnection())
{
const string createTable = "CREATE TABLE Person (Id INT, Name VARCHAR(50), Age INT, LivesAtHouseId INT, ParentId INT)";
var cmd1 = con.AsIDbConnection().CreateCommand();
cmd1.CommandText = createTable;
cmd1.ExecuteNonQuery();
var insertQuery = Insert.Into <Person>().AllColumns.Values(
new Person {
Id = 1, Name = "Kalle", Age = 42
},
new Person {
Id = 2, Name = "Pelle", Age = 42
},
new Person {
Id = 3, Name = "Putte", Age = 45
});
con.ExecuteNonQuery(insertQuery);
var selectQuery =
SetOperations.Union(
Select.Star <Person>().From <Person>().Where <Person>(p => p.Age > 42),
Select.Star <Person>().From <Person>().Where <Person>(p => p.Name == "Kalle")
);
var result = con.Query <Person>(selectQuery);
Assert.That(result, Has.Count.EqualTo(2));
Assert.True(result.Any(p => p.Id == 1 && p.Name == "Kalle" && p.Age == 42));
Assert.True(result.Any(p => p.Id == 3 && p.Name == "Putte" && p.Age == 45));
}
}