Sql style joins

Previous I have shown how dynamic data can combine collections using logical And, Or, Xor and Except functions (see Composite Collections). More recently there have also been SQL style joins introduced which allow the combining of objects from multiple observable collection sources using SQL join semantics.

First, create 2 observable collections like this

var person  = new SourceCache<Person, int>(p => p.Id);
var address = new SourceCache<Address, int>(a => a.Id);

These collections are made observable by calling the Connect() method

var personObservable =  person.Connect();
var addressObservable =  address.Connect();

The results of the underlying data can be combined as follows

var personWithAddress = personObservable.InnerJoin(
    addressObservable,
    a => a.PersonId,
    (personKey, person, address) =>
    {
        return new PersonWithAddress(person, address);
    })

As data changes in Person or in Address a PersonWithAddress object will be produced when there is a matching record in both data sources.

There are also operators for FullJoin, LeftJoin and RightJoin, all of these follow the same the rules as SQL joins. These operators produces one-to-one data mappings. In the future there will also be variants of these operators which produce one-to-many mappings.

Breaking Changes in release 7.2.1 and onwards

RightJoin

before the RightJoin was returning IObservable<IChangeSet<TDestination, TLeftKey>>. This changed to IObservable<IChangeSet<TDestination, TRightKey>> so that it correctly represents the right set.

InnerJoin

before the InnerJoin was returning IObservable<IChangeSet<TDestination, TLeftKey>>. This changed to IObservable<IChangeSet<TDestination, (TLeftKey leftKey, TRightKey rightKey)>>. This was necessary to correctly represent the inner set of left and right. Continuing the above Example:

person.Add(new Person{Id=0, ...});
address.Add(new Address{Id=0, PersonId=0, ...});
address.Add(new Address{Id=1, PersonId=0, ...});

will now result in personWithAddress containing the keys: (0,0) and (0,1) to uniquely represent the two addresses belonging to one person.

To get back to the previous behavior:

var personWithAddress = personObservable.InnerJoin(...).ChangeKey(key=>key.leftKey);