In working with EF Database first, I am not being able to insert records into a specific table. I get the INSERT statement conflicted with the Foreign Key... error.
My question:
Assuming EF requires you to perform only a single dbContext.SaveChanges() at the end of your changing process, does the order you add records to the DbSets has effect on this ? Or it does not matter since the DbContext keeps track of all the changes made to the data and is itself responsible to apply them in the proper order ?
To illustrate, for 3 tables A, B and C, where B has a FK on A and C a FK on B, if I manually loop over all records I have to add to C, B and A - and do DbSet<C>.Add() before I issue DbSet<B>.Add() is problematic ?
Is it required to issue a dbContext.SaveChanges() for each table additions ?
Adding the create sql script for all 3 tables:
CREATE TABLE [dbo].[A](
[Id] [int] NOT NULL,
[a1] [int] IDENTITY(1,1) NOT NULL,
[a2] [nvarchar](50) NULL,
CONSTRAINT [PK_A] 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],
CONSTRAINT [IX_A] UNIQUE NONCLUSTERED
(
[a1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[B](
[Id] [int] IDENTITY(1,1) NOT NULL,
[a1] [int] NULL,
[b1] [int] NULL,
[b2] [nvarchar](50) NULL,
CONSTRAINT [PK_B] 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],
CONSTRAINT [IX_B] UNIQUE NONCLUSTERED
(
[b1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[B] WITH CHECK ADD CONSTRAINT [FK_B_A1] FOREIGN KEY([a1])
REFERENCES [dbo].[A] ([a1])
GO
ALTER TABLE [dbo].[B] CHECK CONSTRAINT [FK_B_A1]
GO
CREATE TABLE [dbo].[C](
[Id] [int] IDENTITY(1,1) NOT NULL,
[b1] [int] NULL,
[c1] [int] NULL,
[c2] [nvarchar](50) NULL,
CONSTRAINT [PK_C] 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]
GO
ALTER TABLE [dbo].[C] WITH CHECK ADD CONSTRAINT [FK_C_B] FOREIGN KEY([b1])
REFERENCES [dbo].[B] ([b1])
GO
ALTER TABLE [dbo].[C] CHECK CONSTRAINT [FK_C_B]
GO
I know what the error aforementioned means, but I could not find yet which data is incorrectly linked/inconsistent.
And by the way, why such errors (INSERT statement conflicted with the Foreign Key...) are not "caught" in GetValidationErrors() ?
Update:
The problem must be the way I am trying to populate the tables. But how should this be done then ?
For the scenario below:
private static void Main(string[] args)
{
//Validate_Xml_Against_Xsd.ValidateXmlAgainstXsd example = new Validate_Xml_Against_Xsd.ValidateXmlAgainstXsd();
//example.Run();
TestEntities t = new TestEntities();
A a = new A { a1 = 1000, a2 = "aaa" };
B b = new B { a1 = 1000, b1 = 87141, b2 = "bbb" };
C c = new C { b1 = 87141, c1 = 1, c2 = "ccc" };
t.C.Add(c);
t.B.Add(b);
t.A.Add(a);
//changing the order does not fix it
//t.A.Add(a);
//t.B.Add(b);
//t.C.Add(c);
int s = t.SaveChanges();
Console.WriteLine(s);
}
How should the tables be populated ? Both my DbSet.Add() attempts fail with
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_B_A1". The conflict occurred in database "Test", table "dbo.A", column 'a1'.
The statement has been terminated.
