Joins

Implementing Joins with FluentCRM

What is a Join?

It's worth taking a moment to think about what we mean by a join in CRM.

A join is a connection between the records of two entities.
A join can be characterized as 1:N, N:1 or N:N. A join can be an inner or an outer join. We can chain multiple joins together to involve three or more entities.

Lets look at a join in the context of some familiar entities: Account and Contact.

Contacts generally have a parent account (not always, but we'll deal with that later). Accounts may have multiple contacts associated with them.

We say that accounts have a 1:N relationship with the Contacts. For each account there may be one or more contact records. This means that if we join a single account to related contact records, we may get zero or more contact records back.

From the contact perspective, each contact may be related to only a single account, but many contacts can be related to the same account, so we would say that Contacts have a N:1 relationship with Accounts.

If you look at entities such as Security Roles and Users then each user may have seceral security roles, and conversely each security role can be associated with many users. This is a N:N relationship.
N:N relationships are actually implemented via a separate join table - so might be better characterised as an N:1:N relationship.

Fluent CRM approach to Joins

I went through several iterations of trying to find a syntax for Joins that was a good fit with FluentCRM.
The final approach that has been developed based on what works for my purposes..

The basis of the FluentCRM join, is that you start with one entity (such as Account) and then use a typed generic function (Join<>()) to follow the relationship through to a related entity. For example, the link from Account to Contact, looks something like this: -

FluentAccount.Account(contactId)
    .UseAttribute((string account) => dto.name=account, "name") /* #1 */
    .Join<FluentContact>( 
        c => c.UseAttribute<string>( contact => dto.fullname = contact, "fullname" )) /* #2 */
    .Execute();

In this case the Join is to the FluentContact. So lets pick up a few important points about this: -

1. The entity that we join to is specified by the type specifier T in the join call Join().
2. The Joined-to entity class is just a standard FluentCRM Entity class.
3. The attributes used to link the two entities are determined by the left entity class (Account in this case = accountid) and the JoinAttribute(rightEntityName) method in the right entity (Contact here = parentcustomerid)
4. To use a different relationship for the join, we define a separate class which has a different specification of join attributes. This can be a subclass of an existing FluentCRM entity class.
   (See the FluentPrimaryContact for an example or a subclass of FluentContact)
5. The right entity can specify alternate left and right join attributes using the JoinFromAttribute(leftEntityName) and JoinAttribute(leftEntityname) overridde methods.
6. The same number of calls will be made to "UseAttribute()" in both case #1 and case #2 above.
7. The FluentCRM framework will deal with most of your aliasing issues.
8. If calling UseEntity((e) => ...), the entity will contain the alias value in its Alias property. e.g. "a1."
9. Entities returned in UseEntity((e)...) will contain attributes from the parent entity (i.e. Account above) and the child entity (Contact). Access to fields in the child entity will require the use of the Alias value.

Example:

Deep Joins

It is also worth noting that it is possible to join down through many levels or relationships. For examples.

FluentSecurityRole.SecurityRole(ctx.GetOrganizationService())
    .Trace(s => Debug.WriteLine(s))
    .Where("name").Equals("Test Role")
    .Join<FluentSystemUserRoles>( 
        sur => sur.Join<FluentSystemUser>(
            su => su.Where( "systemuserid").Equals( userId )
                .UseAttribute( (Guid hr) => hasRole = true, "systemuserid")
            )
        )
    .Execute();

The limits on the depth of joins will be the same as in QueryExpression. Practical experience shows that 4 or 5 levels works as expected without problems.

Additional Join features

There are some additional points to note regarding Joins that may be interesting: -

1. The Outer() function may be used within the join to cause an Outer join to be used (i.e. all records from the right and left side of the join are returned, even if there is no match)