XPO

March 2011 - Posts

  • 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.

LIVE CHAT

Chat is one of the many ways you can contact members of the DevExpress Team.
We are available Monday-Friday between 7:30am and 4:30pm Pacific Time.

If you need additional product information, write to us at info@devexpress.com or call us at +1 (818) 844-3383

FOLLOW US

DevExpress engineers feature-complete Presentation Controls, IDE Productivity Tools, Business Application Frameworks, and Reporting Systems for Visual Studio, along with high-performance HTML JS Mobile Frameworks for developers targeting iOS, Android and Windows Phone. Whether using WPF, ASP.NET, WinForms, HTML5 or Windows 10, DevExpress tools help you build and deliver your best in the shortest time possible.

Copyright © 1998-2017 Developer Express Inc.
All trademarks or registered trademarks are property of their respective owners