locked
EF4 CTP5 - How to do nullible foreign keys? RRS feed

  • Question

  • Attempting a nullible or optional foreign key, EF4 creates an unwanted foreign key column for me named  [UserUserID] instead of recognizing that [UserID] is already defined as the foreign key.

    CREATE TABLE [TourAgent]
    (
      [TourAgentId] int NOT NULL  IDENTITY (1,1)
    , [FirstName] nvarchar(50) NULL
    , [LastName] nvarchar(50) NOT NULL
    , [UserID] int NOT NULL
    , [UserUserID] int NULL   <- created by EF4
    );

    CREATE TABLE [User]
    (
      [UserID] int NOT NULL  IDENTITY (1,1)
    , [UserName] nvarchar(50) NOT NULL
    );

    public class TravelAgent
    {
     public int TravelAgentID { get; set; }
     public string FirstName { get; set; }
     public string LastName { get; set; }

     [ForeignKey("UserID")]
     public int UserID { get; set; }

     public User User { get; set; }

    }

    public class User
    {
     public int UserID { get; set; }
     public string UserName { get; set; }
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
     
            modelBuilder.Entity<TravelAgent>().Property(a => a.TravelAgentID).HasColumnName("TourAgentId").HasDatabaseGenerationOption(DatabaseGenerationOption.Identity);
            modelBuilder.Entity<TravelAgent>().Property(a => a.FirstName);
            modelBuilder.Entity<TravelAgent>().Property(a => a.LastName).IsRequired();
            modelBuilder.Entity<TravelAgent>().HasKey(a => a.TravelAgentID);
            modelBuilder.Entity<TravelAgent>().ToTable("TourAgent", "gradTours");
            modelBuilder.Entity<TravelAgent>().HasEntitySetName("TravelAgents");

            modelBuilder.Entity<User>().Property(a => a.UserID).HasDatabaseGenerationOption(DatabaseGenerationOption.Identity);
            modelBuilder.Entity<User>().Property(a => a.UserName).IsRequired();
            modelBuilder.Entity<User>().HasKey(a => a.UserID);
            modelBuilder.Entity<User>().ToTable("User", "gradTours");
            modelBuilder.Entity<User>().HasEntitySetName("Users");

            modelBuilder.Entity<TravelAgent>().HasOptional<User>(a => a.User);
    }

    Is this a bug in CTP5 or have I defined the mapping incorrectly? 

    Any help would be much appreciated.

    Spider

     

     

    Tuesday, December 21, 2010 4:35 PM

Answers

  • Spider,

     

    I was able to get a nullable FK in the classes you posted just by making the UserID property nullable.  I also removed the ForeignKey attribute—this is typically applied to the navigation property and is passed the name of the FK property:

     

        public class TravelAgent

        {

            public int TravelAgentID { get; set; }

            public string FirstName { get; set; }

            public string LastName { get; set; }

     

            //[ForeignKey("UserID")]

            public int? UserID { get; set; }

     

            public User User { get; set; }

     

        }

     

    If you want to keep the property on the entity non-nullable but still have the column in the database nullable, then you would need to add this in OnModelCreating:

     

        modelBuilder.Entity<TravelAgent>().Property(a => a.UserID).IsOptional();

     

    But note that if you do this there will be a mismatch between the object semantics and the database semantics that you will need to be careful about.  Best bet is to just make the FK property nullable.

     

    Thanks,

    Arthur

    Wednesday, December 22, 2010 6:20 PM
    Moderator

All replies

  • Spider,

     

    I was able to get a nullable FK in the classes you posted just by making the UserID property nullable.  I also removed the ForeignKey attribute—this is typically applied to the navigation property and is passed the name of the FK property:

     

        public class TravelAgent

        {

            public int TravelAgentID { get; set; }

            public string FirstName { get; set; }

            public string LastName { get; set; }

     

            //[ForeignKey("UserID")]

            public int? UserID { get; set; }

     

            public User User { get; set; }

     

        }

     

    If you want to keep the property on the entity non-nullable but still have the column in the database nullable, then you would need to add this in OnModelCreating:

     

        modelBuilder.Entity<TravelAgent>().Property(a => a.UserID).IsOptional();

     

    But note that if you do this there will be a mismatch between the object semantics and the database semantics that you will need to be careful about.  Best bet is to just make the FK property nullable.

     

    Thanks,

    Arthur

    Wednesday, December 22, 2010 6:20 PM
    Moderator
  • Thanks again Arthur.  Truly appreciate you taking the time.  Helps me and I'm sure many inquiring coders.
    Wednesday, December 22, 2010 9:23 PM