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 ]"}

Can you kindly correct me if i am wrong. A foreign key is NULLABLE by DEFAULT in code first asp.net mvc - 5 entity framework. — Unbreakable, Aug 09 '17 at 22:34
If we want to make it non nullable. we need to either use fluent api if not then decorate with "Required" attribute. Am I correct? — Unbreakable, Aug 09 '17 at 22:34
If we don't do either then Foreign key will be defaulted to Nullable — Unbreakable, Aug 09 '17 at 22:35

score 213 · Accepted Answer · answered Apr 14 '11 at 19:49

213

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; }
}

answered Apr 14 '11 at 19:49

Ladislav Mrnka

360,892
59
660
670

47

Virtual is necessary for lazy loading. – Ladislav Mrnka Apr 14 '11 at 20:25
6

Virtual also adds change tracking, which isn't always wanted. About the only time that we ever want virtual is on collections, but YMMV. – Dan VanWinkle Oct 24 '12 at 06:31
@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)? – Travis J Jan 14 '13 at 19:17
1

@TravisJ user.Country returns null then... either catch the exception (optimal) or use `if` (eww) – SparK Feb 12 '14 at 18:13
Any ideas as to how this translates to vb.net? – Jay Nov 24 '14 at 12:07
@Jay use `Nullable(Of Integer)` or `Integer?` in vb.net. – BrainSlugs83 Jul 17 '15 at 00:48
14

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.) :-( – BrainSlugs83 Jul 17 '15 at 00:49
@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? – Geethanga Apr 11 '17 at 10:46
I think in asp.net mvc 5 code first convention, foreign keys are nullable by default. – Unbreakable Aug 09 '17 at 22:02
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. – user1040323 Sep 17 '18 at 10:08
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` – Gonzo345 Jan 23 '20 at 07:19

score 14 · Answer 2 · edited Mar 14 '19 at 13:04

14

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.

edited Mar 14 '19 at 13:04

Mark Cooper

6,738
5
54
92

answered Sep 17 '18 at 10:10

user1040323

481
4
11

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. – perustaja Dec 23 '20 at 19:35

score 10 · Answer 3 · edited Jul 24 '18 at 15:21

10

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 :)

edited Jul 24 '18 at 15:21

r3plica

13,017
23
128
290

answered Jul 17 '18 at 17:22

Yaman Melhem

141
3
13

6

'WithOptional' no linger valid for EFCore... only has 'WithOne' – user1034912 Mar 11 '21 at 03:58

score 1 · Answer 4 · answered Jan 13 '20 at 22:26

1

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://learn.microsoft.com/en-gb/ef/ef6/fundamentals/relationships?redirectedfrom=MSDN

answered Jan 13 '20 at 22:26

Anonimys

596
1
5
14

Entity framework code-first null foreign key

4 Answers4

Linked