EF Code First Error

Question

I am trying to create some tables using Code First. Here is my code:

public class Country
{
    [Key]
    public int Id { get; set; }
    public string CountryName { get; set; }
}

public class State
{
    [Key]
    public int Id { get; set; }
    public string StateName { get; set; }
    public int CountryId { get; set; }
    public Country Country { get; set; }
}

public class Customer
{
    [Key]
    public int Id { get; set; }
    public string Name { get; set; }
    public int CountryId { get; set; }
    public int StateId { get; set; }
    public virtual Country Country { get; set; }
    public virtual State State { get; set; }
}

public class ProductContext : DbContext 
{
    public DbSet<Country> Country { get; set; }
    public DbSet<Customer> Customer { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
    }
}

When I Execute this code the following error occurs:

Introducing FOREIGN KEY constraint 'FK_dbo.State_dbo.Country_CountryId' on table 'State' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints. Could not create constraint. See previous errors.

But I want the CountryId in State Table to be a foreign key. What am I missing here? Can anybody guide me the correct way of achieving this?

score 0 · Accepted Answer · edited May 23 '17 at 12:14

0

Entity Framework is worried about deletion here- because the User has a direct relationship to a Country and also a State and the State also relates to a Country you effectively have a potential loop of User -> State -> Country -> User which would mean that if cascade deletions were enabled the moment you deleted one user you would potentially delete everything in your database.

The answer is in the error message- by disabling cascade deletions across some of these relationships ( which is logical - deleting a user doesn't mean you want to delete their state and country ) you will avoid this risk. As you might imagine this has come up on SO before.

As an aside, having the Country on the User and also on the State looks like questionable denormalisation - there may be a good reason for it, but that happens less often than you would expect.

edited May 23 '17 at 12:14

Community

1
1

answered May 06 '15 at 10:08

glenatron

11,018
13
64
112

Thank you. It worked. Will this disable cascade deletes for any additional tables that I may add in future? – Abhilash D K May 06 '15 at 10:13
CREATE TABLE [dbo].[Customer] ( [Id] INT IDENTITY (1, 1) NOT NULL, [Name] NVARCHAR (MAX) NULL, [CountryId] INT NOT NULL, [StateId] INT NOT NULL, CONSTRAINT [PK_dbo.Customer] PRIMARY KEY CLUSTERED ([Id] ASC), CONSTRAINT [FK_dbo.Customer_dbo.Country_CountryId] FOREIGN KEY ([CountryId]) REFERENCES [dbo].[Country] ([Id]), CONSTRAINT [FK_dbo.Customer_dbo.State_StateId] FOREIGN KEY ([StateId]) REFERENCES [dbo].[State] ([Id]) ); – Abhilash D K May 06 '15 at 10:19
CREATE TABLE [dbo].[State] ( [StateId] INT IDENTITY (1, 1) NOT NULL, [CountryID] INT NOT NULL, [StateName] VARCHAR (100) NOT NULL, PRIMARY KEY CLUSTERED ([StateId] ASC), CONSTRAINT [FK_State_Country] FOREIGN KEY ([CountryID]) REFERENCES [dbo].[Country] ([CountryId]) ); – Abhilash D K May 06 '15 at 10:19
CREATE TABLE [dbo].[Country] ( [CountryId] INT IDENTITY (1, 1) NOT NULL, [CountryName] VARCHAR (100) NOT NULL, PRIMARY KEY CLUSTERED ([CountryId] ASC) ); – Abhilash D K May 06 '15 at 10:19
This is the SQL i have used to create the tables ( leaving code first ). Here CountryID is on Both Customer as well as State. Can you advice me on the best practices I should follow – Abhilash D K May 06 '15 at 10:21
Cascade deletes mean that if I delete an item in Table1 then related items in Table2 are deleted too. So for example if I had User and UserPasswords it would make sense to delete the passwords when I deleted the user, so cascade deletions make sense there. Make the decision based on the relationship between your entities. I tend to switch it off by default, but enable it in the ( relatively few ) places it makes sense. – glenatron May 06 '15 at 10:39

EF Code First Error

1 Answers1