Fluent NHibernate: IEnumerable component mapping from a single query?

Question

Hi,

Hoping you can help me with a mapping problem I have. At the moment I have a view which returns something such as:

Name | Title | Price | Date | Format | FormatPriority |

Example data may be:

Bob | Credits | 340 | 01/01/2010 | BAR | 10 |
Bob | Credits | 340 | 01/01/2010 | FOO | 20 |
Bob | Credits | 340 | 01/01/2010 | WOO | 40 |

What I want is a domain model which looks like this:

string Name;
string Title;
int Price;
DateTime Date;
IEnumerable Formats;

Format class would then have:

string Type
int Priority

At the moment we are using the ClassMap approach within Fluent NHibernate (not auto config). How would we map this? The Component doesn't seem to support a collection and this isn't a HasMany relationship as it's coming back as part of the same query.

Any ideas??

Thanks

Ben

Answer 1

Disclaimer: This is such a huge hack, it pains me to post it.

This is based on the schema you've provided, so it might need to be modified to accomodate a different design. There probably is a much better way to do this, but hopefully this should get you going again at least.

The issue is you have a bit of a mismatch in your model and query. Your query returns multiple rows which you intend to be for a single entity with multiple components, but NHibernate is geared to interpret that as multiple entities each with a single component.

NHibernate supports collections of components, but only when they're stored in a separate table/view. These components are joined via a foreign key back to the entity table. If you can change your design to support this, please do so!

If not, the only option I could think of is a self-join on your view. It won't produce the most optimised query, but it should do the trick.

You didn't mention what your entity was called, so I've gone with Transaction.

public class Transaction
{
    public virtual string Name { get; set; }
    public virtual string Title { get; set; }
    public virtual decimal Price { get; set; }
    public virtual DateTime Date { get; set; }
    public virtual ISet<Format> Formats { get; set; }
}

public class Format
{
    public virtual string Type { get; set; }
    public virtual int Priority { get; set; }

    // OVERRIDE EQUALITY MEMBERS!
}

The mapping I've used is:

public class TransactionMap : ClassMap<Transaction>
{
    public TransactionMap()
    {
        Table("vwTransactions");
        Id(x => x.Name);
        Map(x => x.Title);
        Map(x => x.Price);
        Map(x => x.Date);
        HasMany(x => x.Formats)
            .Table("vwTransactions")
            .KeyColumn("Name")
            .Component(c =>
            {
                c.Map(x => x.Type, "Format");
                c.Map(x => x.Priority, "FormatPriority");
            })
            .Fetch.Join();
    }
}

So you can see the mapping is pointed at the vwTransactions view. You didn't specify an id in your schema, so I've used Name as a identity (this is important). Skip down to the HasMany now, you can see that also points at vwTransactions; NHibernate will see this and do a self-join on the view. Then the key column is set to Name, the same as the entity Id; this way NHibernate will use that to resolve the references between the component and the entity, rather than trying to use an integer foreign key. The Fetch.Join will force NH to eagerly fetch this relationship, so at least we save a bit there. Last thing of note, the Formats property is an ISet, if you don't do this you'll end up with duplicate components.

If you now create a criteria (or hql) query for Transaction, you'll get back your entities with their components; however, you'll get duplicates due to the multiple rows being brought back per entity. This is fairly common, and easily resolved using the DistinctRootEntity transformer.

var transactions = session.CreateCriteria(typeof(Transaction))
  .SetResultTransformer(Transformers.DistinctRootEntity)
  .List<Transaction>();

That should be it, you'll now end up with just one entity (based on your dataset) with 3 components.

Nasty, I know.