locked
Code First Foreign Key Iss RRS feed

  • Question

  • User438705957 posted

    I have a class called Campus to which I want to add a foreign key/relationship to class Sector.
    I have created and populated Sector as follows:

    [Table("TRACS.Lookup_Sector")]
        public class Sector
        {
            [Key]
            [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
            [Display(Name = "Sector Id", AutoGenerateField = true)]
            public int Sector_Id { get; set; }
    
            [Required]
            [StringLength(30)]
            [Display(Name = "Sector Name", AutoGenerateField = true)]
            public string Sector_Name { get; set; }        
    
            [Required]
            [Display(Name = "NoDisplay", AutoGenerateField = false)]
            public Boolean Active { get; set; }
        }

    In order to add Sector as a relationship, I have changed the Campus class as follows.

    [Table("TRACS.Lookup_Campus")]
        public class Campus
        {
            [Key]
            [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
            [Display(Name = "Campus Id", AutoGenerateField = true)]
            public int Campus_Id { get; set; }
    
            [Required]
            [StringLength(20)]
            public string Campus_Name { get; set; }
            
            [Required]
            public Boolean Active { get; set; }
    
            [Required]
            [ForeignKey("Sector")]
            [Display(Name = "NoDisplay", AutoGenerateField = false)]
            public int Sector_Id { get; set; }
    
            [Display(Name = "Sector", AutoGenerateField = true)]
            public virtual Sector
            Sector { get; set; }
        }
    

    I have the database initializer set to MigrateDatabaseToLatestVersion, so the seed method should run everytime the app domain is called.
    The migration seed method in the configuration file that populates the Sector and Campus tables is this:

    // Sector
                context.Sectors.AddOrUpdate(x => x.Sector_Id,
                   new Sector()
                   {
                       Sector_Id = 1,
                       Sector_Name = "CACS-GNS",
                       Active = true
                   });
    
                // Campus            
                context.Campuses.AddOrUpdate(x => x.Campus_Id,
                    new  Campus()
                    {
                        Campus_Id = 1,
                        Campus_Name = "Wallsend",                   
                        Active = true
                        //,Sector_Id = 1
                    }
                    );


    On running the application, I expect seed method to be called, and it is not being called.
    Subsequently the error message relating to the foreign key from Campus to Sector is posted.

    An exception of type 'System.Data.SqlClient.SqlException' occurred in EntityFramework.dll but was not handled in user code.
    Additional information: The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_TRACS.Lookup_Campus_TRACS.Lookup_Sector_Sector_Id". The conflict occurred in database "CACS_GNS", table "TRACS.Lookup_Sector", column 'Sector_Id'.

    Thanks for considering.






    Tuesday, November 15, 2016 12:12 AM

All replies

  • User283571144 posted

    Hi Madog,

    On running the application, I expect seed method to be called, and it is not being called.
    Subsequently the error message relating to the foreign key from Campus to Sector is posted.

    According to your description and codes, I have written a test demo on my computer, it works well.

    I copy all your codes and run migration.

    Could you please explain more about your EF migration?

    Probably your Campus table has some rows already inserted.

    What you are trying to accomplish is to create new table Sector and create foreign key relationship in Campusthat points to Sector table.

    In this condition, I suggest you could try bellow link's solution to solve your problem.

    http://stackoverflow.com/a/19693686

    Best Regards,

    Brando

    Tuesday, November 15, 2016 5:58 AM
  • User438705957 posted
    Hi Brando,

    Very kind of you to try and reproduce my error. It's unbelievable the support on this forum.
    You are right in the description you gave of what I am trying to achieve.
    I have done the same thing many times before for other tables, and for some reason this is not working for campus to sector.
    Yes, there are rows currently in campus, and I am trying to introduce the change, but it should still work.
    The seed is not being called at all.
    I will follow the link you posted and get back to you.
    Cheers
    Tuesday, November 15, 2016 9:08 AM
  • User438705957 posted

    Hi Brando,

    I read that article and followed the steps they suggested and am still getting the same result.

    I'm putting it down to an EF mystery.

    Cheers

    Thursday, November 17, 2016 3:34 AM
  • User283571144 posted

    Hi Madog,

    I read that article and followed the steps they suggested and am still getting the same result.

    I'm putting it down to an EF mystery.

    According to your description, I couldn't reproduce your issue now.

    So I suggest you could explain more about your codes environment.

    Could you please explain more about your previous EF code-first model(Not add the relationship with sector)?

    If you don't change your model code. it works well?

    Do you add foreign key relationship in SQL server not in the EF?

    If you could explain more about your environment, it will more easily for us to reproduce your problem and find the solution.

    Best Regards,

    Brando

    Saturday, November 19, 2016 8:56 AM
  • User438705957 posted
    Yes Brando, it works well if I leave out the attempt to add the foreign key relationship. I don't tool around in SQL Server independently of EF. I know that's a recipe for disaster.
    If I take out the following relationship from Campus, it works successful. I also have to take out the part in the configuration file that attempts to populate Campus with a sector value. The Secor table is already created via the model and has valid data in it.

    [Required]
    [ForeignKey("Sector")]
    [Display(Name = "NoDisplay", AutoGenerateField = false)]
    public int Sector_Id { get; set; }

    [Display(Name = "Sector", AutoGenerateField = true)]
    public virtual Sector
    Sector { get; set; }
    Saturday, November 19, 2016 6:55 PM
  • User283571144 posted

    Hi madog,

    Yes Brando, it works well if I leave out the attempt to add the foreign key relationship. I don't tool around in SQL Server independently of EF. I know that's a recipe for disaster.
    If I take out the following relationship from Campus, it works successful. I also have to take out the part in the configuration file that attempts to populate Campus with a sector value. The Secor table is already created via the model and has valid data in it.

    As far as I know, if you want to add foreign key relationship to existing table, you need to match its requirement.

    For example: Not null and foreign key's value need exist.

    I find your Sector and Campus table all need not null foreign key.

    But when you run the EF migration, it will add the null value column in the table, I think this is the reason why you face this error.

    I suggest you could use Update-Database –Verbose command to see the sql query.

    I suggest you could post this sql query to us, we will find the solution.

    Best Regards,

    Brando

    Tuesday, November 22, 2016 1:57 AM
  • User438705957 posted
    Hi Brando,

    The Sector table/class already exists and has data in it.
    I don't run Update Databse because I'm using MigrateDatabseToLatestVersion as an initialiser. This is how it works in Production so it should work the same in development. Automatic Migrations are enabled.
    Under the Migrations folder, I have a Configuration class which runs every time the app domain is started.
    In this class, I attempt to populate Campus with a valid Sector value that I know is there.
    It still falls over.

    This has worked for me several times before with other tables in a relationship, so I know how to do it. Its just these two tables that are the issue.

    Thanks
    Tuesday, November 22, 2016 8:30 PM
  • User283571144 posted

    Hi Madog,

    Under the Migrations folder, I have a Configuration class which runs every time the app domain is started.
    In this class, I attempt to populate Campus with a valid Sector value that I know is there.

    Sorry, I didn't explain my opinion clearly.

    I think the issue is not about EF now.

    This is your Migrations file:

            public override void Up()
            {
                AddColumn("TRACS.Lookup_Campus", "Sector_Id", c => c.Int(nullable: false));
                CreateIndex("TRACS.Lookup_Campus", "Sector_Id");
                AddForeignKey("TRACS.Lookup_Campus", "Sector_Id", "TRACS.Lookup_Sector", "Sector_Id", cascadeDelete: true);
            }

    By this codes, you will find the Sector_Id couldn't be null.

    But if you use this code update the database, the Sector_Id will be null.

    This is why you face this error.

    I suggest you could try below codes:

     [Table("TRACS.Lookup_Campus")]
        public class Campus
        {
            [Key]
            [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
            [Display(Name = "Campus Id", AutoGenerateField = true)]
            public int Campus_Id { get; set; }
    
            [Required]
            [StringLength(20)]
            public string Campus_Name { get; set; }
    
            [Required]
            public Boolean Active { get; set; }
    
       
            [ForeignKey("Sector")]
            [Display(Name = "NoDisplay", AutoGenerateField = false)]
            public int? Sector_Id { get; set; }
    
            [Display(Name = "Sector", AutoGenerateField = true)]
            public virtual Sector Sector
            { get; set; }
        }

    By using this code, the Sector_Id could be null.

    So it will not show the error.

    Then you could add the Sector_Id.

    Best Regards,

    Brando

    Wednesday, November 23, 2016 2:07 PM