XPO – 11.1 Sneak Peek – Support for the OUTER APPLY operator in SQL queries

We have some good news for our XPO users. In v2011 vol1 we have improved our sql-generator by supporting the OUTER APPLY operator. This SQL operator enables you to create an aggregate’s sub queries outside the WHERE clause and the SELECT expressions. For instance, it provides the capability to perform nested aggregates and aggregates in the group by list of the GROUP BY clause.

How this support is useful for you? It is useful because Microsoft SQL Server and SQL Server Compact are limited  in performing aggregates in these scenarios. For instance, the following queries will throw an exception without the OUTER APPLY operator:

1) Nested aggregate:

select N0.Oid, (select max(N1.Date) from [Orders] N1

where N1.EmployeeId = N0.EmployeeId

AND (select count(*) from [Orders] N2 where N2.CustomerId = N1.CustomerId) <= 2)

from [Employees] N0

2) Aggregate in the group by list of a GROUP BY clause:

select (select count(*) as Res from "dbo"."FreeClass" N2

where (N0."Data" = N2."Data"))

from "dbo"."FreeClassSub" N0

group by (select count(*) as Res from "dbo"."FreeClass" N3

where (N0."Data" = N3."Data"))

However, if we modify the above queries to use the OUTER APPLY operator, they will perform correctly:

1) Nested aggregate:

select N0.Oid, OA0.Res

from [Employees] N0

outer apply

(select max(N1.Date) as Res from [Orders] N1

outer apply

(select count(*) as Res from [Orders] N2 where N2.CustomerId = N1.CustomerId) OA1

where N1.EmployeeId = N0.EmployeeId AND OA1.Res <= 2)) OA0

2) Aggregate in the group by list of a GROUP BY clause:

select OA0.Res from "dbo"."FreeClassSub" N0

outer apply

(select count(*) as Res from "dbo"."FreeClass" N3 where (N0."Data" = N3."Data")) OA0

group by OA0.Res

The OUTER APPLY operator is supported by Microsoft SQL Server and SQL Server Compact. So starting from version 11.1, criteria and group properties (like “[Orders][ [Customers][City = ‘London’].Count() > 10 ].Min(ShippedDate)”) can be performed on those DBMS without any problems. BTW, I recently saw one more practical example from a customer, who incurred the limit of the queries described above. I will post it here, just in case you have similar structures in your projects. So, here are his business classes:

public class ParentObject : XPObject {
    ...
    [Aggregated]

    [Association("Parent-Children")]

    public XPCollection<ChildObject> ChildObjects { ... }

    [PersistentAlias("ChildObjects[].Max(LastChange)")]

    public DateTime LastChangeOfChildObjects {

        get { return (DateTime)EvaluateAlias("LastChangeOfChildObjects"); }

    }

    ...

}

public class ChildObject : XPObject {
    ...

    [Association("Parent-Children")]

    public ParentObject Parent { ... }

    public DateTime LastChange { ... }

    ...

}

The ParentObject was bound to a grid control working in the server mode. If you try to group by the LastChangeOfChildObjects column (take special note of the expression in its PersistentAlias attribute) in 10.2, it will not work, because of the described limitation. There are of course many other examples.

Finally, I should note that support for the OUTER APPLY operator is an internal improvement, and that means that XPO users will not require any additional actions to turn it on.

Free DevExpress Products - Get Your Copy Today