Mapping SQL Views with NHibernate and Foreign Keys

Question

In my Solution I have the following projects

MySystem.Core
MySystem.Core.Data

MySystem.MyAudit1.Core
MySystem.MyAudit1.Core.Data

MySystem.MyAudit2.Core
MySystem.MyAudit2.Core.Data

The number of audit projects could potentially grow to around 20.

The Audit projects all require an "Organisation" reference data table. Rather than duplicate the same reference data table across all audit projects 20 times and try to keep them in sync, my plan is to have a "master" organisation table in the Core project.

This will contain Code and Description for all organisations. Then, in each particular audit project have an OrganisationCode table that contains only the Org Codes relevant to that audit but not the description, and have a view in the audit database that looks up the description from the core Org table as follows:

CREATE VIEW Organisation AS
SELECT d.OrganisationCodeId as 'OrganisationId', d.Code, a.[Description]
FROM MyAudit1.dbo.OrganisationCode d
INNER JOIN [Core].dbo.OrganisationCode a ON d.Code = a.Code

This will mean that if Org descriptions change they can be updated in one place and reflected across all the audits.

However, as you cannot have foreign keys on Views within SQL I had created a foreign key between the OrganisationCode table and whichever other tables in the audit database needs them.

As I have discovered, this of course causes NHibernate to try and return OrganisationCode table rather than the Organisation view and causes my SessionFactory creation to fall over as Nhibernate is looking for OrganisationId in the OrganisationCode table.

Is there an easy way round this which will allow me to have a single description lookup in the core project for reference data that can be used across child projects

are the audittables spread over more than one database? – Firo Feb 23 '12 at 10:43 — Firo, Feb 23 '12 at 10:43

score 0 · Accepted Answer · answered Feb 23 '12 at 11:59

Yes the audit tables are spread over separate dbs, but I have got to the route of my problem.

The reason that my SessionFactory creation was falling over is that the Configuration was being passed the audit data assembly for mapping, and my organisation mapping file was in the core data assembly.

Now that I have solved that by adding an organisation mapping file in my audit data assembly, my trust object is returning the description values from the organisation view.

Thanks for looking.

Mapping SQL Views with NHibernate and Foreign Keys

1 Answers1