nhibernate hasmany when not using foreign key

Question

Let say I have a request table, that looks as follows:

RequestId INT  
ReferenceNumber VARCHAR

Each request is logged in a requestlog table. There are no foreign keys between the tables:

RequestLogId INT  
ReferenceNumber VARCHAR  
Content VARCHAR

The requestlog contains the content of the request, and this content needs to be stored for a period of time. The request can be deleted, but the log can only be deleted after a period of time.

How can I map my request object so that it can contain a list of requestlogs, based on the referencenumber?

I tried this:

Table("InsuranceRequest");  
Id(i => i.Id).Column("InsuranceRequestId");  
Map(i => i.ReferenceNumber);  
HasMany(i => i.InsuranceRequestLog).KeyColumn("RefenceNumber").LazyLoad.Cascade.None();

It doesn't work though. This mapping tries to map the referencenumber to the RequestLogId of requestlog. Is there a way to specify that the joining column should be the insurancerequestlog referencenumber?

Answer 1

nothing prevent the many to many in your table structure.. i recommend to change the structure and make them foreign keys, and i'm sure that the many to many mapping will work for u if the mapping i not working after the structure change.

another tip for u: take an empty db and try expose schema.. than u'll be able to see what nhibernate think the structure should look like