Generic Join for one-to-one and one-to-many relationships

I’m trying to come up with a way to dynamically join data as needed using the C# driver. The POCO models include information about relationships and they already contain ‘containers’ to hold looked up objects (these are not stored, but hydrated on extraction on demand). Here’s my data model (don’t bother suggesting to denormalize - this is just a constructed sample).

public class PhoneBookContact
{
    [BsonId]
    public ObjectId Id { get; set; }

    public string Name { get; set; }

    [Reference("Manager")]
    public ObjectId ManagerId { get; set; }

    [JsonIgnore]
    public PhoneBookContact Manager { get; set; }
	
    [Reference("Secretaries")]
    public List<ObjectId> SecretaryIds { get; set; }
	
    [JsonIgnore]
    public List<PhoneBookContact> Secretaries { get; set; }
}

The container properties, ManagerId and SecretaryIds have a custom attribute Reference that tell us how the data fits together. So if there’s a ManagerId, the looked up PhonebookContact should be put into Manager and likewise for SecretaryIds => Secretaries.

I’m looking for a way to write the join, so that it can be reused (for any type, find all props with a Reference tag, perform the join and define how the references should be filled.

For the manager, this is how far I got:

var col = db.GetCollection<PhoneBookContact>().AsQueryable();
col = col.Join(col, contact => contact.ManagerId, manager => manager.Id, (contact, manager) => { contact.Manager = manager; return contact; });

private PhoneBookContact ProjectContact(PhoneBookContact contact, PhoneBookContact manager)
{
    contact.Manager = manager;
    return contact;
}

The problem is that this doesn’t work since the external method cannot be translated to an expression. This works, but it’s not what I’m after since it would involve mapping every property manually which doesn’t scale:

col = col.Join(col, contact => contact.ManagerId, manager => manager.Id, (contact, manager) => new PhoneBookContact
            { Id = contact.Id, Name = contact.Name, ManagerId = contact.ManagerId, Manager = manager });

Also, what about the many-to-many relationship Secretaries ?

Again, I know in this constructed sample I’d end up applying the denormalization patterns and include a partial PhoneBookContact in the Manager and Secretaries collection but this is just to figure out how both joins have to be written, and then generalized based on the tags on the object. Then end goal is that I have a property lookupReferences that if set to true, generates the join on any object without having to write code for every different type of document.

I finally managed to figure it out:

 var hydratedContact = context.GetCollection<PhoneBookContact>()
    .Aggregate()
    .Match(u => u.Id == id)
    .Lookup(GetCollectionName<PhoneBookContact>(), nameof(PhoneBookContact.ManagerId), "_id", nameof(PhoneBookContact.Manager))
    .Unwind(nameof(PhoneBookContact.Manager))
    .Lookup(GetCollectionName<PhoneBookContact>(), nameof(PhoneBookContact.SecretaryIds), "_id", nameof(PhoneBookContact.Secretary))
    .As<PhoneBookContact>()
    .FirstOrDefault();

I did have to remove the JsonIgnore tags though (I’m actually using a ClassMap and did UnmapMember ) to get the values to populate.

That even works in a generalized way - I just need to follow my Reference tags, get the right type from it and then the collection name.

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.