3

How to create one-to-one relation using NHibernate where other side could be a NULL? For example, I have a Banner entity that has a relation to Image entity like this:

<class name="Banner" table="Banner"> <id name="Id" type="Int64" unsaved-value="0"> <generator class="native" /> </id> <many-to-one name="Image" unique="true" column="ImageId" not-null="false"/> <!-- check: In the example this property was without inverse attribute --> <set name ="BannerSlideSet" fetch="subselect" inverse="true"> <key column="Banner_ID" foreign-key="FK_Banner_BannerSlide" not-null="false" /> <one-to-many class="BannerSlide"/> </set> </class>

So, Image can be null for a Banner entity. I create one banner entity with no image without any issues. In DB I have 

-------------- ID | ImageId -------------- 1 | NULL

After that, I'm trying to create second Banner instance with no Image as well and get the following error:

NHibernate.Exceptions.GenericADOException: could not insert: [Domain.Entities.Banner][SQL: INSERT INTO Banner (ImageId) VALUES (?); select SCOPE_IDENTITY()] ---> System.Data.SqlClient.SqlException: Violation of UNIQUE KEY constraint 'UQ_Banner_7516F70DE6141471'. Cannot insert duplicate key in object 'dbo.Banner'. The duplicate key value is ().

I guess, it happens because I have unique constraint on one-to-many relation between banner and image entities and several Banner instances can't have several NULL values in ImageId field. Question: how I would achieve one-to-nullableone relation with in NHinerbate?

Thanks!

Improve this question

1 Answer 1

Reset to default
3

The solution is to move the ImageId from the Banner table. And place it in the Image table.

This way, whenever we have real (not null) image, we can let:

  1. reference the Banner (BannerId) by Image (many-to-one from the Image side, and collection one-to-many of images from the Banner side) or
  2. use the real one-to-one relationship: the ImageId is the unqiue constrained, generated by the owning Banner's BannerId.

This is the example form the documenation 5.1.11. one-to-one, adjusted to Banner and Image

Now we must ensure that the primary keys of related rows in the BANNER and IMAGE tables are equal. We use a special NHibernate identifier generation strategy called foreign:

The Image mapping:

<class name="Image" table="[Image]"> <id name="Id" column="[ImageId]"> <generator class="foreign"> <param name="property">Banner</param> </generator> </id> ... <one-to-one name="Banner" class="Banner" constrained="true"/> </class>

The Banner:

 <one-to-one name="Image" class="Image"/>
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

If image could be shared among many banners, let's use the pairing tabler BannerImage. This will again allow us to have banners without image, and if there is relation... we can add a pair record. See the <bag> and <many-to-many> mapping: nhforge.org/doc/nh/en/index.html#collections-ofvalues
thank you for the reply. In your approach Images belongs only to Banner entities. I didn’t mention this in my initially message, but, I have several Entities that refer Images: Banner.Image (nullable), BannerSlide.Image (nullable), UserProfile.Photo (not null), and maybe will be others. I was hoping that it would be possible to keep one-way relations to Image entities (Image may know only about UserProfile, since all user’s images are collected under UserProfule.Images). Should I redesign my domain structure? Or, maybe extend it with “link” entities/tables (like Banner_Image table)?
The point here is the SQL Schema, there is a problem. Not in your entities, not in the NHibernate. If there is a unique constraint over the column ImageId... 2 banners won't be allowed to have NULL value. In fact, the easiest way is to remove that constraint. In case, that this does not work for your requirements, I think that pairing tables like: Entity1-Image, Entity2-Image, Entity3-Image... will do the best for you. NHibernate is ready to support any of these solutions. Enjoy this tool ;)
Thanks for reply again. So in case of pairing table and many-to-many, instead of Banner.Image property we have a set of images and there is no way we could restrict it to only one image in DAL. And, in Services layer I have to make sure that a Banner can have only one image. Is this correct?
The correct answer is Yes, you have to do these checks in the code. On the other hand, because the mapping will be in C# represented as IList<Image>, even in case, that somehow the data will contain more relations... code will work. But the best place for applying these checks is for sure the business/service layer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.