Entity framework code-first null foreign key

Question

I have a User < Country model. A user belongs to a country, but may not belong to any (null foreign key).

How do I set this up? When I try to insert a user with a null country, it tells me that it cannot be null.

The model is as follows:

 public class User{
    public int CountryId { get; set; }
    public Country Country { get; set; }
}

public class Country{
    public List<User> Users {get; set;}
    public int CountryId {get; set;}
}

Error: A foreign key value cannot be inserted because a corresponding primary key value does not exist. [ Foreign key constraint name = Country_Users ]"}

Solution 1

You must make your foreign key nullable:

public class User
{
    public int Id { get; set; }
    public int? CountryId { get; set; }
    public virtual Country Country { get; set; }
}

Solution 2

I prefer this (below):

public class User
{
    public int Id { get; set; }
    public int? CountryId { get; set; }
    [ForeignKey("CountryId")]
    public virtual Country Country { get; set; }
}

Because EF was creating 2 foreign keys in the database table: CountryId, and CountryId1, but the code above fixed that.

Solution 3

I have the same problem now , I have foreign key and i need put it as nullable, to solve this problem you should put

    modelBuilder.Entity<Country>()
        .HasMany(c => c.Users)
        .WithOptional(c => c.Country)
        .HasForeignKey(c => c.CountryId)
        .WillCascadeOnDelete(false);

in DBContext class I am sorry for answer you very late :)

Solution 4

I recommend to read Microsoft guide for use Relationships, Navigation Properties and Foreign Keys in EF Code First, like this picture.

Guide link below:

https://docs.microsoft.com/en-gb/ef/ef6/fundamentals/relationships?redirectedfrom=MSDN

View more solutions

Comments

[Ladislav Mrnka]

Virtual is necessary for lazy loading.

[Dan VanWinkle]

Virtual also adds change tracking, which isn't always wanted. About the only time that we ever want virtual is on collections, but YMMV.

[Travis J]

@LadislavMrnka - And how would the lazy loading work when an attempt to get a navigation property was made when the id was null (which throws an exception)?

[SparK]

@TravisJ user.Country returns null then... either catch the exception (optimal) or use if (eww)

[Jay]

Any ideas as to how this translates to vb.net?

[BrainSlugs83]

@Jay use Nullable(Of Integer) or Integer? in vb.net.

[BrainSlugs83]

Also -- this doesn't seem to work for Guid based keys. (It makes them nullable, sure, but saving a record to the database with the foreign key set to null fails due to an automatically generated foreign key constraint.) :-(

[Geethanga]

@LadislavMrnka I already have a nullable column in my table. Only thing is earlier I didn't need the navigation properties so I didn't add them, so the foreign key is not there. Now I have a requirement to navigate between these two entities. When I tried to add the navigation props, migration is created. But when that migration is applied it gives me following error. "The ALTER TABLE statement conflicted with the FOREIGN KEY constraint" I know this should work and what am I missing?

[Unbreakable]

I think in asp.net mvc 5 code first convention, foreign keys are nullable by default.

[user1040323]

I prefer this (below): public class User { public int Id { get; set; } public int? CountryId { get; set; } [ForeignKey("CountryId")] public virtual Country Country { get; set; } } Because EF was creating 2 foreign keys in the database table: CountryId, and CountryId1, but the code about fixed that.

[Gonzo345]

I stated the nullable Guid on my model too and I wasn't noticing any nullable: true on my brand new migration. Once applied, checked the relationships on the SMSS and saw it was allowing nulls. My problem was that I was trying to add Guid.Empty instead of a null

[perustaja]

This is exactly the problem I was having. I don't even have a navigation property in my entity, so it's a bit odd that it's happening anyway.

[user1034912]

'WithOptional' no linger valid for EFCore... only has 'WithOne'