Why does adding explicit foreign ID columns to my EF4.1 CodeFirst entity cause a foreign key constraint conflict?

Currently I have my entities working with models that look like:

public class MyModel
{
    public int开发者_如何学运维 Id { get; set; }
    public string Name { get; set; }
    public string Description { get; set; }

    public virtual User User { get; set; }
}

This is working good, but I keep seeing CodeFirst examples with explicitly created Id properties for foreign entities in the model. This makes sense so I don't have to do lazy load the user to retrieve the id value of the user associated with a MyModel instance.

However, when I change MyModel to be:

public class MyModel
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Description { get; set; }

    public virtual int UserId { get; set; }
    public virtual User User { get; set; }
}

I get the following exception when attempting to commit MyModel instances:

System.Data.SqlClient.SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint "MyModel_User". The conflict occurred in database "mjlunittest", table "dbo.Users", column 'Id'.

Why am I getting this exception?

When you move from your first example of MyModel to the second example the EF Code-First conventions detect one important change:

In the fist case where you don't have a foreign key property in the model the relationship MyModel -> User is optional because the property User in MyModel can - as a reference to another object - be null. So, the EF conventions will create a nullable foreign key column in the database.

In the second case you are telling EF that your foreign key UserId is of type int which is not nullable. Therefore EF convention rules will consider the relationship MyModel -> User as required and create an Int32 column in the database which is not nullable.

If you create an object of the second version type MyModel, add it to the context and call SaveChanges you'll get the exception you described - unless you have a User in the database with the foreign key (which had to be 0 if you don't specify another value when you create the object). Creating and saving an object of the first version won't throw this exception because here the foreign key in the database is allowed to be null.

If you want that both examples of MyModel behave the same way you can either make the foreign key property nullable in the second example:

public virtual int? UserId { get; set; }

Then the relationship is optional in both examples of MyModel.

Or you can make the User property required in the first example:

[Required]
public virtual User User { get; set; }

Then the relationship is required in both examples.