Optional filters

Optional filters is the mechanic to simplify query structure when some filters are not set. For example, we can map data structure to some class and to allow filter objects over some property of dictionary linked to main data structure. When filter applied this link to dictionary table is used, but when not we ineffectively rise query complexity.

To avoid unplanned query complexity XData has optional filter feature. Some filters marked as primary - when this filter has value, query has full form. When filter value has cleared or set to null, data source of filtered field (table or view) will be skipped when SQL query composed. When data source has been skipped then links between already skipped and other data sources are analyzed. And when found link marked as primary linked data source will also skipped. When base table of subquery has been skipped - all subquery will be skipped and subquery links analysis for primary links will be provided over outer query. Until no primary filters found.

This allows to compose complex query relations over multiple tables and subqueries that reflect on real query only when it really necessary.

Let's explain that on example...

public partial class TestOptional: IDataObject
{
    private static Expression<CustomMapping<TestOptional>> _mapping = (
        () => XDataMapping.CustomMapping<TestOptional>()
            .DataTable("T_TEST", "T", 
                x => x.SubqueryFilter(string.Empty, "A")
                    .SetOperation(FilterOperation.Exists)
            .Subquery("A", XDataMapping.GetStructure("H")
                .DataTable("T_TEST_OPT", "H")
                .DataTable("T_OPT", "P",
                    // Link between H and P table marked as Primary filter
                    x => x.Link("H", "opt_id").AsPrimary(),
                    // ConstFilter "OptCode" marked as Primary filter
                    x => x.ConstFilter("OptCode", "code", null).AsPrimary())
                .Select(x => new {
                    TestId = x.Field<long>("H", string.Empty)
                }), "TestId", DataGrouping.None, x => x.SubqueryLink("DocId"))
            .Column("TestId", x => x.Field<long>("T", string.Empty))
            .Map(x => new TestOptional {
                Name = x.Field<decimal>("T", string.Empty)
            }).SetBaseTable("T"));
}

When repository accessed like that...

dataScope.GetReposytory<TestOptional>().ToArray();

XData generate a simple SQL...

select T.test_id as T_test_id, T.name as T_name from T_TEST T

Because...

1. ConstFilter "OptCode" has null value and marked as primary => T_OPT table skipped
2. T_OPT table skipped and Link to T_TEST_OPT marked as primary => T_TEST_OPT table skipped
3. T_TEST_OPT table skipped and marked as base table => subquery is skipped and SubqueryFilter skipped as well, but because it not marked as primary => table T_TEST is not skipped

And when filter is filled...

dataScope.GetReposytory<TestOptional>()
    .SetFilterValue(new FilterName("OptCode"), "TEST").ToArray();

Now XData generate SQL with optional parts...

select T.test_id as T_test_id, T.name as T_name from T_TEST T 
where exists(select 1 
    from T_TEST_OPT H join T_OPT P on H.opt_id = P.opt_id 
    where P.code = "TEST")