Fluent NHibernate - Mapping tables which reference an ID from a lookup table

Question

Supposed I have 4 tables defined as follows:

Projects: [ Project_Id, Project_Name ]

Vendors: [ Vendor_Id, Vendor_Name ]

Project_Vendors: [ Project_Vendor_Id, Vendor_Id, Project_Id ]

Payments: [ Payment_Id, Project_Vendor_Id, Payment_Amount ]

The relationships should be as follows:

A Project can have many Vendors associated
A Vendor can belong to many projects
Many payments can be made to many vendors of a project (so what makes a payment unique is a project AND a vendor)

I can easily map Project and Vendor objects and their relation using the ProjectVendors lookup table with the following:

public class ProjectMapping : ClassMap<Project> { public ProjectMapping() { Table("Projects"); Id(x => x.Id).Column("Project_Id").GeneratedBy.TriggerIdentity(); HasManyToMany(x => x.Vendors).Table("Project_Vendors") .ParentKeyColumn("Project_Id") .ChildKeyColumn("Vendor_Id") .Cascade.AllDeleteOrphan(); } }

My question is, if my classes are defined as follows:

public class Payment { public virtual int? Id { get; set; } public virtual Project Project { get; set; } public virtual Vendor Vendor { get; set; } public virtual double Amount { get; set; } } public class Project { public virtual int? Id { get; set; } public virtual string Name { get; set; } public virtual List<Vendor> Vendors { get; set; } public virtual List<Payment> VendorPayments { get; set; } }

How can I map my Project and Payment objects using the lookup table Project_Vendors?

Firo · Accepted Answer · 2012-12-11 19:31:26Z

Not directly possible without mapping the Project_Vendors table because NHibernate has to know where the id to reference comes from. You might be able to fake it but i would advice against it.

class ProjectVendorPair { public virtual int Id { get; set; } public virtual Project Project { get; set; } public virtual Vendor Vendor { get; set; } } // in PaymentMap References(x => x.Owner, "Project_Vendor_Id");

Or you change the database Structure:

get rid of column Project_Vendor_Id
have columns Project_Id and Vendor_Id in Payment table

Yeah I kind of figured this. I don't mind changing the database structure except that it takes away from the integrity of the data a bit and also creates a small amount of duplicate data. Would you consider this approach acceptable in enterprise level software where the integrity of your data is more reliant on an application level than on a database level?
dataintegrity is still secure when defining a unique constraint over Project_id and Vendor_id in link table and a foreignkey constraint from Payment.Project_id/Vendor_id to Link table. It might even be more efficient since you do not have to go to the database to get the Project_Vendor_Id when making a payment

Collectives™ on Stack Overflow

Fluent NHibernate - Mapping tables which reference an ID from a lookup table

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related