Computing Aggregate Values Efficiently with Dynamic Entities

Answer:

A sproc is certainly the easy route, but a few words of caution.

(1)     You probably want to make sure you call this with DataSourceOnly;

(2)     You want to stick some kind of guard in the property so that it is invoked only once per ... microsecond? ... and cached in your business object otherwise; remember that .NET calls properties many times in a row for totally spurious reasons and you don't want to keep making trips to the database each time;

(3)     The balance will be based on data in the database, not data in the client - which means there could be a serious disconnect between what the user sees on screen - what she adds up with her hand calculator - and the results returned from the property; if there is any chance of a discrepancy, you have to figure out how to deal with it.

(4)     This approach assumes that the client is continuously connected to the database.

For all of these reasons, I avoid aggregates unless I have a proven performance issue; and then I take steps to deal with the problems mentioned.

Another approach is to use dynamic entities.

Dynamic entity types work great for the following reasons:

• You don’t know the structure of the class until runtime.
• You want to generate aggregate statistics from a table. You may not know which column is returned until runtime, e.g. the user picks which statistic to compute.
• You want to retrieve the minimum number of columns necessary to present the result in a grid. Again, you may not know which columns to retrieve until runtime based on user’s selection.

Once defined, dynamic entity type instances can be queried, created, modified, and deleted just like regular DevForce entities.

In the example in the attachment, the Employee has a custom property called TotalOrderRevenue with computes the total revenue for the Employee’s orders. Key points are highlighted below.

  In Employee.cs:

    /// <summary>

    /// Total revenue for this Employee's orders

    /// </summary>

    public double TotalOrderRevenue {

      // Calculated property using a PassThruQuery and a dynamic entity type.

      get {

        // Can use guard clause here to limit calculation.

        // Example: Recalculate if 10 seconds has elapsed since last calculation.

        if ((DateTime.Now - mLastPolledTime) >= new TimeSpan(0, 0, 10)) {

          mTotalRevenue = CalculateTotalRevenue();

          mLastPolledTime = DateTime.Now;

        }

        return mTotalRevenue;

      }

    }

    private double CalculateTotalRevenue() {

      // Create a Dynamic Entity Type without a primary key.

      // Note: msType is only created once for this Employee type since this same dynamic type

      // can be used for all subsequent calculations of an Employee's total order revenue.

      if (msType == null) {

        msType = DynamicEntityTypeBuilder.CreateType("TotalOrderRevenue", "Default");

      }

      // Construct PassThruQuery specifying the SUM aggregate.

      string sqlStmt = String.Format("select SUM(OrderDetail.Quantity * OrderDetail.UnitPrice * OrderDetail.Discount) " +

        "from Employee, OrderSummary, OrderDetail " +

        "where Employee.Id = {0} and " +

        "Employee.Id = OrderSummary.EmployeeId and " +

        "OrderSummary.Id = OrderDetail.OrderSummaryId", this.Id);

      IEntityQuery query = new PassthruRdbQuery(msType, sqlStmt);

      // Retrieve list of entities based on the query.

      IList<DynamicEntity> entities = this.PersistenceManager.GetEntities<DynamicEntity>(query);

      // Result is in the first entity's first column.

      return ( (entities[0][0] is System.DBNull) ? 0 : (double)entities[0][0] );

    }

(1)     In the Getter for Employee.TotalOrderRevenue, I added a “guard clause” (for illustrative purposes) if you are concerned about performance. It will limit the calculation to once every 10 seconds.

(2)     The dynamic entity type, msType, is created once per Employee class.

You can create dynamic types with or without a primary key. Dynamic entity types with a primary key operate almost exactly like a statically defined DevForce entity type. Query results are merged into PM according to the query’s MergeStrategy. In the example above, I created a type without a primary key. Results will not be merged. The PM will first discard all prior instances of the type and then fill the cache with the query results. Because we only care about the computed value for a given Employee, this is the way to go.

You can define the schema for the dynamic entity type in 2 ways:

1. By declaring it programmatically. See the Developer’s Guide for examples on the 5 overloads of the DynamicEntityTypeBuilder.CreateType method.
2. By defining and executing a query for the type (shown here).

1.        A PassThruQuery is then constructed containing the SUM aggregate.

2.        The list of entities is retrieved based on the query.

3.        The result is returned in the first record’s first column.

4.        You can now bind to Employee.TotalOrderRevenue just as you would any other custom property.

For more details on dynamic entities, please reference the chapter titled “Dynamic Enitity Types” in the Developer’s Guide.