locked
EF Code First Approach: Confused in EF Foreign Key constraint by fluent syntax RRS feed

  • Question

  • Hi All,

    I am trying to create a foreign key relation ship with fluent syntax using EF code first approach.

    My entities are as follows,

    public partial class Defect
        {
            public int DefectID { get; set; }
            public decimal ReleaseNo { get; set; }
            public int BuildNo { get; set; }
            public string Title { get; set; }
            public string Description { get; set; }
            public string StepsToReproduce { get; set; }
            public int ApplicationModuleID { get; set; }
            public int SeverityLevel { get; set; }
            public string LoggedBy { get; set; }
            public Nullable<System.DateTime> LoggedOn { get; set; }
            public string LastModifiedBy { get; set; }
            public Nullable<System.DateTime> LastModifiedOn { get; set; }
            public string AssignedTo { get; set; }
            public string Status { get; set; }
            public string ResolutionNote { get; set; }
            public Nullable<System.DateTime> ResolvedOn { get; set; }
            public int ProjectID { get; set; }
    
            public virtual SeverityIndex SeverityIndex { get; set; }
            public virtual User LoggedByUser { get; set; }
            public virtual User LastModifiedUser { get; set; }
            public virtual User AssignedToUser { get; set; }
            public virtual Project Project { get; set; }
    
        }


    public class DefectMap:EntityTypeConfiguration<Defect>
        {
            public DefectMap()
            {
                this.HasKey(d => d.DefectID);
                this.ToTable("Defect");
                this.Property(d => d.DefectID)
                    .IsRequired()
                    .HasColumnName("DefectID")
                    .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
                this.Property(d => d.Description)
                    .IsRequired()
                    .IsUnicode()
                    .IsVariableLength()
                    .HasMaxLength(2000)
                    .HasColumnName("Description")
                    .HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);
                this.Property(d => d.StepsToReproduce)
                    .IsOptional()
                    .IsUnicode()
                    .IsVariableLength()
                    .HasMaxLength(4000)
                    .HasColumnName("StepsToReproduce")
                    .HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);
                this.Property(d => d.LastModifiedBy)
                    .IsOptional()
                    .IsUnicode()
                    .IsVariableLength()
                    .HasMaxLength(10)
                    .HasColumnName("LastModifiedBy")
                    .HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);
                this.Property(d => d.AssignedTo)
                    .IsOptional()
                    .IsUnicode()
                    .IsVariableLength()
                    .HasMaxLength(10)
                    .HasColumnName("AssignedTo")
                    .HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);
                this.Property(d => d.Status)
                    .IsOptional()
                    .IsUnicode()
                    .IsVariableLength()
                    .HasMaxLength(50)
                    .HasColumnName("Status")
                    .HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);
                this.Property(d => d.ResolutionNote)
                    .IsOptional()
                    .IsUnicode()
                    .IsVariableLength()
                    .HasMaxLength(4000)
                    .HasColumnName("ResolutionNote")
                    .HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);
    
                this.HasRequired(p => p.Project).WithMany(p => p.DefectList).HasForeignKey(p => p.ProjectID);
                this.HasRequired(s => s.SeverityIndex).WithMany(s => s.DefectList).HasForeignKey(s => s.SeverityLevel).WillCascadeOnDelete();
                this.HasOptional(u => u.AssignedToUser).WithMany(u => u.AssignedToUserList).HasForeignKey(u => u.AssignedTo).WillCascadeOnDelete();
                this.HasOptional(u => u.LastModifiedUser).WithMany(u => u.ModifiedByUserList).HasForeignKey(u => u.LastModifiedBy);
                this.HasRequired(u => u.LoggedByUser).WithMany(u => u.LoggedByUserList).HasForeignKey(u => u.LoggedBy);
            }


    public partial class Project
        {
            public Project()
            {
                ApplicationModuleList = new List<ApplicationModule>();
                DefectList = new List<Defect>();
                UserList = new List<User>();
            }
    
            public int ID { get; set; }
            public string ProjectName { get; set; }
            public string ProjectManager { get; set; }
            public Nullable<System.DateTime> ProjectStartDate { get; set; }
            public Nullable<System.DateTime> ProjectEstimatedEndDate { get; set; }
            public Nullable<System.DateTime> ProjectActualEndDate { get; set; }
            public Nullable<int> ProjectBillingModel { get; set; }
            public Nullable<decimal> ProjectEstimatedBudget { get; set; }
            public Nullable<decimal> ProjectActualBudget { get; set; }
            public Nullable<int> ProjectPortfolio { get; set; }
            public Nullable<decimal> ProjectBillingRate { get; set; }
            public Nullable<int> ProjectEstimatedManHours { get; set; }
            public Nullable<int> ProjectActualManHours { get; set; }
            public Nullable<int> ProjectIsApproved { get; set; }
    
            public virtual ICollection<ApplicationModule> ApplicationModuleList { get; set; }
            public virtual ICollection<Defect> DefectList { get; set; }
            public virtual ICollection<User> UserList { get; set; }
            public virtual BillingModel BillingModel { get; set; }
            public virtual Portfolio Portfolio { get; set; }
            public virtual ApprovalStatus ApprovalStatus { get; set; }
        }


    public class ProjectMap:EntityTypeConfiguration<Project>
        {
            public ProjectMap()
            {
                this.HasKey(p => p.ID);
                this.ToTable("Projects");
                this.Property(p => p.ID)
                    .HasColumnName("ID")
                    .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity)
                    .IsRequired();
                this.Property(p => p.ProjectName)
                    .HasColumnName("ProjectName")
                    .HasMaxLength(200)
                    .IsRequired()
                    .IsVariableLength()
                    .IsUnicode()
                    .HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);
    
                this.HasOptional(p => p.BillingModel).WithMany(p=>p.Projects).HasForeignKey(p => p.ProjectBillingModel).WillCascadeOnDelete();
                this.HasOptional(p => p.Portfolio).WithMany(p=>p.Projects).HasForeignKey(p => p.ProjectPortfolio).WillCascadeOnDelete();
                this.HasOptional(p => p.ApprovalStatus).WithMany(p=>p.Projects).HasForeignKey(p => p.ProjectIsApproved).WillCascadeOnDelete();
            }
        }
     

    I am trying code first approach for database creation using fluent API.

    However when I run the code I get error saying

    Introducing FOREIGN KEY constraint 'FK_dbo.User_dbo.Projects_ProjectID' on table 'User' 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 

    The same error appears for AssignedTo column.

    Here I am trying to implement logic where, A project can have many defects and a defect should have an associated Project ID (i.e one to many relationship between project and defect).

    Can anyone suggest what is wrong with the code and where should I rectify the code to get things working?

    Thanks in advance!!!


    • Edited by SamirGurav Friday, August 16, 2013 9:17 PM
    Friday, August 16, 2013 9:16 PM

Answers

  • Hello,

    Welcome toMSDN Support Forum.

    From your description, I notice the issue you are experiencing is how to generate a DataBase with Code-First.

    If I have any misunderstood, please let me know.

    I review your code and find that the map-code may be not appropriate.

    I made a sample and please see the sample code below:

    Domain Class Code:

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    namespace CodeFirst.Entites
    {
        public partial class User
        {
            public User()
            {
                CostCentres = new List<CostCentre>();
                //UserXrefs = new List<UserXref>();
                //CustXrefs = new List<CustXref>();
            }
            public string Username { get; set; }
            public string FullName { get; set; }
            public Nullable<int> Password { get; set; }
            public Nullable<System.DateTime> PwdChange { get; set; }
            public Nullable<int> Privs { get; set; }
            public Nullable<System.DateTime> LastLogin { get; set; }
            public string Desk { get; set; }
            public string Initials { get; set; }
            public string D2000ID { get; set; }
            public string DefPortfolio { get; set; }
            public string LoggedIn { get; set; }
            public string Locked { get; set; }
            public string CurHostName { get; set; }
            public Nullable<int> CustID { get; set; }
            public Nullable<int> LastRefNum { get; set; }
            public string PagerApprove { get; set; }
            public Nullable<int> PagerNo { get; set; }
            //SR: Added 
            public virtual ICollection<CostCentre> CostCentres { get; set; }
            //public virtual ICollection<UserXref> UserXrefs { get; set; }
            //public virtual ICollection<CustXref> CustXrefs { get; set; }
        }
    }

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    namespace CodeFirst.Entites
    {
        public partial class CostCentre
        {
            public string Userid { get; set; }
            public string System { get; set; }
            public string CostCentre1 { get; set; }
            //SR: Added relationship
            public User User { get; set; }
        }
    }

    Domain Class Map Code:

    using System;
    using System.Collections.Generic;
    using System.Data.Entity.ModelConfiguration;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using CodeFirst.Entites;
    namespace CodeFirst.Mapping
    {
        public class UserMap : EntityTypeConfiguration<User>
        {
            public UserMap()
            {
                // Primary Key
                this.HasKey(t => t.Username);
                // Properties
                this.Property(t => t.Username)
                    .IsRequired()
                    //.IsFixedLength()
                    .HasMaxLength(12);
                this.Property(t => t.FullName)
                    .IsFixedLength()
                    .HasMaxLength(30);
                this.Property(t => t.Desk)
                    .IsFixedLength()
                    .HasMaxLength(6);
                this.Property(t => t.Initials)
                    .IsFixedLength()
                    .HasMaxLength(3);
                this.Property(t => t.D2000ID)
                    .IsFixedLength()
                    .HasMaxLength(6);
                this.Property(t => t.DefPortfolio)
                    .IsFixedLength()
                    .HasMaxLength(1);
                this.Property(t => t.LoggedIn)
                    .IsFixedLength()
                    .HasMaxLength(1);
                this.Property(t => t.Locked)
                    .IsFixedLength()
                    .HasMaxLength(1);
                this.Property(t => t.CurHostName)
                    .IsFixedLength()
                    .HasMaxLength(20);
                this.Property(t => t.PagerApprove)
                    .IsFixedLength()
                    .HasMaxLength(1);
                // Table & Column Mappings
                this.ToTable("Users");
                this.Property(t => t.Username).HasColumnName("Username");
                this.Property(t => t.FullName).HasColumnName("FullName");
                this.Property(t => t.Password).HasColumnName("Password");
                this.Property(t => t.PwdChange).HasColumnName("PwdChange");
                this.Property(t => t.Privs).HasColumnName("Privs");
                this.Property(t => t.LastLogin).HasColumnName("LastLogin");
                this.Property(t => t.Desk).HasColumnName("Desk");
                this.Property(t => t.Initials).HasColumnName("Initials");
                this.Property(t => t.D2000ID).HasColumnName("D2000ID");
                this.Property(t => t.DefPortfolio).HasColumnName("DefPortfolio");
                this.Property(t => t.LoggedIn).HasColumnName("LoggedIn");
                this.Property(t => t.Locked).HasColumnName("Locked");
                this.Property(t => t.CurHostName).HasColumnName("CurHostName");
                this.Property(t => t.CustID).HasColumnName("CustID");
                this.Property(t => t.LastRefNum).HasColumnName("LastRefNum");
                this.Property(t => t.PagerApprove).HasColumnName("PagerApprove");
                this.Property(t => t.PagerNo).HasColumnName("PagerNo");
            }
        }
    }
    using System;
    using System.Collections.Generic;
    using System.Data.Entity.ModelConfiguration;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using CodeFirst.Entites;
    namespace CodeFirst.Mapping
    {
        public class CostCentreMap : EntityTypeConfiguration<CostCentre>
        {
            public CostCentreMap()
            {
                // Primary Key
                this.HasKey(t => new { t.Userid, t.System, t.CostCentre1 });
                // Properties
                this.Property(t => t.Userid)
                    .IsRequired()
                    .HasMaxLength(12);
                this.Property(t => t.System)
                    .IsRequired()
                    .HasMaxLength(1);
                this.Property(t => t.CostCentre1)
                    .IsRequired()
                    .HasMaxLength(12);
                // Table & Column Mappings
                this.ToTable("CostCentres");
                this.Property(t => t.Userid).HasColumnName("Userid");
                this.Property(t => t.System).HasColumnName("System");
                this.Property(t => t.CostCentre1).HasColumnName("CostCentre");
                //SR : Added mapping
                this.HasRequired(cc => cc.User)
                    .WithMany(u => u.CostCentres)
                    .HasForeignKey(cc => cc.Userid);
            }
        }
    }

    The Result:

    A User can have many CostCenre and a CostCenre should have an associated UserID (i.e one to many relationship between User and CostCenre).

    If this does not work for you, please let me know.

    I look forward to hearing from you.

    Best Regards.


    <THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
    Thanks
    MSDN Community Support

    Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.

    • Marked as answer by Fred Bao Friday, August 23, 2013 1:25 AM
    Monday, August 19, 2013 3:04 AM