4

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.

Chadd Nervig
  • 327
  • 1
  • 4
  • 16

2 Answers2

8

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.

Denis Ivin
  • 5,594
  • 1
  • 26
  • 25
  • This looks like it'd be a fine solution, except for the fact that the DB is a version of oracle that doesn't support subqueries within the select parameters. (Ugh) Thanks though – Chadd Nervig Mar 04 '11 at 19:02
  • Going to accept this as the answer, since it would work for probably 99% of the people who later come looking for an answer to this question. Sadly, I'm in the 1% that uses an 8yr old Oracle database that this doesn't work on. – Chadd Nervig Mar 04 '11 at 19:52
  • Ah.. Yes, I was testing in Oracle 9, so that's probably why it works there. Well, you could still go with a helper sql view and map it to a new entity which would include just a subset of columns and then use it instead of the regular Product, but I guess it mainly depends on the scenario. – Denis Ivin Mar 04 '11 at 20:39
  • Oracle 9? I was working in Oracle 10g three years ago. Course, I'm still working in SQL Server 2005 now, six years later, soooo.... – KeithS Mar 04 '11 at 22:10
  • Heh.. I have to maintain systems connected to MSSQL 2000.. So, SQL Server 2005 is not that bad after all :) – Denis Ivin Mar 04 '11 at 23:15
1

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.

KeithS
  • 70,210
  • 21
  • 112
  • 164
  • Yeah, it's a many-to-one. But the Product table doesn't actually have ManufacturerName on it, so this would always be blank until that Manufacturer row is initialized (or simply would throw an error on mapping)? – Chadd Nervig Mar 04 '11 at 19:05
  • You'd have to either add the field to the table, or pull the Product record from an editable view. That's the downside of an ORM; NH simply expects you to want enough of what you've mapped to justify pulling it all when you ask it for a record, and doesn't give you many options other than fully-customized queries to pull joined multi-table result sets out of the DB. – KeithS Mar 04 '11 at 19:24
  • Unfortunately, modifying the database in any way is out of the question. Looks like I'll have to just deal with the performance of grabbing the whole row each time, individually, or wherever that's too much to handle, build a list of all the ManufacturerIds I need and grab just the name from all of them in a batch. Thanks for the assistance. – Chadd Nervig Mar 04 '11 at 19:43