private static async Task Step10UseDerivedTables(ISqDatabase database)
{
var tCustomer = new DerivedTableCustomer("CUST");
var customers = await Select(CustomerData.GetColumns(tCustomer))
.From(tCustomer)
.Where(tCustomer.Type == 2 | tCustomer.Name.Like("%Free%"))
.OrderBy(Desc(tCustomer.Name))
.OffsetFetch(1, 2)
.QueryList(database, r => CustomerData.Read(r, tCustomer));
foreach (var customer in customers)
{
Console.WriteLine($"Id: {customer.Id}, Name: {customer.Name}, Type: {customer.CustomerType}");
}
}
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)}"));
}
