Using Object Expression

Thinking about the SQL statements below:

SELECT [Orders].*

FROM [Orders]

WHERE

  [Orders].[CustomerID] IN

    (

      SELECT [Customers].[CustomerID]

      FROM [Customers]

      WHERE

        [Customers].[ContactTitle] = 'Owner'

    ) AND

  NOT [Orders].[OrderID] IN

      (

        SELECT [Order Details].[OrderID]

        FROM [Order Details]

        WHERE

          [Order Details].[ProductID] IN

            (

              SELECT [Products].[ProductID]

              FROM [Products]

              WHERE

                [Products].[CategoryID] IN

                  (

                    SELECT [Categories].[CategoryID]

                    FROM [Categories]

                    WHERE

                      [Categories].[CategoryName] = 'Seafood'

                  )

            )

      )

Usually, it can be compiled into the following OQL:

SelectQuery qryOrder = OQL

  .SelectFrom(NW.Order)

  .Where(

    NW.Order.CustomerID.In(OQL

      .Select(NW.Customer.CustomerID)

      .From(NW.Customer)

      .Where(NW.Customer.ContactTitle == "Owner")

    ) && ! NW.Order.OrderID.In(OQL

      .Select(NW.OrderDetail.OrderID)

      .From(NW.OrderDetail)

      .Where(

        NW.OrderDetail.ProductID.In(OQL

          .Select(NW.Product.ProductID)

          .From(NW.Product)

          .Where(

            NW.Product.CategoryID.In(OQL

              .Select(NW.Category.CategoryID)

              .From(NW.Category)

              .Where(NW.Category.CategoryName == "Seafood")))))));

The previous OQL is not legible. We can refactor them as follows:

SelectQuery qryCategory = OQL

  .Select(NW.Category.CategoryID)

  .From(NW.Category)

  .Where(NW.Category.CategoryName == "Seafood");

 

SelectQuery qryProduct = OQL

  .Select(NW.Product.ProductID)

  .From(NW.Product)

  .Where(NW.Product.CategoryID.In(qryCategory));

 

SelectQuery qryOrderDetail = OQL

  .Select(NW.OrderDetail.OrderID)

  .From(NW.OrderDetail)

  .Where(NW.OrderDetail.ProductID.In(qryProduct));

 

SelectQuery qryCustomer = OQL

  .Select(NW.Customer.CustomerID)

  .From(NW.Customer)

  .Where(NW.Customer.ContactTitle == "Owner");

 

SelectQuery qryOrder = OQL

  .SelectFrom(NW.Order)

  .Where(

    NW.Order.CustomerID.In(qryCustomer)

    && ! NW.Order.OrderID.In(qryOrderDetail));

After analyzing the above OQL, we can see that the query conditions of all the sub-queries simply consist of the constraint between tables. Therefore, the above OQL can be converted to more concise object expressions.

SelectQuery qryOrder = OQL

  .SelectWhere(

    NW.Order ==

      (NW.Customer.ContactTitle == "Owner")

    && NW.Order !=

      (NW.OrderDetail ==

        (NW.Product == (NW.Category.CategoryName == "Seafood"))

      )

    );

Related Topics

Multi-table Select