CODEWITHSUNDEEP
c#sqlentity-framework
Elena
Elena

Reputation: 839

One-to-Zero relationship in Code-First

I have the following table structure:

User:
UserId
Name
CountryId

Country
CountryId
Name

where CountryId in table user is a foeign key to Country table.
In code , I have the following entities:

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



public class Country
{ 
    public int Id {get; set;}
    public string Name {get; set;}
}

Table Country stores a list of 10 available countries a user can belong to. Don't kill me for the database model, it has to be this way, legacy issues.

The relationship are defined as following through the fluent api:

 modelBuilder.Entity<User>()
         .HasRequired<Country>(c => c.Country)
         .WithMany()
         .HasForeignKey(c => c.CountryId);

The problem is every time I try to insert a new user into the db, the model defined tries to insert a new entry into the Country table as well and I get the following error:

Cannot insert the value NULL into column 'id', table '.countries'; column does not allow nulls. INSERT fails.\r\nThe statement has been terminated.

Why does ef tries to insert a new record into the country table as well and how can I fix this to only insert a new User and update the user country from the list of already existing countries?

Upvotes: 2

Views: 180

Answers (3)

Mohammad Javad
Mohammad Javad

Reputation: 584

try this:

public class Country
{ 
    public int Id {get; set;}
    public string Name {get; set;}
    public ICollection<User> Users { get; set; }
}

and :

modelBuilder.Entity<User>()
     .HasOptional<Country>(c => c.Country)
     .WithMany(c=>c.Users)
     .HasForeignKey(c => c.CountryId);

Upvotes: 0

Iain Galloway
Iain Galloway

Reputation: 19190

What's most likely happening is that you're either adding a user without a country:-

var user = new User()
{
  Name = "Elena"
};
db.Users.Add(user);

If this is the case, you'll need to ensure that that you can in fact add a user without a country.

First you'll need to change your fluent API configuration:-

modelBuilder.Entity<User>()
     .HasOptional<Country>(c => c.Country)
     .WithMany()
     .HasForeignKey(c => c.CountryId);

You'll also need to make the CountryId property on User nullable:-

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

Or you're doing something weird when you create the user to insert, e.g:-

var user = new User()
{
  ...
  Country = new Country() ... // This will create a new country!
};

If that's the case, you want to link the user to an existing country instead:-

var user = new User()
{
  ...
  CountryId = countryId
};

Upvotes: 2

George
George

Reputation: 497

Try this:

modelBuilder.Entity<User>()
     .HasOptional<Country>(c => c.Country)
     .WithMany()
     .HasForeignKey(c => c.CountryId);

Upvotes: 0

Related Questions