Click here to Skip to main content
15,071,519 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi All,

I have an issue with LINQ and 1 to 1 relationships. Basically I have 2 tables that form a 1 to 1 relationship. The one table's PK (table A) is an auto generated number and the other tables PK is also the FK (table B).

Now when I create a new instance of table A's entity and attach a new entity of table B to A and try to commit the changes to the database, i get a 'The INSERT statement conflicted with the FOREIGN KEY constraint' exception. from what I can deduce from the data context's log, the foreign key is not updated automatically as I would think it should.

Any ideas?

Please let me know if I need to be more clear in my question.

thanks in advance
Posted
Comments
Bala Selvanayagam 11-Oct-11 10:57am
   
can you please post me the table structure of both tables please [Showing the primary keys at both sides]... also you code whoing how you are inserting from the front end

As far as you have the relationship (one to one) defined at the database level then it will work.

I just did a test and hope it helps

1.I have tables Employee & Address - the one to one relationship is defined and verified in the "database diagrams" node of SQL server.

SQL
CREATE TABLE [dbo].[employees](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](50) NULL,
 CONSTRAINT [PK_employees] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]


SQL
CREATE TABLE [dbo].[addresses](
    [AddressID] [int] IDENTITY(1,1) NOT NULL,
    [EmpID] [int] NOT NULL,
    [Address] [varchar](50) NULL,
 CONSTRAINT [PK_addresses] PRIMARY KEY CLUSTERED
(
    [AddressID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]



The following code at the front end works

try {
              DataClasses1DataContext db = new DataClasses1DataContext();

              employee emp = new employee();
              emp.Name = "Bala";
              db.employees.InsertOnSubmit(emp);

              address add = new address();
              add.Address1 = "UK";
              add.employee = emp;
              db.addresses.InsertOnSubmit(add);

              db.SubmitChanges();
              MessageBox.Show("OK");

          } catch (Exception ex) {
              MessageBox.Show (ex.Message);
          }
   
Comments
PJ du Preez 11-Oct-11 13:35pm
   
Thanks. This was the other option I was considering. To use your example, i have a factory that (and this was my intention) to build an Employee-Address relationship for me (depending on the type of employee , a different address type should be attached). But i think to KISS i'll have to do away with the factory so that I can InsertOnSubmit my employee before i attempt to attach the address. I'm using a repository pattern to keep things seperate. Thanks again!
create two different objects for those two tables
First commit first table (objTableA)
then set this auto generated PK id to the FK of second table object
objTableB.FKField = objTableA.PKField
and then commit second table.

reason behind this is that in tableB the FK id that your setting should exists in the tableA first, as you have the relation.

Hope this will help you.

~Amol.
   
Comments
PJ du Preez 11-Oct-11 9:39am
   
Thanks, this is an option I was thinking of but due to complexities in my structure, this is not the route I want to go. I have done similar things with one to many relationships which works perfectly. any thoughts?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900