Derived tables and dynamic relations

Info

This section is about a feature in the Low-level API. If you use Linq to LLBLGen or QuerySpec (recommended) you're derived tables / dynamic relations automatically. The code in this section describes the usage of a derived table and a dynamic relation in the lower-level API.

One of the more advanced features of SQL is that you can specify a query as the FROM clause of a SELECT statement, or as one side of a JOIN statement. Also in other areas it could be helpful if you could refer to a set created by a new SELECT statement inside a query. This feature is called Derived Table. Below is a simple example:

SELECT o.*
FROM
(
    SELECT  CustomerId 
    FROM    Customers
    WHERE   Country = @country
) c INNER JOIN Orders o ON
    c.CustomerId = o.CustomerId

The query has a JOIN between a table and another query as the FROM clause. In normal cases you wouldn't need this feature that much, as most of the time you can rewrite a query with a Derived Table into a query which doesn't use a Derived Table. The above query can for example be rewritten in such a way that the WHERE clause becomes part of the ON clause in the INNER JOIN.

However, in cases where it can be very efficient to have a query as one side of a JOIN or as source in the FROM clause, it is useful that a feature like Derived Table support is available. LLBLGen Pro supports Derived Tables in its native (low-level) querying API through the DerivedTableDefinition. This class can be used together with the DynamicRelation class to build a query which uses a Derived Table.

The LLBLGen Pro querying API uses pre-fabricated relation objects to specify joins. These relations are defined in the LLBLGen Pro designer, either through reverse engineering of the database schema or defined by the user. Sometimes it's necessary to specify a relation between two entities which aren't directly related in the model.

To ease the creation of a relation, the DynamicRelation class is generated into the RelationClasses namespace. It allows joins between DerivedTableDefinition instances and other DerivedTableDefinition instances, entities, or two entities over a predicate. DynamicRelation instances are also used to wrap a DerivedTableDefinition instance so it's usable inside a query.

This section will describe both in more detail and will give examples how they can be used. Users of Linq to LLBLGen Pro will use both classes indirectly.

Info

Firebird 1.x and SqlServer CE 3.1 and earlier don't support Derived Tables in SQL. If you're using one of these databases, please upgrade to a later version of Firebird (2.x) or SqlServer CE (3.5).

DerivedTableDefinition

A Derived Table instance in LLBLGen Pro's query API can be created by creating a DerivedTableDefinition object. There are various constructors defined in this class. It always requires an enumerable object of entity fields and an alias.

The list of entity fields are the fields which form the SELECT clause of the query formed by the Derived Table. The alias specified in the constructor of the DerivedTableDefinition class is the alias of the query formed by the Derived Table. In the example query in the preface section above, the Derived Table in the JOIN clause is aliased as c. This is the alias specified in the DerivedTableDefinition constructor.

The DerivedTableDefinition is a full query definition, so you can specify predicates, relations, group by clauses, sort clauses and a limiter, as well a flag if the query should return distinct values or not. This also allows nesting of DerivedTableDefinition instances through DynamicRelation objects inside the relation collection passed to the constructor of DerivedTableDefinition.

Targeting a DerivedTableDefinition for an entity fetch

When fetching entities which data is coming from a Derived Table, the target of the fetch has to be set to the DerivedTableDefinition, as by default, entity fields which are created to form the query for an entity fetch target the table/view field they're mapped on.

To be able to make LLBLGen Pro target the DerivedTableDefinition instead, the RelationCollection which contains the DynamicRelation with the DerivedTableDefinition has a property called SelectListAlias. Once this property is set to the alias specified in the constructor call of the DerivedTableDefinition, the entity fields in query which form the fields which are read for filling the entities will target the DerivedTableDefinition aliased with the alias set as SelectListAlias.

In the examples in the next section the usage of SelectListAlias is illustrated.

Important!

Fetches of entities in an inheritance hierarchy by using a Derived Table as the source to fetch the data from is discouraged if the Derived Table is specified manually. This is because the framework needs to formulate the query in a certain way to be able to determine the entity type of the rows returned.

If a derived table is used, this isn't necessarily the case as the Derived Table might have a completely different resultset, which means that the resulting resultset isn't necessarily the resultset usable to fetch entities in an inheritance hierarchy.

Using a DerivedTableDefinition

To use a DerivedTableDefinition, it has to be wrapped inside a DynamicRelation, which is discussed more in detail later in this section. Below are two examples of how the DerivedTableDefinition can be used in your code. For convenience, the Linq to LLBLGen Pro and QuerySpec query equivalents are given as well.

Example 1. Query mentioned in Preface

SelfServicing

The low-level API query is given with equivalents in Linq and QuerySpec.

Low-level API, C#Linq, C#QuerySpec, C#

// First define the fields of the derived table select
var dtFields = new ResultsetFields(1);
dtFields.DefineField(CustomerFields.CustomerId, 0);
var dtDefinition = new DerivedTableDefinition(
               dtFields, "c", (CustomerFields.Country=="USA"));

// specify the relation which is a dynamic relation. 
var relation = new DynamicRelation(dtDefinition, JoinHint.Inner, 
                        EntityType.OrderEntity, "o", 
                       (new EntityField(CustomerFieldIndex.CustomerId.ToString(), "c", typeof(string))
                            .Equal(OrderFields.CustomerId.SetObjectAlias("o"))));

var relations = new RelationCollection();
relations.Add(relation);
relations.SelectListAlias = "o";  // entities should target the aliased order table. 

// fetch the data
var orders = new OrderCollection();
orders.GetMulti(null, relations);

var metaData = new LinqMetaData();
var q = from o in metaData.Order
        join cu in (from c in metaData.Customer 
                    where c.Country=="USA"
                    select c.CustomerId) on o.CustomerId equals cu
        select o;

var qf = new QueryFactory();
var q = qf.Order
            .From(QueryTarget
                .InnerJoin(qf.Customer.As("cu")
                        .Where(CustomerFields.Country == "USA")
                        .Select(CustomerFields.CustomerId))
                .On(OrderFields.CustomerId == CustomerFields.CustomerId.Source("cu")));
var orders = new OrderCollection();
orders.GetMulti(q);

Adapter

The low-level API query is given with equivalents in Linq and QuerySpec.

Low-level API, C#Linq equivalent, C#QuerySpec, C#

// First define the fields of the derived table select
var dtFields = new ResultsetFields(1);
dtFields.DefineField(CustomerFields.CustomerId, 0);
var dtDefinition = new DerivedTableDefinition(
               dtFields, "c", (CustomerFields.Country=="USA"));

// specify the relation which is a dynamic relation. 
var relation = new DynamicRelation(dtDefinition, JoinHint.Inner, 
                        EntityType.OrderEntity, "o", 
                       (new EntityField2(CustomerFieldIndex.CustomerId.ToString(), "c", typeof(string))
                            .Equal(OrderFields.CustomerId.SetObjectAlias("o"))));

var bucket = new RelationPredicateBucket();
bucket.Relations.Add(relation);
bucket.SelectListAlias = "o";  // entities should target the aliased order table. 

// fetch the data
var orders = new EntityCollection<OrderEntity>();
using(DataAccessAdapter adapter = new DataAccessAdapter())
{
    adapter.FetchEntityCollection(orders, bucket);
}

var metaData = new LinqMetaData(adapter);
var q = from o in metaData.Order
        join cu in (from c in metaData.Customer 
                    where c.Country=="USA"
                    select c.CustomerId) on o.CustomerId equals cu
        select o;

var qf = new QueryFactory();
var q = qf.Order
    .From(QueryTarget
        .InnerJoin(qf.Customer.As("cu")
                .Where(CustomerFields.Country == "USA")
                .Select(CustomerFields.CustomerId))
        .On(OrderFields.CustomerId == CustomerFields.CustomerId.Source("cu")));
var orders = adapter.FetchQuery(q);

Example 2: Get all orders with a total higher than a given limit

The query looks like this:

SELECT O.*
FROM 
(
    SELECT OrderID, SUM(Quantity * UnitPrice) As Total
    FROM [Order Details]
    Group By OrderID
) As OrderDetailTotals 
    INNER JOIN Orders O On OrderDetailTotals.OrderID = O.OrderID
WHERE OrderDetailTotals.Total > @limit

SelfServicing

The low-level API query is given with equivalents in Linq and QuerySpec.

Low-level API, C#Linq, C#QuerySpec, C#

// first specify the elements in the derived table select (which is a dyn. list)
var dtFields = new ResultsetFields(2);
dtFields.DefineField(OrderDetailFields.OrderId, 0);
dtFields.DefineField(new EntityField("Total", 
             (OrderDetailFields.Quantity * OrderDetailFields.UnitPrice), AggregateFunction.Sum), 1);
var dtGroupBy = new GroupByCollection(dtFields[0]);
var dtDefinition = new DerivedTableDefinition(dtFields, "OrderDetailTotals", null, dtGroupBy);

// then specify the relation. 
// derivedtable spec, join type, end entity type, alias first element, alias end element, on clause filter
var relation = new DynamicRelation(dtDefinition, JoinHint.Inner, 
                    EntityType.OrderEntity, "O",
                   (new EntityField(OrderDetailFieldIndex.OrderId.ToString(), "OrderDetailTotals", typeof(int))
                        .Equal(OrderFields.OrderId.SetObjectAlias("O"))));

// then specify the rest of the query elements
var relations = new RelationCollection();
relations.Add(relation);
relations.SelectListAlias = "O";
var filter = new EntityField("Total", "OrderDetailTotals", typeof(int)).GreaterThan(5000);

// then fetch the data
var orders = new OrderCollection();
orders.GetMulti(filter, relations);

var metaData = new LinqMetaData();
var q = from o in metaData.Order
        join ods in
            (from od in metaData.OrderDetail
             group od by od.OrderId into g
             select new { OrderId = g.Key, Total = g.Sum(x => x.Quantity * x.UnitPrice) })
        on o.OrderId equals ods.OrderId
        where ods.Total > 5000
        select o;

var qf = new QueryFactory();
var q = qf.Order
        .From(QueryTarget
            .InnerJoin(qf.Create("ods")
                    .Select(OrderDetailFields.OrderId,
                           (OrderDetailFields.Quantity * OrderDetailFields.UnitPrice)
                            .Sum().As("Total"))
                    .GroupBy(OrderDetailFields.OrderId))
            .On(OrderFields.OrderId.Equal(qf.Field("OrderId").Source("ods"))))
        .Where(qf.Field("Total").Source("ods") > 5000);

var orders = new OrderCollection();
orders.GetMulti(q);

Adapter

The low-level API query is given with equivalents in Linq and QuerySpec.

Low-level API, C#Linq equivalent, C#QuerySpec, C#

// first specify the elements in the derived table select (which is a dyn. list)
var dtFields = new ResultsetFields(2);
dtFields.DefineField(OrderDetailFields.OrderId, 0);
dtFields.DefineField(new EntityField2("Total", 
             (OrderDetailFields.Quantity * OrderDetailFields.UnitPrice), AggregateFunction.Sum), 1);
var dtGroupBy = new GroupByCollection(dtFields[0]);
var dtDefinition = new DerivedTableDefinition(dtFields, "OrderDetailTotals", null, dtGroupBy);

// then specify the relation. 
// derivedtable spec, join type, end entity type, alias first element, alias end element, on clause filter
var relation = new DynamicRelation(dtDefinition, JoinHint.Inner, 
                    EntityType.OrderEntity, "O",
                   (new EntityField2(OrderDetailFieldIndex.OrderId.ToString(), "OrderDetailTotals", typeof(int))
                        .Equal(OrderFields.OrderId.SetObjectAlias("O"))));

// then specify the rest of the query elements
var filter = new RelationPredicateBucket();
filter.Relations.Add(relation);
filter.SelectListAlias = "O";
filter.PredicateExpression.Add(new EntityField2("Total", "OrderDetailTotals", typeof(int)).GreaterThan(5000));

// then fetch the data
var orders = new EntityCollection<OrderEntity>();
using(var adapter = new DataAccessAdapter())
{
    adapter.FetchEntityCollection(orders, filter);
}

var metaData = new LinqMetaData(adapter);
var q = from o in metaData.Order
        join ods in
            (from od in metaData.OrderDetail
             group od by od.OrderId into g
             select new { OrderId = g.Key, Total = g.Sum(x => x.Quantity * x.UnitPrice) })
        on o.OrderId equals ods.OrderId
        where ods.Total > 5000
        select o;

var qf = new QueryFactory();
var q = qf.Order
    .From(QueryTarget
        .InnerJoin(qf.Create("ods")
                .Select(OrderDetailFields.OrderId,
                       (OrderDetailFields.Quantity * OrderDetailFields.UnitPrice)
                        .Sum().As("Total"))
                .GroupBy(OrderDetailFields.OrderId))
        .On(OrderFields.OrderId == qf.Field("OrderId").Source("ods")))
    .Where(qf.Field("Total").Source("ods").GreaterThan(5000));

var orders = adapter.FetchQuery(q);

DynamicRelation

A DynamicRelation instance can be useful in the situation where you want to join two elements over a non-existing relation, e.g. you want to create a join between two entities by specifying a predicate expression. In the previous section the DynamicRelation has been introduced as a wrapping element for DerivedTableDefinitions and also the class to use when you want to join a derived table to an entity.

Joining two entities over a predicate is one line of code with the DynamicRelation class: when using the constructor overload which accepts two EntityType instances, you can specify which two entities should be joined together, the aliases for both sides (not required in all situations, only if you join the same entity to itself) and the predicate which should be used as ON clause.

If you want to combine existing generated relation objects with DynamicRelation instances, you can: just specify the DynamicRelation's relation as you would otherwise and add it to the RelationCollection at the right spot (i.e. after the relation it joins to).