Friday, 6 February 2009

Fluent NHibernate and Collections Mapping

You can find some bits and pieces about mapping collections with NHibernate in many different places but yet I decided to write another post about it. What is different about my post? I hope to gather here all (in one place) relevant information regarding the most common mappings: many-to-one/one-to-many and many-to-many. In my examples I'm useing Fluent NHibernate API but also XML counterpart are included. All examples are based on the following schema: (subset of AdventureWorks database)


Bidirectional many-to-one/one-to-many

This is the most common type of association, I have used Product and ProductReview tables to depict how it works and how it can be mapped.

In our case each product (one) can have multiple reviews (many). On ProductReview side association can be mapped like this:

   References(x => x.Product, "ProductID").FetchType.Join();
which is equal to:

   <many-to-one fetch="join" name="Product" column="ProductID" />
What is FetchType doing? Basically FetchType can be Select or Join, we can define how we want NHibernate to load product for us, consider this code:

   1:  var review = session.Get<ProductReview>("1");
   2:  var productName = review.Product.Name;    

For FetchType.Join() NHibernate will call database once with following query:

   SELECT ... FROM ProductReview pr left outer join Product p on pr.ProductID=p.ProductID WHERE ... 
As you can see review and product are loaded with one call. For FetchType.Select() we will get two calls:

   SELECT ... FROM ProductReview pr WHERE ... 
   SELECT ... FROM Product p WHERE ... 
Second call will be executed on demand, it means, only if we try to use Product object like in above example: var productName = review.Product.Name;

In general you have to determine in each case which FetchType is more beneficial for you.

Now, lets check Product side, this is many side of one-to-many association so Product has a collection of reviews, I have chosen to use ISet:

   1:  HasMany(x => x.ProductReview)
   2:      .KeyColumnNames.Add("ProductID")
   3:      .AsSet()
   4:      .Inverse()
   5:      .Cascade.All();
corresponding XML mapping:

   1:      <set name="ProductReview" inverse="true" cascade="all">
   2:        <key column="ProductID" />
   3:        <one-to-many class="AdventureWorksPlayground.Domain.Production.ProductReview, AdventureWorksPlayground, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null" />
   4:      </set>
Here you can find two confusing things:
  • inverse="true" - it tells NHibernate that other side of this association is a parent. I know that it sounds other way round but that's how it is. ProductReview table has foreign key (and ProductID column) therefore ProductReview controls the association.
    What are the implications? In above example review.Product has to be set correctly, as this the property which NHibernate will check to figure our what product is associated with the review. It will ignore collection of reviews on product!
  • cascade="all" - it tells NHibernate that all events (like save, update, delete) should be propagated down. Calling session.SaveOrUpdate(product) will save (or update) the product itself but also the same event will be applied to all depending objects.
We are almost ready to move to many-to-many associations, but before we do that, check this piece of code:

   1:  var product = new Product
   2:                    {
   3:                        Name = "Bike",
   4:                        SellStartDate = DateTime.Today
   5:                    };
   6:  
   7:  product.ProductReview.Add(new ProductReview
   8:                                {
   9:                                    Product = product,
  10:                                    Rating = 4,
  11:                                    ReviewerName = "Bob",
  12:                                    ReviewDate = DateTime.Today
  13:                                });
  14:  
  15:  product.ProductReview.Add(new ProductReview
  16:                                {
  17:                                    Product = product,
  18:                                    Rating = 2,
  19:                                    ReviewerName = "John",
  20:                                    ReviewDate = DateTime.Today
  21:                                });
  22:  
  23:  
  24:  session.SaveOrUpdate(product);

Can you see a potential problem here? Each ProductReview knows about its Product, and thanks to cascade="all" everything is configured correctly but still you may end up with just one review in database ... why? I'm using ISet here, so it guarantees that I have only unique objects in the collection. Most of the people know that NHibernate classes should have Equals() and GetHashCode() methods overridden. It is useful when you want to check that two objects represent the same row in a database. People use primary id column in Equals() implementation, primary id is unique so it fits perfectly isn't it? It does if primary key is defined, and in above example, objects are saved in a last line, before that, they don't have any primary id. That is a reason for using different data to determine equality.

Bidirectional many-to-many association

For this association I have used Product, ProductProductPhoto (link) and ProductPhoto tables. Each product can have multiple photos, but each photo can also be associated with multiple products. ProductProductPhoto is just a link table and doesn't have any representation as a separate class. On both sides mapping looks very similarly.

Product side:

   1:  HasManyToMany(x => x.Photos)
   2:      .AsBag()
   3:      .WithTableName("Production.ProductProductPhoto")
   4:      .WithParentKeyColumn("ProductID")
   5:      .WithChildKeyColumn("ProductPhotoID")
   6:      .Cascade.All();
which produces XML like this:

   1:  <bag name="Photos" cascade="all" table="Production.ProductProductPhoto">
   2:        <key column="ProductID" />
   3:        <many-to-many column="ProductPhotoID" class="AdventureWorksPlayground.Domain.Production.ProductPhoto, AdventureWorksPlayground, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null" />
   4:  </bag>
and ProductPhoto side:

   1:  HasManyToMany(x => x.Products)
   2:      .AsBag()
   3:      .WithTableName("Production.ProductProductPhoto")
   4:      .WithParentKeyColumn("ProductPhotoID")
   5:      .WithChildKeyColumn("ProductID")
   6:      .Inverse();
XML:

   1:  <bag name="Products" inverse="true" table="Production.ProductProductPhoto">
   2:        <key column="ProductPhotoID" />
   3:        <many-to-many column="ProductID" class="AdventureWorksPlayground.Domain.Production.Product, AdventureWorksPlayground, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null" />
   4:  </bag>
In efect Product has a collection of Photos (IList<ProductPhoto> Photos) and ProductPhoto has a collection of products. It's mandatory, in cases like this, to mark one side as inverse="true".

This is fairly straightforward example but unfortunately not very common. In typical case link table has some additional data (like ProductDocument which has ModifiedDate column) and those additional data forces us to use different approach. Among NHibernate best practices you can find general guideline:

Good usecases for a real many-to-many associations are rare. Most of the time you need additional information stored in the "link table". In this case, it is much better to use two one-to-many associations to an intermediate link class. In fact, we think that most associations are one-to-many and many-to-one, you should be careful when using any other association style and ask yourself if it is really necessary.
So, in fact, for tables Product, Document and ProductDocument, we have to create three classes and three mappings. Both Product and Document have a link to each other through ProductDocument object. Interesting part is ProductDocument which has composite primary id (two columns) which can be mapped in a following way:

   1:      public class ProductDocumentMap : ClassMap<ProductDocument>
   2:      {
   3:          public ProductDocumentMap()
   4:          {
   5:              UseCompositeId()
   6:                  .WithKeyReference(x => x.Product, "ProductID")
   7:                  .WithKeyReference(x => x.Document, "DocumentID");
   8:  
   9:              Map(x => x.ModifiedDate).Not.Nullable();
  10:          }
  11:      }
and it generates XML like this:

   1:    <class name="ProductDocument" table="Production.ProductDocument" xmlns="urn:nhibernate-mapping-2.2">
   2:      <composite-id>
   3:        <key-many-to-one class="AdventureWorksPlayground.Domain.Production.Product, AdventureWorksPlayground, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null" name="Product" column="ProductID" />
   4:        <key-many-to-one class="AdventureWorksPlayground.Domain.Production.Document, AdventureWorksPlayground, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null" name="Document" column="DocumentID" />
   5:      </composite-id>
   6:      <property name="ModifiedDate" column="ModifiedDate" not-null="true" type="DateTime">
   7:        <column name="ModifiedDate" />
   8:      </property>
   9:    </class>
Then we can write code like this:

   1:      var product = CreateNewProduct();
   2:      var photo1 = CreateNewPhoto();
   3:      var photo2 = CreateNewPhoto();
   4:  
   5:      product.Photos.Add(photo1);
   6:      product.Photos.Add(photo2);
   7:  
   8:      // we don't have to save photos because of Cascade.SaveUpdate()
   9:      // INSERT INTO [Production.Product]
  10:      // INSERT INTO [Production.ProductPhoto]
  11:      // INSERT INTO [Production.ProductPhoto]
  12:      // INSERT INTO [Production.ProductProductPhoto]
  13:      // INSERT INTO [Production.ProductProductPhoto]
  14:      session.SaveOrUpdate(product);
And that is all what I think is important in this subject ... anything missing? Leave a comment and I will try to add missing parts.

Related posts

43 comments:

Yoyo said...

Hello,I got one question.
Assuming that the tables keep intact, but if a product can have many photos,a photo can be only belong to a product,the how to map product and photo?

Dariusz TarczyƄski said...

Great post Marek!

dotnetchris said...

Great post I've been following Fluent NHibernate for a while but have been working on other parts of my project and haven't gotten around to doing collection mappings using this for reference should make it very easy to understand which mapping is correct.

Having your schema at the top of this post made it alot more understandable, most posts I've seen on this topic tend to leave the schemas out of it.

Marek Blotny said...

@Darek and @dotnetchris

Thanks a lot for positive feedback!

@Yoyo

If I understand correctly your question then without changing tables you can't really switch to many(photos)-to-one(product).

mamboer said...

Hi,
Sorry for my obscure comment.I just got 5 tables in one of my project,which are Posts,Products, Comments,and other two linked tables CommentToPost(CommentID,PostID) plus CommentToProduct(CommentID,ProductID).
A Post(or a Product) can have many Comments,while a Comment belongs to only one Post(or a Product).
How to map these 5 tables using FL?

Marek Blotny said...

@mamboer

If a single comment belongs to only one product then there is no point in having link table. You should remove link table and add a foreign key to Comments table. Then it will be simple one-to-many association.

On Comment side you can have:
References(x => x.Product, "ProductID")

and on Product side:
HasMany(x => x.Comments)
.WithKeyColumn("ProductID")
.AsSet()
.Inverse()
.Cascade.All();

Hope that it helps :)

mamboer said...

Hi,maybe you mistake my intention here.I have a few objects which are commentable,Post's comment and Product's comment e.g.,both have the same data structure,that's why i need link table and treat Comment as an independent business object.

As in your case,things go well in the case of domain design,but it isn't so good when go to DB design.

Best regards and respect for your further points.

mamboer said...

Additional remarks:
Many commentable business objects have the SAME comment data structure ,
1,Posts(PostID,Title,Content,...)
2,Products(ProductID,Name,Desc,...)
3,Album(AlbumID,Title,...)
...
...
Typically we have two kinds of DB designs,
1,Comment dependent,no link table
PostComment(CommentID,PostID,Title,Content,...)
ProductComment(CommentID,ProductID,Title,Content,...)
AlbumComment(CommentID,AlbumID,Title,Content,...)
2,Comment independent,having link table
Comment(CommentID,Title,Content,...)
PostComment(CommentID,PostID)
ProductComment(CommentID,ProductID)
...
Obviously,option two does a better job in the DB world.

Marek Blotny said...

@mamboer
I see your point now ... it's a tricky question :)

Comment is a separate table, and will be represented a as separate object, that's certain. But single comment can reference Product or Post and that is a problem here.

I think the easiest way will be to have Product (and Post) object that will use unidirectional many-to-many association though link table to the Comment table. So comments will be available as a collection for those objects but comment itself won't have a link back.

How does it sound?

mamboer said...

ha ha,it sounds good:)
which is how i map this situation currently.

Do u know CSLA.NET?
I run a framework leveraging CSLA.NET with Nhibernate on google code,i'm looking for people who wanna do contributions.HAVE a look if you are interested.

Here goes the link:
http://code.google.com/p/cslarepo/

Christoffer said...

I would love to see your complete fluent mappings somewhere ? Can you post them or just post an url ?

Marek Blotny said...

Hi Christoffer ... here is my test project, you should find mappings there.

mamboer said...

Hi,i got an strange issue on M2M relationship mapping.
Let's say we have 3 domain objects:
1,User
2,UserRole(A link class)
3,Role

I added two roles to a user,but can't remove them through the user's navigation property LinkToRoles(of type IList< UserRole >),it always throw an error "a different object with the same identifier value was already associated with the session".

You can check my unit test on:
http://code.google.com/p/mamboer/source/browse/trunk/src/Samples/Vivasky/Vivasky.Tests/Data/UserRepositoryTests.cs,the Can_Delete_Role() test method.

Can you share me your opinion?

Anonymous said...

Hi, I've got a question. I try to do my example exactly the same as yours but it doesn't work. I've four tables: Client (id), Agent (id), Function(id) and AgentClients (client_id, agent_id, function_id). Could you write how to do mapping to this examaple. Thanks in advance :)

Topflysecurity said...

Creat post. i got stuck at work with a new project that involves alot of relations and this is just what i needed.

Thanks Marek

Topflysecurity said...

how does your mappings look for product and document?

how does the domain look for product, document and productdocument? im kinda confused with what should have IList for what and do i have 2 IList in productdocument domain?

hope im not to confusing with my questions. maby it should be eaziest way if you could post your mappings and domain so i could take a look :)

thx in advance

Marek Blotny said...

@Topflysecurity

Here you can find all source files:
http://marekblotny.googlepages.com/AdventureWorksPlayground.collections.zip

ProductDocument links document and product so it knows only about single product and single document. Product and Document have a collection of links (IList<ProductDocument>).

Cheers, Marek

Topflysecurity said...

Thx for the Code Marek

i looked at it but when i use it for my model it wont work. do i need to use the overrides in all classes? i didnt really get that. i get errors in mapping. this is often

Could not determine type for: Phaeton.Core.Domain.Receiver, Phaeton.Core, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null, for columns: NHibernate.Mapping.Column(ReceiverID).

i asume you are buizy so i will not post any code but if you feel u got a little time over just let me know :)

Anonymous said...

Can u send me a sample of how to convert stored procedures having left/outer joins , nested querries , udfs to equilavent fulent n hibernate code . All the examples given are simple scenarios but i need to have a sample of a complicated scenario . thanks

Steve Gentile said...

Thank you - especially for the sample as well.

Very well done. I was struggling with the many to many composite part of it and your sample helped me through it.

Marek Blotny said...

@Steve Gentile: thanks for the praise, I'm glad that it helped you :)

Abel Braaksma said...

Nice post! but... I was actually hoping to also find something on one-to-one relationships. I know you can create them as one-to-many but then the one-and-one-only check is gone, and I know how to create them through the HBM config, but how would you do it using Fluent NHibernate with Auto Mapping?

Sorry if this is off-topic.

-- Abel --

Dani said...
This comment has been removed by the author.
Dani said...

Hi, thid post is good, but version 1.0 holds some changes and it would be nice if the post will be addapted.

concerning the many to many example, what if I don't what the ProductPhoto Object to hold a list of products ?

(I just want the product to hold the list of it's photos) - would it be possible to map it without having the prodcutphoto class a Product member ?

SPere said...

Hi, Great post! from never working on Fluent NHibernate, I can now build quiet simple mappings by using this post and the FAQ on NHibernate website.

I was hoping you could extend you last example to show how one could go about updating "ModifiedDate" column in "ProductProductPhoto" table?

SPere said...

oops! ...

I was hoping you could extend you last example to show how one could go about updating "ModifiedDate" column in "ProductDocument" table?

Kelly said...

Sorry, realise this post is getting old, but how would you go about mapping something like User -> UserForumRole -> Forum, where UserForumRole is many to many but also contains a (property / column) denoting the role the user has for the forum? I'm thinking the many to many table would need to exist as an entity with a user property, a forum property and a role enum.

Thanks...

Anonymous said...

The sample he gives is correct for
User -> UserForumRole -> Forum

Just follow his Product -> ProductProductPhoto -> ProductPhoto Sample.

Read his stuff twice, as it's a bit confusing.

One thing to note: With the latest version of fluent, I had to remove the
.WithTableName(etc
or in the current version
.Table(etc
to get it to build the database correctly.
It kept trying to create the many-to-many table twice on me.

Jalal said...

Just wanna thank you ... searched and found your helpful article

Fatih YASAR said...

Very clear article, it was helped me a lot. Thank you very much

Pablo said...

What about if there's only PKs in ProductProductPhoto and a relatioship with QualityPhoto. One ProductProductPhoto has many QualityPhoto.

1) Do you need mapping ProductProductPhoto or put HasManyToMany in Product and ProductPhoto?
2) How is the mapping of QualityPhoto?

I have this situation in a legacy database that I couldn't even mapping ProductProductPhoto.

Could you help me?

Day to Day English said...

Very useful post. I would love however to see it updated to the latest version of Fluent

jQuery with ASP.NET said...

Yep, update to the latest version would be great. This post is now out-of-date, at least in terms of source code.

Shahzad Ali said...
This comment has been removed by the author.
Shahzad Ali said...

Can u please give any complete example about extra column in link table


Good usecases for a real many-to-many associations are rare. Most of the time you need additional information stored in the "link table". In this case, it is much better to use two one-to-many associations to an intermediate link class. In fact, we think that most associations are one-to-many and many-to-one, you should be careful when using any other association style and ask yourself if it is really necessary.
So, in fact, for tables Product, Document and ProductDocument, we have to create three classes and three mappings. Both Product and Document have a link to each other through ProductDocument object. Interesting part is ProductDocument which has composite primary id (two columns) which can be mapped in a following way:

Anonymous said...

Hi Everyone
Can some one please give me a fluent example of how to deal with this scenario:

CommentsTable(id,for,by,text,date and more properties....)

Given that: comments could be for product, employee, toilets of that company.

A linking table for each entity, like
product-comments(pid,cid,expDate)
employee-comments(eid,cid,expDate)

Domain model
myComment[expDate,Comment]

product=>myComments as IList
employee=>myComments as IList

Now: I how do i map this one, with myComment mapped as resusable component.

Thanks
Guru

Anonymous said...

May Be This is more clear scenario

class Person
{
public int Id;
public string Name;
public IList Addresses;
}

class Employee:Person
{
public int Salary;
}

class Supplier:Person
{
public string PhoneNumber;
}

class OneAddress
{
public int Id;
public DateTime LastUsed;
public Address Address;
}

class Address
{
public int Id;
public string Street;
public string City;
}

/*
* Linking Tables
* Employee-Address[empId,addressId,lastUsed]
* Supplier-Address[supId,addressId,lastUsed]
*/

Anonymous said...

Really good post! Thank you :)

Anonymous said...

Thanks for this nice explanation!

Lee Shin said...

spot on with this write-up, i like the way you discuss the things. i'm impressed, i must say. i'll probably be back again to read more. thanks for sharing this with us.

Lee Shin
www.trendone.net

Aissa said...

Nice post.Thank you for taking the time to publish this information very informative! So happy to be given a privilege to post a comment here.

aissa
www.joeydavila.net

sarah lee said...

I really enjoyed reading your article. I found this as an informative and interesting post, so i think it is very useful and knowledgeable. I would like to thank you for the effort you have made in writing this article.


edupdf.org

Cindy Dy said...

I recently came across your blog and have been reading along. I thought I would leave my first comment. I don't know what to say except that I have enjoyed reading. Nice blog. I will keep visiting this blog very often..


Pauleen
www.imarksweb.org