locked
Error creating table with Foreign Key RRS feed

  • Question

  • I am trying to create a database and code base using Entity Frameworks's Code First method.  I have a set of classes that inherit from a base class.  The classes are below:

    User Class:

    public class Users
            {
                [Key]
                public int ID { get; set; }
                [Required]
                [StringLength(100, ErrorMessage = "Description Max Length is 100")]
                public String FirstName { get; set; }
                [StringLength(100, ErrorMessage = "Description Max Length is 100")]
                public String MiddleName { get; set; }
                [Required]
                [StringLength(100, ErrorMessage = "Description Max Length is 100")]
                public String LastName { get; set; }
                [StringLength(100, ErrorMessage = "Description Max Length is 100")]
                public String KnownAs { get; set; }
                [Required]
                [StringLength(10, ErrorMessage = "Description Max Length is 10")]
                public String UserName { get; set; }
                [StringLength(500, ErrorMessage = "Description Max Length is 500")]
                public String Email { get; set; }
                [StringLength(15, ErrorMessage = "Description Max Length is 15")]
                public String Telephone { get; set; }
                [Required]
                public Locations UserLocation { get; set; }
            }

    GroupManager Class:

    public class GroupManagers : Users
            {
                [Required]
                public Boolean OnDuty { get; set; }
            }

    BackUpGroupManagers Class:

    public class BackUpGroupManagers : GroupManagers
            {
                [Required]
                public Boolean CallFirst { get; set; }
            }

    OnCall Class:

    public class OnCalls : Users
            {
                [Required]
                public Boolean OnDuty { get; set; }
            }

    When the database is created, it creates a single users table, as seen below:

    SELECT [ID]
          ,[FirstName]
          ,[MiddleName]
          ,[LastName]
          ,[KnownAs]
          ,[UserName]
          ,[Email]
          ,[Telephone]
          ,[OnDuty]
          ,[CallFirst]
          ,[OnDuty1]
          ,[Discriminator]
          ,[UserLocation_ID]
      FROM [dbo].[Users]

    I am overriding the OnModelCreating like this:

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
            {
                modelBuilder.Entity<WorkGroups>().HasRequired(a => a.BackUpGroupManager).WithMany().HasForeignKey(u => u.BackUpGroupManagerID);
                modelBuilder.Entity<WorkGroups>().HasRequired(b => b.GroupManager).WithMany().HasForeignKey(x => x.GroupManagerID).WillCascadeOnDelete(false);
                modelBuilder.Entity<WorkGroups>().HasRequired(c => c.OnCall).WithMany().HasForeignKey(y => y.OnCallID).WillCascadeOnDelete(false);
            }

    I am doing this because if I don't, I get the following error:

    Introducing FOREIGN KEY constraint 'FK_dbo.WorkGroups_dbo.Users_BackUpGroupManagerID' on table 'WorkGroups' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

    Below is the WorkGroups Class:

    public class WorkGroups
            {
                [Key]
                public int ID { get; set; }
                [Required]
                [StringLength(16, ErrorMessage = "Description Max Length is 16")]
                public String WorkGroup { get; set; }
                public int GroupManagerID { get; set; }
                public int BackUpGroupManagerID { get; set; }
                public int OnCallID { get; set; }
                [ForeignKey("GroupManagerID")]
                public GroupManagers GroupManager { get; set; }
                [ForeignKey("BackUpGroupManagerID")]
                public BackUpGroupManagers BackUpGroupManager { get; set; }
                [ForeignKey("OnCallID")]
                public OnCalls OnCall { get; set; }
            }

    I would like the OnCall, BackUpGroupManagers, and GroupManagers class to all be their own table with a foreign key back up to the Users table.  

    Can someone please point me in the redirection?

    Thanks

    Thursday, July 4, 2013 3:51 PM

Answers

  • Hi Bob;

    Not sure how the third mapping is done because Users being the base class of GroupManagers inheriting from Users but I am not sure that GroupManagers can be the base class to BackUpGroupManagers because it was already define as an inherited type.


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    NOTE: If I ask for code, please provide something that I can drop directly into a project and run (including XAML), or an actual application project. I'm trying to help a lot of people, so I don't have time to figure out weird snippets with undefined objects and unknown namespaces.

    • Marked as answer by Chester Hong Thursday, July 18, 2013 2:08 AM
    Tuesday, July 9, 2013 1:49 PM

All replies

  • Hi Bob;

    What you are looking for is called Table Per Type, TPT, and the following post describes the three types of inheritance with example code.

    Code First in the ADO.NET Entity Framework 4.1

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    NOTE: If I ask for code, please provide something that I can drop directly into a project and run (including XAML), or an actual application project. I'm trying to help a lot of people, so I don't have time to figure out weird snippets with undefined objects and unknown namespaces.

    Thursday, July 4, 2013 7:15 PM
  • Fernando,

    Thank you for the reply.  I have come up with the following code based on the article your suggested.  

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
            {
                modelBuilder.Entity<Users>().Map(m => m.ToTable("Users")).Map<OnCalls>(m => m.ToTable("OnCalls"));
                modelBuilder.Entity<Users>().Map(m => m.ToTable("Users")).Map<GroupManagers>(m => m.ToTable("GroupManagers"));
                modelBuilder.Entity<GroupManagers>().Map(m => m.ToTable("GroupManagers")).Map<BackUpGroupManagers>(m => m.ToTable("BackUpGroupManagers"));
            }

    However, I get the following error:

    The type 'Users' has already been mapped to table 'Users'. Specify all mapping aspects of a table in a single Map call.
    Saturday, July 6, 2013 3:10 PM
  • Hi Bob;

    Not sure how the third mapping is done because Users being the base class of GroupManagers inheriting from Users but I am not sure that GroupManagers can be the base class to BackUpGroupManagers because it was already define as an inherited type.


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    NOTE: If I ask for code, please provide something that I can drop directly into a project and run (including XAML), or an actual application project. I'm trying to help a lot of people, so I don't have time to figure out weird snippets with undefined objects and unknown namespaces.

    • Marked as answer by Chester Hong Thursday, July 18, 2013 2:08 AM
    Tuesday, July 9, 2013 1:49 PM