Shh... IT in progress.

Something new for XPO v9.3, Free Joins

User Rating: / 2
PoorBest 

OK firstly I will admit I don’t know the full ins and outs of this new feature, however I can tell you a particular use case where it has saved my life recently.

For now I will keep this blog short or I will never get it posted. If you have questions don't hesitate to leave a comment.

In my case I have a Note object which basically is a “Note” and “Reminder” wrapped into one, this allows users to leave a note, with an optional reminder for a User which is either themselves or someone else to “follow” the note up.

So for an example here is the object

In my use case, I am using .NET Remoting over DSL connections so every byte transferred counts.

To help facilitate this all my lookups have a XPView datasource that only has 2 fields, the “DisplayMember” which is the Display for the Column and the ID “ValueMember”.

To allow this to happen I bind my LookupEdit EditValue to the <Field>!Key value which allows XPO to automatically run to the database and obtain my related object (based on my selected ID) and assign it to the property of my object (so in other words we only run to the database and collect the whole user object once it is selected, instead of loading a list of all users straight up). In the case of my Note object I bind it to the AssignedTo!Key property. All this works well, and with this Note object it has a Property called “AssignedTo” of User object type, so I wish to find out how many Notes are Assigned to a User.

The easiest way to do this is to have a “Association” on the “AssignedTo” Property to a XPCollection on the User object which we’ll call “Reminders”

So my Note class would have this property

<association ("USER-NOTES")>_
<noforeignkey ()> _
Public Property AssignedTo() As User
    Get
        Return _assignedTo
    End Get
    Set(ByVal Value As User)
        SetPropertyValue("AssignedTo", _assignedTo, Value)
    End Set
End Property 

and my User class will have this property

    <Association("User-Notes")> _
    Public ReadOnly Property Notes() As XPCollection(Of Note)
        Get
            Return GetCollection(Of Note)("Notes")
        End Get
    End Property

So now if I want a count of Reminders for each User in my XPView all I have to do is add this property.

New ViewProperty("Notes", SortDirection.None, New AggregateOperand("Notes", Nothing, Aggregate.Count, Nothing), False, True)

All works well, except for one issue. When I create a new note and assign it to a particular user (keeping in mind my database has been in use for just on 10 years) some users have up to 5000 notes in the database. When the new Note is “Assigned” to the User, XPO will “add” the new note to the associated XPCollection which means XPO will have to load the Association “Reminders” which means it loads 5000 notes from the system. This is not very helpful when bandwidth is a major concern.

In comes the JoinOperand, I remove my “Reminders” collection property on my User and remove the Association. Now if I want a count of Reminders all I have to do is

New ViewProperty("Notes", SortDirection.None, New JoinOperand("Note", New BinaryOperator(New OperandProperty("^.This"), New OperandProperty("AssignedTo"), BinaryOperatorType.Equal), Aggregate.Count, Nothing), False, True)

So I still acheive the same outcome however there isn’t a “Association” between the Note and User class meaning that when I “Assign” the note to a user, XPO will not have to do any additional loading (unlike the previous method).

As I mentioned at the start, Free Joins most likely have many benefits, this is just one particular benefit which saves my user from up to a 2 minute load when they create a note.

Again I apologise for the “basic” blog post, however I figure it is probably better to get this information out in some form rather than none at all.

Background: eXpress Persistent Objects (XPO) is a Object Relation Mapping (ORM) solution provided by DevExpress (DX), XPO is a Object->Database model (ie. You create strongly typed objects to which XPO will handle the persistance to a database store, however there is a wizard to generate your Objects from Database [Databsae->Object]). XPO supports persisting to 16 databases out of the box (although you can easily extend their DataStore classes to gain support for your particular database). See here for more details http://www.devexpress.com/Help/?document=XPO