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)}")); }