private static async Task Step14TreeExploring(ISqDatabase database)
{
//Var some external filter..
ExprBoolean filter = CustomColumnFactory.Int16("Type") == 2 /*Company*/;
var tableCustomer = new TableCustomer();
var baseSelect = Select(tableCustomer.CustomerId)
.From(tableCustomer)
.Where(filter)
.Done();
//Checking that filter has "Type" column
var hasVirtualColumn = filter.SyntaxTree()
.FirstOrDefault <ExprColumnName>(e => e.Name == "Type") !=
null;
if (hasVirtualColumn)
{
baseSelect = (ExprQuerySpecification)baseSelect.SyntaxTree()
.Modify(e =>
{
var result = e;
//Joining with the sub query
if (e is TableCustomer table)
{
var derivedTable = new DerivedTableCustomer();
result = new ExprJoinedTable(
table,
ExprJoinedTable.ExprJoinType.Inner,
derivedTable,
table.CustomerId == derivedTable.CustomerId);
}
return(result);
});
}
await baseSelect !
.Query(database,
r => Console.WriteLine($"Id: {tableCustomer.CustomerId.Read(r)}"));
}
private static async Task Step8JoinTables(ISqDatabase database)
{
var tUser = new TableUser();
var tCompany = new TableCompany();
var tCustomer = new TableCustomer();
var cType = CustomColumnFactory.Int16("Type");
var cName = CustomColumnFactory.String("Name");
var customers = await Select(
tCustomer.CustomerId,
Case()
.When(IsNotNull(tUser.UserId))
.Then(Cast(1, SqlType.Int16))
.When(IsNotNull(tCompany.CompanyId))
.Then(Cast(2, SqlType.Int16))
.Else(Null)
.As(cType),
Case()
.When(IsNotNull(tUser.UserId))
.Then(tUser.FirstName + " " + tUser.LastName)
.When(IsNotNull(tCompany.CompanyId))
.Then(tCompany.CompanyName)
.Else(Null)
.As(cName)
)
.From(tCustomer)
.LeftJoin(tUser, on: tUser.UserId == tCustomer.UserId)
.LeftJoin(tCompany, on: tCompany.CompanyId == tCustomer.CompanyId)
.QueryList(database,
r => (Id: tCustomer.CustomerId.Read(r), CustomerType: cType.Read(r), Name: cName.Read(r)));
foreach (var customer in customers)
{
Console.WriteLine($"Id: {customer.Id}, Name: {customer.Name}, Type: {customer.CustomerType}");
}
}
