CODEWITHSUNDEEP
c#nhibernatefluent-nhibernate
Michael Hallock
Michael Hallock

Reputation: 1413

Fluent NHibernate and multiple key columns

I have a User entity that has a IList property. The mapping for this looks like this:

        HasMany(x => x.Forms) 
            .Cascade.None() 
            .AsBag().KeyColumn("Supervisor") 
            .Key(key => key.Not.Update()) 
            .PropertyRef("Email");

Now I have a new feature request in that essentially adds another KeyColumn that should ALSO populate into this property. Essentially, each form can have a "Supervisor" and an "Alternate", and I need this property to populate any Forms where the User.Email is equal to either one (Forms BELONG to a User IF the User.Email == Form.Supervisor OR User.Email == Form.Alternate).

Is there a way to do this? Simply adding another KeyColumn specification simply overrides the previous one, and adding keys via the Keys() method seems to not allow for multiple key columns like I want... I'm looking for a way to essentially tell it that this relationship should be found with a WHERE Superviser = Email OR Alternate = Email, but it doesn't seem to support that OR clause...

My other option is essentially duplicate this mapping, but for "Alternate" in another property, and then slam the collections together in my consuming code, but I wanted to see if NHibernate was smart enough for this to be possible...

Any suggestions?

Upvotes: 0

Views: 1602

Answers (1)

Daniel Schilling
Daniel Schilling

Reputation: 4967

To my knowledge, no - NHibernate does not support this unusual type of join. I would approach this exactly like you said. Use two collections - User.SupervisorForms and User.AlternateForms, then combine them later, perhaps using LINQ's Concat method to do so. You could define a property to do this concatenation for you:

public virtual IEnumerable<Form> Forms
{
    get { return SupervisorForms.Concat(AlternateForms); }
}

If you wish to load a User with a completely initialized Forms collection in a single round-trip to the database, then you can use this approach from the Improving Performace - Multi Query section of the NHibernate documentation:

User user = s.CreateMultiQuery()
    .Add("select u from User u left join fetch u.SupervisorForms where u.Id = :id")
    .Add("select u from User u left join fetch u.AlternateForms where u.Id = :id")
    .SetInt32("id", 123)
    .UniqueResult<User>();

This code uses CreateMultiQuery() and HQL - but the approach should work just as well with your choice of query batching syntax: CreateMultiQuery(), CreateMultiCriteria(), or Future() / FutureValue().

Upvotes: 1

Related Questions