Framework Demo

This demo, based on SqlServer, is meant to show the versatility of the WYSE framework and the freedom that goes wit it. Combining .NET with SQL to create something that is more and better than the sum of the two parts. The following are examples of how better database interaction could take shape, but should not be considered as 'this is how you should do it'. WYSE is built to offer the freedom to do it the way you want. The idea is to be able to code without having to make code quality damaging compromises.

...and since code speaks louder than words demo code (stack trace) and database results are shown in a developer-tools-like panel as you interact with the server.

Advanced Language Features

The framework supports several ways of building queries and also facilitates dynamic SQL statements decoration. The latter enables to statements to be modified, which in turn offers the possibility to neatly encapsulate query building code centrally.

Because WYSE's SQL statements have the fluent structure of plain SQL but are actually .NET class-based, code reuse becomes a lot easier and more flexible and powerful, especially if your tables/views share characteristics.

The following example uses custom Selects that are tailored to SQL tables that all have an 'Id' column and an 'IsDeleted' column. Because of this, the logic that goes with that is encapsulated in a base Subselect class (that, in turn, inherits from WYSE's Select class)

Additionally, the wrapped Selects implement the ability to produce so-called GenericMappings. They are WYSE's way of taking the mapping code out of the individual joins and storing them centrally. This benefits code reuse greatly (and they make some other unique and cool hierarchical things possible too).

Get a dashboard by id (complex query)

Building on the previous example featuring custom Selects... The following examples feature a shared/generic repository base that implements basic CRUD operations.

This repository base uses a centralized custom QueryBackbone that can decorate a query in a way that paging is added.

Get an address by id Get addresses (with centralized custom paging) Create an address Delete an address by id Upsert an address by id

As an alternative for building queries the fluent way, WYSE also has builders for Selects, Inserts, Deletes, and Updates. In their simple form they are good as a short and easy way to create basic statements. Beyond that they are great for conditionally adding commonly used query parts like OrderBy, Top and Having for example. They are also useful as a base to supply default parts for your application's queries.

Among other things, The next demo example shows a builder that guarantees proper usage of an IsDeleted column in the Where clause.

Get customers using a builder (with paging)

To be able to dynamically rebuild a given query based on your needs and the rules you dictate can be a very powerful way to elevate your database usage. The versatility it offers can help reduce the need for having to code similar SQL statements (or parts of them) throughout a code base. And that will improve your software's overall maintainability.

In the three examples below the custom QueryBackbone is used to add this demo's division-based security SQL code when/where needed. In this demo that is anywhere in a query where a table is used that has a DivisionId column (like the Customer table) and because of that an additional InnerJoin is needed.
Please note: This custom division security is hierarchical in nature.

Get customer fluently Get customers fluently (with paging) Get customers with generic mapping (with paging)

In the case of MySql and SqlServer WYSE supports row-level security (RLS) through SQL statement decoration. This means SQL statements can be constructed without bothering with security, and afterward WYSE rebuilds the original statement based on the security situation.
MySql supports user-based RLS. With SqlServer both user and group-based RLS are supported.

A 'Select' is security-decorated An 'Insert' is security-decorated (within a rollbacked transaction) An 'Update' is security-decorated (within a rollbacked transaction) A 'Delete' is security-decorated (within a rollbacked transaction)

SQL is versatile, but there are things it is not good at. For example, cases where one row has to be paired with the next. To help the developer out WYSE comes with a means to enumerate transitions generically. The following example uses that.

A list of price transitions with a minimal price increase

Everything changes in time. Because of that data oftentimes pertains to a certain period. In those cases where you want to store periods that are guaranteed adjacent (going from one record to another) what you want to store is a single date, and not a from/to (because it is more cumbersome to manage and maintain validity).
WYSE assists here by offering a specialized dictionary. Using it you can easily go from records stored with a single date (in this case 'StartingFrom') to a period-key/item dictionary.
The following example uses the dictionary to retrieve the periods a product had a certain price.

A dictionary with date ranges for a key and items as the values

PS Besides DateTime, other types can be used in pairs to form a range making a dictionary key.

The small window you are using greatly diminishes your demo experience. Please consider viewing using a larger window.

Trace Entries: 2

...Framework.Controllers.HomeController.GetCustomersWithAddressesGenerically:

[HttpGet]
public async Task<ViewResult> GetCustomersWithAddressesGenerically(
    [FromQuery] PagingSettings pagingSettings)
{
    var traceTree = _traceTreesRepository.Record();
    traceTree.RecordCodeMethodExecution(MethodBase.GetCurrentMethod());

    var result = await _customerService.GetWithAddressesGenericallyResult(
        pagingSettings,
        traceTree);

    return CreateView(traceTree, result);
}

Trace Entries: 6

...Framework.Services.CustomerService.GetWithAddressesGenericallyResult:

public async Task<PagedResult<Customer>> GetWithAddressesGenericallyResult(
    PagingSettings pagingSettings,
    TraceTree parentTraceTree)
{
    var traceTree = parentTraceTree.AddChild();
    traceTree.RecordCodeMethodExecution(MethodBase.GetCurrentMethod());

    // Inherit from WYSE Select...
    // CustomerSubselect supports IGenericMappingsSupplier
    // The Select (its top level joins) plus generic mappings determine specific mappings
    var customerSubselect = new CustomerSubselect(traceTree, pagingSettings);
    var homeAddressSubselect = new AddressSubselect();
    var billingAddressSubselect = new AddressSubselect();

    Select select = new /*WYSE*/Select()
        .Everything()  // Effectively an '*', but explicit
        .From(customerSubselect)
        .InnerJoin(homeAddressSubselect, customerSubselect.HomeAddressId == homeAddressSubselect.Id)
        .LeftJoin(billingAddressSubselect, customerSubselect.BillingAddressId == billingAddressSubselect.Id);

    return await GetItemTreesAsync(
        select,
        pagingSettings,
        customerSubselect.Convert,
        traceTree);
}

Trace Entries: 1

...Framework.DataSpace.Queries.ItemSubselectBase ctor:

/*
 * ItemSubselectBase is the Framework Demo base class for all subselects used in
 * repositories and other dynamic query building.
 * This class inherits from WYSE's convertable subselect that enables query result row 
 * conversion (extending DbDataReader) and (Hierarchical) Object Relational Mapping.
 * Usage examples (different overloads):
 * - var select = new AddressSelect()  // Gets all addresses
 * - var select = new AddressSelect(id: new Guid("11111111-1111-1111-1111-111111111111"))  // Get a single address by id
 * - var select = new AddressSelect(isDeleted: true)  // Only gets the deleted addresses
 * - var select = new AddressSelect(new PagingSettings { PageNumber = 3, PageSize = 20 })
 * So a Select with WYSE can be used to encapsulate SQL logic,
 * making it kind of like a coded version of a SqlServer function or view,
 * but much more flexible!
 */
protected ItemSubselectBase(
    ItemTableBase<TItem> sourceTable,
    TraceTree parentTraceTree,
    PagingSettings? pagingSettings,  /* If not null then paging is added to the query */
    Guid? id,  /* If not null then a select by id is done */
    bool? isDeleted)  /* If 'null' then both deleted and not deleted */
{
    var traceTree = parentTraceTree?.AddChild();
    traceTree?.RecordCodeConstructorExecution(typeof(ItemSubselectBase<TSelect, TItem>));

    _sourceListConvert = sourceTable.Convert;

    // Funneling is a concept used in WYSE to expose the columns of a (nested)
    // subselect one level higher in a query, so it can be used in a From or in
    // Where/Join expressions for example.
    Id = /*WYSE*/Funnel(sourceTable.Id);
    Created = /*WYSE*/Funnel(sourceTable.Created);
    LastModified = /*WYSE*/Funnel(sourceTable.LastModified);
    IsDeleted = /*WYSE*/Funnel(sourceTable.IsDeleted);

    var columnExpressions = new List</*WYSE*/IExpression>(
        sourceTable./*WYSE*/ListColumns());

    if (pagingSettings is not null)
    {
        // Add SqlServer's 'COUNT(Id) OVER()' to selected columns.
        // The modifying of the Where clause for row selection is done in
        // the query backbone. Check the traces below.
        sourceTable.CountUnpaged = new /*WYSE*/Count(sourceTable.Id).Over();
        CountUnpaged = /*WYSE*/Funnel(sourceTable.CountUnpaged);

        columnExpressions.Add(sourceTable.CountUnpaged);
    }

    // This makes the statement: SELECT [columnExpressions] FROM [sourceList]
    /*WYSE*/From from = this./*WYSE*/Columns(columnExpressions).From(sourceTable);

    // Determine the applicable where clause sub expressions (based on SQL parameters)
    var whereExpressions = new List</*WYSE*/BooleanExpression>();

    if (id.HasValue)
    {
        whereExpressions.Add(
            sourceTable.Id == new /*WYSE*/UniqueIdentifierParam("Id", id));
    }
    if (isDeleted.HasValue)
    {
        whereExpressions.Add(
            sourceTable.IsDeleted == new /*WYSE*/BitParam("IsDeleted", isDeleted));
    }

    if (whereExpressions.Count > 0)
    {
        // Append a Where clause with a condition that is all the
        // sub expressions 'AND'-ed together
        from.Where(whereExpressions./*WYSE*/And());
    }
}

Trace Entries: 4

...Framework.Services.ServiceBase.GetItemTreesAsync:

protected async Task<PagedResult<TItem>> GetItemTreesAsync<TItem>(
    Select select,
    PagingSettings pagingSettings,
    Converter<DbDataReader, TItem> converter,
    TraceTree parentTraceTree)
    where TItem : ItemBase
{
    var traceTree = parentTraceTree.AddChild();
    traceTree.RecordCodeMethodExecution(MethodBase.GetCurrentMethod());

    // In Wyse, mappings facilitate Hierarchical Object Relational Mapping.
    // The information to do is reflected/extracted from the Select at runtime.
    // This way you have complete control of how your objects are built and you always
    // have the desired Select/mappings combination.
    // No more no less, in the right place, always. Best efficiency possible.
    // NOTE: Select reflection is based on the implementation of an interface of both
    // the list involved in the join as the (list of) POCO domain object(s).
    // PS: Ad hoc (custom) mapping can be used as well, and in conjunction with reflection.
    var mappings = /*WYSE*/MappingsFactory.GetMappings(select);

    // A scriptable can be anything in the WYSE language that can be part of a script:
    // - Executables; like Selects, Inserts, Updates, Deletes, Merges (SqlServer only)
    // - Variable and Temp table declarations
    // - If/Elses
    // ...and more.
    var scriptables = new List</*WYSE*/IScriptable>();

    _queryBackbone.Using(pagingSettings, traceTree).Modify(select, scriptables);

    var pageItems = await _sqlExecuter.GetScriptItemTreesAsync(
        // Create new script combining scriptables with select
        new Script(scriptables) + select,
        select,
        converter,  /* of the primary/root table, used in the From of the Select */
        mappings,
        parentTraceTree);

    return new PagedResult<TItem>(pagingSettings, pageItems);
}

Trace Entries: 1

...Framework.DataSpace.Queries.Modification.QueryBackbone.Modify:

public override void Modify(
    /*WYSE*/Select select,
    List</*WYSE*/IScriptable> preparationScriptables)
{
    var traceTree = _parentTraceTree.AddChild();
    traceTree.RecordCodeMethodExecution(MethodBase.GetCurrentMethod());

    // You can build a pipleline of modifiers to modify an executable (here a Select),
    // and add scriptables (like table variables) used in the modified Select.
    // These modifiers let you cut/paste strongly typed SQL language snippets.
    // In the case of this Framework Demo there are 3 custom modifiers:
    // - Division security decorator
    // - Paging
    // - Identifiers resetter
    // ...more on those below.
    base.Modify(select, preparationScriptables);
}

Trace Entries: 4

...Framework.DataSpace.Queries.Modification.DivisionSecurityDecorator.Modify:

public void Modify(/*WYSE*/Select select, List</*WYSE*/IScriptable> scriptables)
{
    var traceTree = _parentTraceTree.AddChild();
    traceTree.RecordCodeMethodExecution(MethodBase.GetCurrentMethod());

    if (!select.Uses<IDivisionReferringTable>())
    {
        return;
    }

    // Put all the Ids of the divisions the current user has rights to in a 
    // TableVariable (Inherits from WYSE SqlServer TableVariable).
    var userDivisionIdsTableVariable = new UserDivisionIdsTableVariable();

    scriptables.Add(new /*WYSE*/Declare(userDivisionIdsTableVariable));

    var fillTableVariable = new UserDivisionIdsTableVariable.Fill(
        userDivisionIdsTableVariable,
        _sessionContext.CurrentUser.DivisionId,
        traceTree);

    scriptables.Add(fillTableVariable);

    // This is a WYSE ISnippetReplacementFactory
    var securityReplacement = new SecurityReplacement(
        userDivisionIdsTableVariable,
        traceTree);

    // SQL Statements can be remade by chaining multiple ISnippetReplacementFactory instances.
    // Here just one is needed.
    _ = /*WYSE*/StatementRebuilder.Rebuild(
        select,
        /* rebuild the original Select, instead of a clone of it that is returned */
        rebuildOnClone: false,
        [securityReplacement]);
}

Trace Entries: 2

...Framework.DataSpace.Queries.Systems.UserDivisionIdsTableVariable+Fill ctor:

// The Fill class inherits from WYSE InsertInto
public Fill(
    UserDivisionIdsTableVariable tableVariable,
    Guid userDivisionId,
    TraceTree parentTraceTree)
    : base(tableVariable)
{
    var traceTree = parentTraceTree.AddChild();
    traceTree.RecordCodeConstructorExecution(typeof(Fill));

    var getDescendents = new GetDescendentsTableFunction(userDivisionId, traceTree);

    Select getDescendentsSelect = new /*WYSE*/Select()
        .Columns(getDescendents.DivisionId)
        .From(getDescendents);

    // Insert into tableVariable the Select result of the table function.
    this./*WYSE*/Columns(tableVariable)
        .Select(getDescendentsSelect);
}

Trace Entries: 1

...Framework.DataSpace.Entities.Relational.Systems.GetDescendantsTableFunction ctor:

// Returns a table with a division id and the ids of all its descendents.
public GetDescendantsTableFunction(
    Guid divisionId,
    TraceTree parentTraceTree)
    : base()
{
    var traceTree = parentTraceTree.AddChild();
    traceTree.RecordCodeConstructorExecution(GetType());

    // When just using/calling the function setting the parameter value is enough.
    DivisionIdParam.SetValue(divisionId);

    var parentTable = new DivisionHierarchyTable().WithAlias("parent");
    var childTable = new DivisionHierarchyTable().WithAlias("child");
    var inTable = new DivisionHierarchyTable().WithAlias("i");

    // This Select is NOT necessary when merely calling the function.
    // (only needed when WYSE deploys the database).
    // The Select is added here just to show what is under the GetDescendents-hood.
    Select = new Select()
        .Distinct()
        .Columns(childTable.DivisionId.WithAlias("DivisionId"))
        .From(parentTable)
        // DivisionNodeId is of SqlServer type 'HierarchyId'
        // IsDescendantOf is a built-in SqlServer function that WYSE supports
        .InnerJoin(
            childTable,
            childTable.DivisionNodeId
                .IsDescendantOf(parentTable.DivisionNodeId) == true)
        .Where(parentTable.DivisionId.In(
            new Select()
                .Columns(inTable.DivisionId.WithAlias("DivisionId"))
                .From(inTable)
                .Where(inTable.DivisionId == DivisionIdParam)));
}

Trace Entries: 1

...Framework.DataSpace.Queries.Modification.DivisionSecurityDecorator+SecurityReplacement.MustReplace:

// In WYSE you can control whether or not replacement is necessary
// for a SQL statement snippet.
public bool MustReplace(Snippet snippet)
{
    if (!_mustReplaceTraceHasBeenRecorded)
    {
        var traceTree = _parentTraceTree.AddChild();
        traceTree.RecordCodeMethodExecution(MethodBase.GetCurrentMethod());
        _mustReplaceTraceHasBeenRecorded = true;
    }

    return
        snippet is /*WYSE*/From from &&
            from.List is IDivisionReferringTable
        ||
        snippet is /*WYSE*/Join join &&
            join.TargetList is IDivisionReferringTable;
}

Trace Entries: 2

...Framework.DataSpace.Queries.Modification.DivisionSecurityDecorator+SecurityReplacement.GetReplacements:

// In WYSE replacing can be used to remove, change, or add as you see fit.
public IReadOnlyList</*WYSE*/Snippet> GetReplacements(
    /*WYSE*/Snippet snippet)
{
    var traceTree = _parentTraceTree.AddChild();
    traceTree.RecordCodeMethodExecution(MethodBase.GetCurrentMethod());

    // In this case we always want to keep the original SQL snippet.
    var replacements = new List</*WYSE*/Snippet> { snippet };

    if (snippet is /*WYSE*/From from &&
        from.List is IDivisionReferringTable fromTable)
    {
        replacements.Add(CreateDivisionSecurityJoin(fromTable, traceTree));
    }
    else if (snippet is /*WYSE*/Join join &&
        join.TargetList is IDivisionReferringTable joinTable)
    {
        replacements.Add(CreateDivisionSecurityJoin(joinTable, traceTree));
    }

    return replacements;
}

Trace Entries: 1

...Framework.DataSpace.Queries.Modification.DivisionSecurityDecorator+SecurityReplacement.CreateDivisionSecurityJoin:

private InnerJoin CreateDivisionSecurityJoin(
    IDivisionReferringTable referringTable,
    TraceTree parentTraceTree)
{
    var traceTree = parentTraceTree.AddChild();
    traceTree.RecordCodeMethodExecution(MethodBase.GetCurrentMethod());

    Select divisionsSelect = new /*WYSE*/Select()
        .Columns(_userDivisionIdsTableVariable.DivisionId)
        .From(_userDivisionIdsTableVariable);
    // Alternatively...
    // var divisionsSelect = /*WYSE*/SelectBuilder.Build(_userDivisionIdsTableVariable);

    // Join with the funneled division id of the table variable used
    // in the division select.
    var joinExpression = referringTable.DivisionId ==
        divisionsSelect[_userDivisionIdsTableVariable.DivisionId];

    return new /*WYSE*/InnerJoin(divisionsSelect, joinExpression);
}

Trace Entries: 2

...Framework.DataSpace.Queries.Modification.PagingDecorator.Modify:

public void Modify(/*WYSE*/Select select, List</*WYSE*/IScriptable> preparationScriptables)
{
    var traceTree = _parentTraceTree.AddChild();
    traceTree.RecordCodeMethodExecution(MethodBase.GetCurrentMethod());
    
    if (_pagingSettings is null)
    {
        return;
    }

    // SQL Statements can be remade by chaining multiple ISnippetReplacementFactory instances.
    // Here just one is needed.
    _ = /*WYSE*/StatementRebuilder.Rebuild(
        select,
        /* rebuild the original Select, instead of a clone of it that is returned */
        rebuildOnClone: false,
        [new PagingAppending(select, _pagingSettings, traceTree)]);
}

Trace Entries: 1

...Framework.DataSpace.Queries.Modification.PagingDecorator+PagingAppending.GetReplacements:

public IReadOnlyList</*WYSE*/Snippet> GetReplacements(/*WYSE*/Snippet snippet)
{
    var traceTree = _parentTraceTree.AddChild();
    traceTree.RecordCodeMethodExecution(MethodBase.GetCurrentMethod());

    return new List</*WYSE*/Snippet>
    {
        snippet,
        // SqlServer paging demands an OrderBy so we supply a default one if absent.
        // Default OrderBy here is on the first column.
        snippet is /*WYSE*/OrderBy ? null : new /*WYSE*/OrderBy(1),
        new /*WYSE*/OffsetRows(_pagingSettings.PageNumber * _pagingSettings.PageSize),
        new /*WYSE*/FetchNextRowsOnly(_pagingSettings.PageSize)
    }.NotNulls();
}

Trace Entries: 1

...Framework.DataSpace.Queries.Customers.CustomerSubselect.GetGenericMappings:

// Generic mappings are WYSE's way of defining mappings separate from a
// specific Select, in order to facilitate reuse.
public IReadOnlyList</*WYSE*/GenericMapping> GetGenericMappings()
{
    var traceTree = _parentTraceTree?.AddChild();
    traceTree?.RecordCodeMethodExecution(MethodBase.GetCurrentMethod());

    if (_genericMappings is null)
    {
        // Factory for creating generic mappings starting from a
        // 'CustomerSubselect' source list, having a domain object
        // of type 'Customer'
        var factory = new /*WYSE*/GenericMappingsFactory<CustomerSubselect, Customer>();

        _genericMappings = new /*WYSE*/GenericMappingList(
            // Target list is a 'AddressSubselect' and maps to a type 'Address'
            factory.CreateMapping<AddressSubselect, Address>(
                (s, a) => s.HomeAddressId == a.Id,
                c => c.HomeAddress),
            factory.CreateMapping<AddressSubselect, Address>(
                (s, a) => s.BillingAddressId == a.Id,
                c => c.BillingAddress));
    }
    
    return _genericMappings;
}

Trace Entries: 1

...Framework.DataSpace.Access.SqlExecuter.GetScriptItemTreesAsync:

public async Task<IReadOnlyList<TItem>> GetScriptItemTreesAsync<TItem>(
    /*WYSE*/IScript script,
    /*WYSE*/ISelect select,
    Converter<DbDataReader, TItem> converter,
    IReadOnlyList</*WYSE*/Mapping> mappings,
    TraceTree parentTraceTree)
    where TItem : IKeyEquatable
{
    var traceTree = parentTraceTree.AddChild();
    traceTree.RecordCodeMethodExecution(MethodBase.GetCurrentMethod());

    return await Get(
        script,
        reader => reader./*WYSE*/GetItemTrees(select, converter, mappings).ToList(),
        parentTraceTree);
}

Trace Entries: 2

...Framework.DataSpace.Access.SqlExecuter.Get:

private async Task<TItem> Get<TItem>(
    /*WYSE*/IExecutable executable,  /* A Select or a Script */
    Func<DbDataReader, TItem> getResult,
    TraceTree parentTraceTree)
{
    var traceTree = parentTraceTree.AddChild();
    traceTree.RecordCodeMethodExecution(MethodBase.GetCurrentMethod());

    using SqlConnection connection = _connectionFactory.Create();
    connection.Open();

    // Aliases and parameter names can be given custom values.
    // If omitted, unique identifer values are supplied
    // In the case of this Demo, these generated values are overwritten,
    // based on the current render settings, with more legible values.
    // WYSE supports several kinds of Resetting/renaming.
    ResetIdentifiers(executable);

    using SqlCommand command = connection.CreateCommand()
        // A WYSE Render context determines certain (overridable) render settings.
        // Here the context is SqlServer. More SQL flavors are supported.
        // A WYSE render builder is used for stringifying
        // the strongly typed SQL statements.
        /*WYSE*/.For(executable, _renderContextFactory.Create(), _createRenderBuilder);

    try
    {
        using var reader = await command.ExecuteReaderAsync();

        var result = getResult(reader);

        traceTree.RecordDbCommandExecutionSuccess(
            command.CommandText,
            command.Parameters.ToDictionary(),
            result);

        return result;
    }
    catch (Exception exception)
    {
        traceTree.RecordDbCommandExecutionFailure(
            command.CommandText,
            command.Parameters.ToDictionary(),
            exception);

        return default;
    }
}

Command Parameters:

@IsDeleted1	:	False
@IsDeleted2	:	False
@IsDeleted3	:	False

Command Text:

DECLARE @UserDivisionIds AS TABLE (
    [DivisionId] uniqueidentifier NOT NULL
);

INSERT INTO @UserDivisionIds
([DivisionId])
SELECT [56154_FrameworkDemoSystems_dbo_GetDescendants].[DivisionId] [DivisionId_1]
FROM [56154_FrameworkDemoSystems].[dbo].[GetDescendants]('11111111-1111-1111-1111-111111111111') [56154_FrameworkDemoSystems_dbo_GetDescendants];

SELECT
    [_CustomerSubselect].[56154_FrameworkDemoCustomers_demo_Customer_FullName] [_StringFunnel_1],
    [_CustomerSubselect].[56154_FrameworkDemoCustomers_demo_Customer_DivisionId] [_GuidFunnel_1],
    [_CustomerSubselect].[56154_FrameworkDemoCustomers_demo_Customer_HomeAddressId] [_GuidFunnel_2],
    [_CustomerSubselect].[56154_FrameworkDemoCustomers_demo_Customer_BillingAddressId] [_NullableGuidFunnel_1],
    [_CustomerSubselect].[56154_FrameworkDemoCustomers_demo_Customer_Id] [_GuidFunnel_3],
    [_CustomerSubselect].[56154_FrameworkDemoCustomers_demo_Customer_Created] [_DateTimeFunnel_1],
    [_CustomerSubselect].[56154_FrameworkDemoCustomers_demo_Customer_LastModified] [_DateTimeFunnel_2],
    [_CustomerSubselect].[56154_FrameworkDemoCustomers_demo_Customer_IsDeleted] [_BooleanFunnel_1],
    [_CustomerSubselect].[_Count] [_NumberFunnel],
    [_AddressSubselect_1].[56154_FrameworkDemoCustomers_demo_Address_Town_1] [_StringFunnel_2],
    [_AddressSubselect_1].[56154_FrameworkDemoCustomers_demo_Address_Id_1] [_GuidFunnel_4],
    [_AddressSubselect_1].[56154_FrameworkDemoCustomers_demo_Address_Created_1] [_DateTimeFunnel_3],
    [_AddressSubselect_1].[56154_FrameworkDemoCustomers_demo_Address_LastModified_1] [_DateTimeFunnel_4],
    [_AddressSubselect_1].[56154_FrameworkDemoCustomers_demo_Address_IsDeleted_1] [_BooleanFunnel_2],
    [_AddressSubselect_2].[56154_FrameworkDemoCustomers_demo_Address_Town_2] [_StringFunnel_3],
    [_AddressSubselect_2].[56154_FrameworkDemoCustomers_demo_Address_Id_2] [_GuidFunnel_5],
    [_AddressSubselect_2].[56154_FrameworkDemoCustomers_demo_Address_Created_2] [_DateTimeFunnel_5],
    [_AddressSubselect_2].[56154_FrameworkDemoCustomers_demo_Address_LastModified_2] [_DateTimeFunnel_6],
    [_AddressSubselect_2].[56154_FrameworkDemoCustomers_demo_Address_IsDeleted_2] [_BooleanFunnel_3]
FROM
(
    SELECT
        [56154_FrameworkDemoCustomers_demo_Customer].[FullName] [56154_FrameworkDemoCustomers_demo_Customer_FullName],
        [56154_FrameworkDemoCustomers_demo_Customer].[DivisionId] [56154_FrameworkDemoCustomers_demo_Customer_DivisionId],
        [56154_FrameworkDemoCustomers_demo_Customer].[HomeAddressId] [56154_FrameworkDemoCustomers_demo_Customer_HomeAddressId],
        [56154_FrameworkDemoCustomers_demo_Customer].[BillingAddressId] [56154_FrameworkDemoCustomers_demo_Customer_BillingAddressId],
        [56154_FrameworkDemoCustomers_demo_Customer].[Id] [56154_FrameworkDemoCustomers_demo_Customer_Id],
        [56154_FrameworkDemoCustomers_demo_Customer].[Created] [56154_FrameworkDemoCustomers_demo_Customer_Created],
        [56154_FrameworkDemoCustomers_demo_Customer].[LastModified] [56154_FrameworkDemoCustomers_demo_Customer_LastModified],
        [56154_FrameworkDemoCustomers_demo_Customer].[IsDeleted] [56154_FrameworkDemoCustomers_demo_Customer_IsDeleted],
        COUNT([56154_FrameworkDemoCustomers_demo_Customer].[Id]) OVER() [_Count]
    FROM [56154_FrameworkDemoCustomers].[demo].[Customer] [56154_FrameworkDemoCustomers_demo_Customer]
    INNER JOIN
    (
        SELECT [UserDivisionIds].[DivisionId] [UserDivisionIds_DivisionId]
        FROM @UserDivisionIds [UserDivisionIds]
    ) [_Select_2] ON ([56154_FrameworkDemoCustomers_demo_Customer].[DivisionId] = [_Select_2].[UserDivisionIds_DivisionId])
    WHERE ([56154_FrameworkDemoCustomers_demo_Customer].[IsDeleted] = @IsDeleted1)
) [_CustomerSubselect]
INNER JOIN
(
    SELECT
        [56154_FrameworkDemoCustomers_demo_Address_1].[Town] [56154_FrameworkDemoCustomers_demo_Address_Town_1],
        [56154_FrameworkDemoCustomers_demo_Address_1].[Id] [56154_FrameworkDemoCustomers_demo_Address_Id_1],
        [56154_FrameworkDemoCustomers_demo_Address_1].[Created] [56154_FrameworkDemoCustomers_demo_Address_Created_1],
        [56154_FrameworkDemoCustomers_demo_Address_1].[LastModified] [56154_FrameworkDemoCustomers_demo_Address_LastModified_1],
        [56154_FrameworkDemoCustomers_demo_Address_1].[IsDeleted] [56154_FrameworkDemoCustomers_demo_Address_IsDeleted_1]
    FROM [56154_FrameworkDemoCustomers].[demo].[Address] [56154_FrameworkDemoCustomers_demo_Address_1]
    WHERE ([56154_FrameworkDemoCustomers_demo_Address_1].[IsDeleted] = @IsDeleted2)
) [_AddressSubselect_1] ON ([_CustomerSubselect].[56154_FrameworkDemoCustomers_demo_Customer_HomeAddressId] = [_AddressSubselect_1].[56154_FrameworkDemoCustomers_demo_Address_Id_1])
LEFT JOIN
(
    SELECT
        [56154_FrameworkDemoCustomers_demo_Address_2].[Town] [56154_FrameworkDemoCustomers_demo_Address_Town_2],
        [56154_FrameworkDemoCustomers_demo_Address_2].[Id] [56154_FrameworkDemoCustomers_demo_Address_Id_2],
        [56154_FrameworkDemoCustomers_demo_Address_2].[Created] [56154_FrameworkDemoCustomers_demo_Address_Created_2],
        [56154_FrameworkDemoCustomers_demo_Address_2].[LastModified] [56154_FrameworkDemoCustomers_demo_Address_LastModified_2],
        [56154_FrameworkDemoCustomers_demo_Address_2].[IsDeleted] [56154_FrameworkDemoCustomers_demo_Address_IsDeleted_2]
    FROM [56154_FrameworkDemoCustomers].[demo].[Address] [56154_FrameworkDemoCustomers_demo_Address_2]
    WHERE ([56154_FrameworkDemoCustomers_demo_Address_2].[IsDeleted] = @IsDeleted3)
) [_AddressSubselect_2] ON ([_CustomerSubselect].[56154_FrameworkDemoCustomers_demo_Customer_BillingAddressId] = [_AddressSubselect_2].[56154_FrameworkDemoCustomers_demo_Address_Id_2])
ORDER BY 1 ASC
OFFSET 0 ROWS
FETCH NEXT 20 ROWS ONLY;

Command Parameters:

@IsDeleted1	:	False
@IsDeleted2	:	False
@IsDeleted3	:	False

Command Text:

DECLARE @UserDivisionIds AS TABLE (
    [DivisionId] uniqueidentifier NOT NULL
);

INSERT INTO @UserDivisionIds
([DivisionId])
SELECT [56154_FrameworkDemoSystems_dbo_GetDescendants].[DivisionId] [DivisionId_1]
FROM [56154_FrameworkDemoSystems].[dbo].[GetDescendants]('11111111-1111-1111-1111-111111111111') [56154_FrameworkDemoSystems_dbo_GetDescendants];

SELECT
    [_CustomerSubselect].[56154_FrameworkDemoCustomers_demo_Customer_FullName] [_StringFunnel_1],
    [_CustomerSubselect].[56154_FrameworkDemoCustomers_demo_Customer_DivisionId] [_GuidFunnel_1],
    [_CustomerSubselect].[56154_FrameworkDemoCustomers_demo_Customer_HomeAddressId] [_GuidFunnel_2],
    [_CustomerSubselect].[56154_FrameworkDemoCustomers_demo_Customer_BillingAddressId] [_NullableGuidFunnel_1],
    [_CustomerSubselect].[56154_FrameworkDemoCustomers_demo_Customer_Id] [_GuidFunnel_3],
    [_CustomerSubselect].[56154_FrameworkDemoCustomers_demo_Customer_Created] [_DateTimeFunnel_1],
    [_CustomerSubselect].[56154_FrameworkDemoCustomers_demo_Customer_LastModified] [_DateTimeFunnel_2],
    [_CustomerSubselect].[56154_FrameworkDemoCustomers_demo_Customer_IsDeleted] [_BooleanFunnel_1],
    [_CustomerSubselect].[_Count] [_NumberFunnel],
    [_AddressSubselect_1].[56154_FrameworkDemoCustomers_demo_Address_Town_1] [_StringFunnel_2],
    [_AddressSubselect_1].[56154_FrameworkDemoCustomers_demo_Address_Id_1] [_GuidFunnel_4],
    [_AddressSubselect_1].[56154_FrameworkDemoCustomers_demo_Address_Created_1] [_DateTimeFunnel_3],
    [_AddressSubselect_1].[56154_FrameworkDemoCustomers_demo_Address_LastModified_1] [_DateTimeFunnel_4],
    [_AddressSubselect_1].[56154_FrameworkDemoCustomers_demo_Address_IsDeleted_1] [_BooleanFunnel_2],
    [_AddressSubselect_2].[56154_FrameworkDemoCustomers_demo_Address_Town_2] [_StringFunnel_3],
    [_AddressSubselect_2].[56154_FrameworkDemoCustomers_demo_Address_Id_2] [_GuidFunnel_5],
    [_AddressSubselect_2].[56154_FrameworkDemoCustomers_demo_Address_Created_2] [_DateTimeFunnel_5],
    [_AddressSubselect_2].[56154_FrameworkDemoCustomers_demo_Address_LastModified_2] [_DateTimeFunnel_6],
    [_AddressSubselect_2].[56154_FrameworkDemoCustomers_demo_Address_IsDeleted_2] [_BooleanFunnel_3]
FROM
(
    SELECT
        [56154_FrameworkDemoCustomers_demo_Customer].[FullName] [56154_FrameworkDemoCustomers_demo_Customer_FullName],
        [56154_FrameworkDemoCustomers_demo_Customer].[DivisionId] [56154_FrameworkDemoCustomers_demo_Customer_DivisionId],
        [56154_FrameworkDemoCustomers_demo_Customer].[HomeAddressId] [56154_FrameworkDemoCustomers_demo_Customer_HomeAddressId],
        [56154_FrameworkDemoCustomers_demo_Customer].[BillingAddressId] [56154_FrameworkDemoCustomers_demo_Customer_BillingAddressId],
        [56154_FrameworkDemoCustomers_demo_Customer].[Id] [56154_FrameworkDemoCustomers_demo_Customer_Id],
        [56154_FrameworkDemoCustomers_demo_Customer].[Created] [56154_FrameworkDemoCustomers_demo_Customer_Created],
        [56154_FrameworkDemoCustomers_demo_Customer].[LastModified] [56154_FrameworkDemoCustomers_demo_Customer_LastModified],
        [56154_FrameworkDemoCustomers_demo_Customer].[IsDeleted] [56154_FrameworkDemoCustomers_demo_Customer_IsDeleted],
        COUNT([56154_FrameworkDemoCustomers_demo_Customer].[Id]) OVER() [_Count]
    FROM [56154_FrameworkDemoCustomers].[demo].[Customer] [56154_FrameworkDemoCustomers_demo_Customer]
    INNER JOIN
    (
        SELECT [UserDivisionIds].[DivisionId] [UserDivisionIds_DivisionId]
        FROM @UserDivisionIds [UserDivisionIds]
    ) [_Select_2] ON ([56154_FrameworkDemoCustomers_demo_Customer].[DivisionId] = [_Select_2].[UserDivisionIds_DivisionId])
    WHERE ([56154_FrameworkDemoCustomers_demo_Customer].[IsDeleted] = @IsDeleted1)
) [_CustomerSubselect]
INNER JOIN
(
    SELECT
        [56154_FrameworkDemoCustomers_demo_Address_1].[Town] [56154_FrameworkDemoCustomers_demo_Address_Town_1],
        [56154_FrameworkDemoCustomers_demo_Address_1].[Id] [56154_FrameworkDemoCustomers_demo_Address_Id_1],
        [56154_FrameworkDemoCustomers_demo_Address_1].[Created] [56154_FrameworkDemoCustomers_demo_Address_Created_1],
        [56154_FrameworkDemoCustomers_demo_Address_1].[LastModified] [56154_FrameworkDemoCustomers_demo_Address_LastModified_1],
        [56154_FrameworkDemoCustomers_demo_Address_1].[IsDeleted] [56154_FrameworkDemoCustomers_demo_Address_IsDeleted_1]
    FROM [56154_FrameworkDemoCustomers].[demo].[Address] [56154_FrameworkDemoCustomers_demo_Address_1]
    WHERE ([56154_FrameworkDemoCustomers_demo_Address_1].[IsDeleted] = @IsDeleted2)
) [_AddressSubselect_1] ON ([_CustomerSubselect].[56154_FrameworkDemoCustomers_demo_Customer_HomeAddressId] = [_AddressSubselect_1].[56154_FrameworkDemoCustomers_demo_Address_Id_1])
LEFT JOIN
(
    SELECT
        [56154_FrameworkDemoCustomers_demo_Address_2].[Town] [56154_FrameworkDemoCustomers_demo_Address_Town_2],
        [56154_FrameworkDemoCustomers_demo_Address_2].[Id] [56154_FrameworkDemoCustomers_demo_Address_Id_2],
        [56154_FrameworkDemoCustomers_demo_Address_2].[Created] [56154_FrameworkDemoCustomers_demo_Address_Created_2],
        [56154_FrameworkDemoCustomers_demo_Address_2].[LastModified] [56154_FrameworkDemoCustomers_demo_Address_LastModified_2],
        [56154_FrameworkDemoCustomers_demo_Address_2].[IsDeleted] [56154_FrameworkDemoCustomers_demo_Address_IsDeleted_2]
    FROM [56154_FrameworkDemoCustomers].[demo].[Address] [56154_FrameworkDemoCustomers_demo_Address_2]
    WHERE ([56154_FrameworkDemoCustomers_demo_Address_2].[IsDeleted] = @IsDeleted3)
) [_AddressSubselect_2] ON ([_CustomerSubselect].[56154_FrameworkDemoCustomers_demo_Customer_BillingAddressId] = [_AddressSubselect_2].[56154_FrameworkDemoCustomers_demo_Address_Id_2])
ORDER BY 1 ASC
OFFSET 0 ROWS
FETCH NEXT 20 ROWS ONLY;

Framework Demo

Advanced Language Features

Behind The Scenes Explanation

1. Full Code, Queries, And Results

2. Queries And Results Only

3. API Results

Minimize SQL:
Naming strategy: