Linq to Sql and Non-PK, unique-FK relationship issues

Question

Hello everyone,

I've recently been reading Louis Davidson's book on Sql Server Database Design and found it quite informative. I've picked up on alot of concepts that I didn't previously know alot (or anything) about. Primarily - I picked up on a way to set up database relationships that I hand't tried before.

Basically you use a surrogate key as the tables PK (an auto incremented id field) and then set up one or more Alternate Keys consisting of one or more Unique keys. Theese alternate kays would then be the values used for relationships (or the PK, if that makes more sense for the given relationship).

I remodelled an old database that was suffering from some data inconsitencies due to poor design to implement this, to me, new way of thinking.

On a database level it works great. Tha relationships function the way they're supposed to and the constraints are enforced in a consistent, reliable manner.

HOWEVER

I cannot get it to work properly in either the Entity Framework or in Linq to Sql classes. I read that in V1 of EF, it just flat out won't support this kind of relationship - so I moved to Linq to Sql to see if things would work out better. They seemingly did, as I got all the relationships automatically mapped out when I imported the classes from my database. The problem is that I can't save data to the database because of InvalidCastOperation exceptions as soon as I try to save data.

So I have a couple of questions:

1. Is this a limitation in Linq To Sql?
2. If so, is there a way to work around it? Preferebly without implementing sprocs for save, update and delete - type safety is something I would like to keep.
3. Is this way of designing database relationships "correct" and/or a good practice?

I hope someone can shed some light on this, as I'm getting quite frustrated about it. I can't really find any good material on the subject online - so hopefully someone here has an answer or can point me in the right direction.

Thanks alot!

EDIT - Solution.

What I ended up doing was this - I went back to using the Entity Framework in conjunction with a redesign of the database schema. I remodeled the relationships to rely on primary keys rather than alternate keys, in most cases. Where that was not an option - I made some modifications to the EF layout. I implemented the relationship that relied on the AK's - at which time EF complains. To get around that I had to delete the foreign key property on the many side of the relationship at which point EF accepts the relationship.

Answer 1

1) Yes.

2) If you can mark your alternate key as primary in the L2S model and unmark the real PK as PK then it will work.

3) From the db perspective there's nothing wrong, but as you have noticed it is not supported by L2S or EF. Personally I prefer to always have FKs pointing to the PK and only use AKs for lookups.