1

I have a legacy database design that I'm trying to work around with EF 4. Essentially, I have two tables that reference each other causing issues when trying to add new entries.

My structure is this:

CREATE TABLE [dbo].[Account] (
     [AccountId] INT IDENTITY (1, 1) NOT NULL,
     [PrimaryPersonId] INT NULL,
     [other columns])

CREATE TABLE [dbo].[Person] (
    [PersonId] INT IDENTITY (1, 1) NOT NULL,
    [AccountId] INT NOT NULL,
    [other columns])

Person has a foreign key to Account (AccountId) and Account has a foreign key to Person (PrimaryPersonId). When creating a new account and person, this is obviously a problem. Currently the solution is to use an insert trigger on the Person table that updates the Account table with a new PrimaryPersonId when a person is created.

I'd like to get away from needing triggers and bring this code into the model if possible so there's less "magic" happening. Is there a good way to do this with EF 4?

tshepang
  • 12,111
  • 21
  • 91
  • 136
Brian Vallelunga
  • 9,869
  • 15
  • 60
  • 87
  • Do the two records on `account` and `person` always point to each other? Can there be, say, `(personid = 1, accountid = 2)` in `person` and `(accountid = 2, primarypersonid = 3)` in `account`? – Quassnoi Apr 22 '11 at 13:59
  • There can be multiple persons associated with a single account (hence the need for a primary), but not multiple accounts associated with a single person. – Brian Vallelunga Apr 22 '11 at 14:14

1 Answers1

0

I would suggest moving to having the insert happen in code. Build out an account method that accepts a person object as a parameter) to add the person object into the account table. You could even go bigger and wrap an interface on anything that handles inserting into that table so that you can make sure it ALWAYS happens.

Blake Rogers Onyxtek Software Solutions, LLC

Blake Rogers
  • 637
  • 2
  • 7
  • 13