Multi-field foreign key: how to setup?

May 17, 2010 at 7:51 PM
Edited May 17, 2010 at 9:11 PM

I've been trying to figure out how to set a multi-field foreign key setup, on which on of the fields is fixed.

Let me explain. I'm working on this database from a supplier of ours which has been setup many years ago, actually ported from FoxPro to SQL server two years ago. This database can't be changed. In fact, we cannot even add stored procedures or views in it.

Most of it's 'type tables' are basically in one mega-type table. Let me give you an example:

The (Customers) has a column data14 which is actually mapped to a list of departments. You could say, data14 = department_id if you designed the table yourself ;)
If you wanted to know the department name, ordinarily you would need to look that up in some table, say, Departments. Not in this database, however!

This database contains a PropertiesRegister table which has multiple columns: a primary key which isn't used for anything, and the fields NR, ID and DESCR. As you would guess, the relation would be expressed in SQL like this:

SELECT *,
PropertiesRegister.Descr AS DepartmentName
FROM Contacts
INNER JOIN PropertiesRegister
ON PropertiesRegister.ID = Contacts.data14
AND PropertiesRegister.NR = 14

Now, how would I go and define this relation using nHydrate? Is that even possible?

(To add to your schadenfreude, imagine that I actually found a field that is called data300 (!) somewhere in this database......)

Thanks a lot for your ideas!

Coordinator
May 22, 2010 at 9:18 PM

First off, if you cannot add stored procedures to the database then the nHydrate framework is going to be a bit crippled. The built-in functionality is almost all stored procedure based. That said you can use if for read-only access by using the LINQ ability. All LINQ is dynamic and there are no stored procedures. In the situation described you could define a relation in the model between PropertiesRegister and Contacts based on the field ID->data14. Now when you write your query in code it would look something like this.

PropertiesRegisterCollection propertiesRegisterCollection = PropertiesRegisterCollection.RunSelect(x=>x.NR == 14);
string deptName = propertiesRegisterCollection[0].DepartmentName;
string data14 = propertiesRegisterCollection[0].ContactsItem[0].data14;

Also remember you do not need to use the same field names as the database. Each table and field has a CodeFacade property. If you populate this value, it will be used for the code name. This will not affect the database at all. It is just a simple mapping. So you could name the "Descr" field as "DepartmentName" if you wish. Also keep in mind that tables really should be singular. You can use the CodeFacade for this too. Simple give the facade "Contact" to the table "Contacts" in your model. You will notice that one of my code objects above was named "ContactsItem" which is goofy. Adding a facade will make it "ContactItem" along with "ContactCollection" and "ContactList". This looks better.