Composite primary key scenario

Question

I need a many to many structure, but with an aggregate constraint. What i'm trying to accomplish is done easily in pure sql, but since ActiveRecord discourages composite primary keys, i'm not sure how to accomplish what i need in recommended style.

Here is what i would have in pure sql:

table Project (ID int)

table Report (ProjectWideID nvarchar(50), ProjectID int, primary key (ProjectWideID, ProjectID))

table ChosenReport(ListOrder int, ProjectWideReportID, ReportID, primary key (ProjectID,ProjectWideReportID))

This means that a project has many reports. Each report has an assigned id, which is unique inside a project. Project has many chosen reports as an ordered list, each of them references a report in the same project by it's project-wide assigned report id.

But here is my ActiveRecord classes, and something is missing here.

[ActiveRecord]
public class Project
{
 [PrimaryKey]
 public int ID { get; set; }
 [HasMany] IList<Report> Reports { get; set; }
 [HasMany] IList<ChosenReport> ChosenReports { get; set; }
}

[ActiveRecord]
public class Report
{
 [PrimaryKey]
 public int ID { get; set; }
 [BelongsTo("ProjectID")]
 public Project ParentProject { get; set; }

 // ... other properties
}

[ActiveRecord]
public class ChosenReport
{
 // This one must be a key property
 [BelongsTo("ParentProjectID")]
 Project ParentProject { get; set; }

 // This one must be a key property
 [BelongsTo("ParentProjectID")]
 Report ParentReport { get; set; }

 // ... other properties
}

Now, since i have surrogate keys, i don't know how to constraint ChosenReport so it can't have reference to a report from different project. So i have to enforce constraints in domain. Do i have any other options for this with ActiveRecord?

Answer 1

The true ActiveRecord way of doing this would be making your classes inherit from ActiveRecordBase<T> and then overriding OnSave() and implementing your checks there. But I recommend implementing the checking logic in a NHibernate interceptor or event listener instead.