Advanced queries

This has been posted before to the XPO beta newsgroup, but now that the beta is over, I thought it might be interesting to all XPO users. I updated everything to the current state of things, so the description should match the current release 6.1.

XPO 6.1 is able to perform advanced queries, enabling the following features:

• Filter criteria can use several fields at once and perform calculations
• Database queries can be performed that don't create objects on the client
• Aggregates can be queried directly from the database backend

Filter criteria

Assuming this business object:

  public class OrderEntry : XPObject {
    public OrderEntry() { }
    public OrderEntry(Session session) : base(session) { }

    public OrderEntry(string articleName, decimal articlePrice, int articleCount, int rebatePercent) {
      this.articleName = articleName;
      this.articlePrice = articlePrice;
      this.articleCount = articleCount;
      this.rebatePercent = rebatePercent;
    }

    private string articleName;
    public string ArticleName {
      get { return articleName; }
      set { articleName = value; }
    }

    private decimal articlePrice;
    public decimal ArticlePrice {
      get { return articlePrice; }
      set { articlePrice = value; }
    }

    private int articleCount;
    public int ArticleCount {
      get { return articleCount; }
      set { articleCount = value; }
    }

    private int rebatePercent;
    public int RebatePercent {
      get { return rebatePercent;}
      set { rebatePercent = value; }
    }
  }

It is possible to create XPCollection instances like these:

  new XPCollection(typeof(OrderEntry), 
    CriteriaOperator.Parse("ArticlePrice * ArticleCount > 400"));

  new XPCollection(typeof(OrderEntry),
    CriteriaOperator.Parse("iif(RebatePercent == 0, ArticlePrice, ArticlePrice - (ArticlePrice * RebatePercent / 100)) > 50"));

Querying from the backend without creating objects

The new XPView class can query information from the database and make it available in a number of "fields", which have to be defined for the view beforehand.

This code snippet would construct a view with a calculated field:

  XPView view = new XPView(session, typeof(OrderEntry));
  view.AddProperty("RealPrice", 
    "iif(RebatePercent == 0, ArticlePrice, ArticlePrice - (ArticlePrice * RebatePercent / 100))");

XPView implements IBindingList, so it can be bound to visual components just like an XPCollection can. The most important difference is that the XPView's result set doesn't contain objects of the given type, but instead a dedicated object type called ViewRecord. From code, the field of the view above could be accessed like this:

  foreach(ViewRecord record in view)
    Console.WriteLine(record["RealPrice"]);

Even though objects are not constructed automatically by the XPView, it is possible to access the corresponding object for a ViewRecord. It is important that the key property of the class that is being queried (the property with the KeyAttribute, called “Oid” for the standard XPObject base class) must be included in the field list of the XPView, if you want to be able to query the object directly from the ViewRecord.

  view.AddProperty("Oid");
  foreach (ViewRecord record in view) {
    OrderEntry oe = (OrderEntry) record.GetObject();
    ... // now do something with oe
  }

If a filter criteria is used to access one of the fields of an XPView, the name of the field must be used (instead of the field value expression being repeated). Like here:

  view.Filter = CriteriaOperator.Parse("RealPrice > 50");

Querying aggregate values

The properties that are added to the XPView support aggregates directly. Assume the following class in addition to the OrderEntry, and the corresponding association property in the OrderEntry class (see details about associations here).

  public class Order: XPObject {
    ...

    private DateTime orderDate;
    public DateTime OrderDate {
      get { return orderDate; }
      set { orderDate = value; }
    }

    [Association("Order-Entries"), Aggregated]
    public XPCollection<OrderEntry> Entries {
      get { return GetCollection<OrderEntry>("Entries"); }
    }

    ...
  }

This would allow for the following view to be used to find out the total values of all orders:

  XPView view = new XPView(session, typeof(Order));
  view.AddProperty("OrderDate");
  view.AddProperty("OrderValue", 
    "Entries.Sum(iif(RebatePercent == 0, ArticlePrice, ArticlePrice - (ArticlePrice * RebatePercent / 100)) * ArticleCount)");
  foreach (ViewRecord record in view)
    Console.WriteLine("Order: {0}, total value: {1}", record["OrderDate"], record["OrderValue"]);

Top-level aggregates can also be queried, but in this case a method of the Session object must be used:

  int numOrderEntry = (int)
    session.Evaluate<OrderEntry>(CriteriaOperator.Parse("Count()"), null);

Free DevExpress Products - Get Your Copy Today