I understand the error, the question is how do I tell EF to insert the User record before the UserSettings?
I am trying to setup a 1 to 1 relationship where the User table can exist without the UserSettings, but the UserSettings can only exist if there is a User. In order to do that I setup the UserSettings table to have its primary key to also be the foreign key; maybe this is bad design, thoughts? Here is the create script for both tables:
CREATE TABLE [dbo].[Users]
(
[Userid] UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
[UserName] NVARCHAR(MAX) NOT NULL,
CONSTRAINT [PK_Users] PRIMARY KEY ([Userid])
)
and here is the UserSettings table:
CREATE TABLE [dbo].[UserSettings]
(
[UserId] [uniqueidentifier] NOT NULL,
[SettingOne] [bit] NOT NULL,
[SettingTwo] [bit] NOT NULL,
[SettingThree] [bit] NOT NULL,
CONSTRAINT [PK_UserSettings] PRIMARY KEY ([UserId]),
CONSTRAINT [FK_UserSettings_Users] FOREIGN KEY ([UserId]) REFERENCES [Users]([UserId]),
)
This is how my entities are setup:
public class User
{
[Key]
public Guid UserId { get; set; }
public string UserName { get; set; }
[ForeignKey("UserId")]
public UserSetting UserSetting { get; set; }
}
public class UserSetting
{
[Key]
public Guid UserId { get; set; }
public bool SettingOne { get; set; }
public bool SettingTwo { get; set; }
public bool SettingThree { get; set; }
}
When I run the following:
static void Main(string[] args)
{
using (var db = new SandBoxContext())
{
var userId = Guid.NewGuid();
db.Users.Add(new User()
{
UserId = userId,
UserName = "Bob",
UserSetting = new UserSetting()
{
UserId = userId,
SettingOne = true,
SettingTwo = false,
SettingThree = true
}
});
db.SaveChanges();
}
Console.ReadKey();
}
db.SaveChanges() throws the exception below:
SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_UserSettings_Users". The conflict occurred in database "SandBox", table "dbo.Users", column 'UserId'.