protected override IExprSubQuery CreateQuery()
{
var tUser = new TableUser();
var tCompany = new TableCompany();
var tCustomer = new TableCustomer();
return(Select(
tCustomer.CustomerId.As(this.CustomerId),
Case()
.When(IsNotNull(tUser.UserId))
.Then(Cast(Literal(1), SqlType.Int16))
.When(IsNotNull(tCompany.CompanyId))
.Then(Cast(Literal(2), SqlType.Int16))
.Else(Null)
.As(this.Type),
Case()
.When(IsNotNull(tUser.UserId))
.Then(tUser.FirstName + " " + tUser.LastName)
.When(IsNotNull(tCompany.CompanyId))
.Then(tCompany.CompanyName)
.Else(Null)
.As(this.Name)
)
.From(tCustomer)
.LeftJoin(tUser, on: tUser.UserId == tCustomer.UserId)
.LeftJoin(tCompany, on: tCompany.CompanyId == tCustomer.CompanyId)
.Done());
}
private static async Task Step15SyntaxModification(ISqDatabase database)
{
var tUser = new TableUser();
Console.WriteLine("Original expression:");
var expression = SelectTop(1, tUser.FirstName).From(tUser).Done();
await expression.QueryScalar(database);
expression = expression
.WithTop(null)
.WithSelectList(tUser.UserId, tUser.FirstName + " " + tUser.LastName)
.WithWhere(tUser.UserId == 7);
Console.WriteLine("With changed selection list and filter:");
await expression.QueryScalar(database);
var tCustomer = new TableCustomer();
expression = expression
.WithInnerJoin(tCustomer, on: tCustomer.UserId == tUser.UserId);
Console.WriteLine("With joined table");
await expression.QueryScalar(database);
}
private static async Task Step7CreatingCustomers(ISqDatabase database)
{
var tUser = new TableUser();
var tCompany = new TableCompany();
var tCustomer = new TableCustomer();
var tSubCustomer = new TableCustomer();
//Users
await InsertInto(tCustomer, tCustomer.UserId)
.From(
Select(tUser.UserId)
.From(tUser)
.Where(!Exists(
SelectOne()
.From(tSubCustomer)
.Where(tSubCustomer.UserId == tUser.UserId))))
.Exec(database);
//Companies
await InsertInto(tCustomer, tCustomer.CompanyId)
.From(
Select(tCompany.CompanyId)
.From(tCompany)
.Where(!Exists(
SelectOne()
.From(tSubCustomer)
.Where(tSubCustomer.CompanyId == tCompany.CompanyId))))
.Exec(database);
}
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}");
}
}
