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