Fluent NHibernate - Mapping a property to a column on a joined table

Question

I've got a couple tables, for example:

• Product {Id, Name, ManufacturerId, ...}
• Manufacturer {Id, Name, ...}

I'd like to be able to include ManufacturerName on my Product object (instead of having to load the whole Manufacturer row when I only need the name). My ProductMap looks like...

Table("Product");
Id(x => x.Id, "Id");
Map(x => x.ProductName, "ProductName");
Map(x => x.ManufacturerId, "ManufacturerId");
References(x => x.Manufacturer, "ManufacturerId");

What do I need to add to populate the ManufacturerName property on my Product object? I believe I need to make some sort of Join() call, but I'm having trouble figuring out how to write it with all the relevant parameters. It needs to join the current table (Product) to the Manufacturer table, on Product.ManufacturerId = Manufacturer.Id, and grab the Manufacturer.Name column, populating the ManufacturerName property on the object.

Answer 1

I think you could use a formula to dynamically retrieve a manufacturer name. This is not an elegant solution and personally I would prefer using a separate sql view mapped to a new entity (e.g. ProductExtra, etc.) where it would query just necessary columns but anyways. Here we go:

1. Add the ManufacturerName property to the Product class

2. Add a mapping line for that new property to your ProductMap:

   Table("Product");
   Id(x => x.Id, "Id");
   Map(x => x.ProductName, "ProductName");
   Map(x => x.ManufacturerId, "ManufacturerId");
   Map(x => x.ManufacturerName).Formula("(select m.ManufacturerName from Manufacturer m where m.Id = ManufacturerId)");
   
   References(x => x.Manufacturer, "ManufacturerId");
   

Hope this helps.

Answer 2

NH Joins are tricky, and require things that your schema may not support. For instance, the joined table's primary key is matched to your current table's primary key. It works a lot like a OneToOne mapping, except NH won't create an explicit constraint to that effect. Since this isn't the case in your mapping (looks like a many-to-one reference), I doubt you could make an explicit join work.

Try mapping a "pass-through" property:

public class Product
{
   ...

   public string ManufacturerName
   {
      get{return NHibernateUtil.IsInitialized(Manufacturer) 
                    ? Manufacturer.Name : manufacturerName;}
      set{if(NHibernateUtil.IsInitialized(Manufacturer))
             Manufacturer.Name = value 
          else
             manufacturerName = value;}
   }
}

...

//In your mapping:
Map(x => x.ManufacturerName, "ManufacturerName");

This will persist a normalized Manufacturer's name onto the Product table as a denormalized field. The field will also exist in the Manufacturer table. When you retrieve JUST the Product, you get the name from the Product table. After the Manufacturer is lazy-initialized for some other reason (or eager-loaded), you get the name from the Manufacturer table, which then means you can persist the Manufacturer record's name to the Product.