Fluent API not creating Identity Specification (Is Identity) in SQL Server

Question

I have an MVC4 application that is using code first. I have created 3 models that I wish to use to keep track of my clients Associates each with a designation of AssociateType (Distributors or Retailers) each Associate also has a deignated Region (North Florida or South Florida.

I started off by creating the following models and running update-database to create the tables in the database.

Associate.cs

    namespace XXX.Models
    {
        public class Associate
        {
            public int AssociateID { get; set; }
            [StringLength(50), Column(TypeName = "varchar")]
            public string AssociateName { get; set; }
            public int AddressNumber { get; set; }
            [StringLength(50), Column(TypeName = "varchar")]
            public string AddressStreet { get; set; }
            [StringLength(20), Column(TypeName = "varchar")]
            public string AddressCity { get; set; }
            [StringLength(2), Column(TypeName = "varchar")]
            public string State { get; set; }
            [StringLength(10), Column(TypeName = "varchar")]
            public string Zipcode { get; set; }
            [StringLength(16), Column(TypeName = "varchar")]
            public string MainPhoneNumber { get; set; }
            [StringLength(60), Column(TypeName = "varchar")]
            public string AssociateEmail { get; set; }
            [StringLength(80), Column(TypeName = "varchar")]
            public string AssociateWebsite { get; set; }

            //See Corresponding Navigation Properties
            [Display(Name = "Region")]
            public int RegionID { get; set; }
            [Display(Name = "AssociateType")]
            public int AssociateTypeID { get; set; }

            [StringLength(35), Column(TypeName = "varchar")]
            public string ContactFirstName { get; set; }
            [StringLength(35), Column(TypeName = "varchar")]
            public string ContactLastName { get; set; }
            [StringLength(16), Column(TypeName = "varchar")]
            public string ContactPhoneNumber { get; set; }
            [StringLength(60), Column(TypeName = "varchar")]
            public string ContactEmail { get; set; }

            public virtual Region Region { get; set; }
            public virtual AssociateType AssociateType { get; set; }
        }

AssociateType.cs

    namespace XXX.Models
    {
        public class AssociateType
        {
            [ForeignKey("Associate")]
            public int AssociateTypeID { get; set; }
            [StringLength(50), Column(TypeName = "varchar")]
            public string AssociateTypeName { get; set; }

            public virtual Associate Associate { get; set; }
        }
    }

'Region.cs'

    namespace XXX.Models
    {
        public class Region
        {
            public int RegionID { get; set; }
            [StringLength(20), Column(TypeName = "varchar")]
            public string RegionName { get; set; }
            [Column(TypeName = "varchar(Max)")]
            public string RegionDescription { get; set; }

            public virtual Associate Associate { get; set; }
        }
    }

DBContext

    namespace XXX.Models
    {
        public class XXXDb : DbContext
        {
            public XXXDb(): base("name=DefaultConnection")
            { 

            }
            public DbSet<Associate> Associates { get; set; }
            public DbSet<AssociateType> AssociateType { get; set; }
            public DbSet<Ingredient> Ingredients { get; set; }
            public DbSet<Region> Regions { get; set; }
            public DbSet<UserProfile> UserProfiles { get; set; }


            protected override void OnModelCreating(DbModelBuilder modelBuilder)
            {
            modelBuilder.Entity<Associate>().HasKey(a => a.AssociateID);
            modelBuilder.Entity<Associate>().Property(a => a.AssociateID)
                        .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
            modelBuilder.Entity<Associate>().HasRequired(at => at.AssociateType)
                        .WithRequiredDependent();
            modelBuilder.Entity<Associate>().HasRequired(r => r.Region)
                        .WithRequiredDependent();

            modelBuilder.Entity<AssociateType>().HasKey(at => at.AssociateTypeID);
            modelBuilder.Entity<AssociateType>().Property(at => at.AssociateTypeID)
                        .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);

            modelBuilder.Entity<Region>().HasKey(r => r.RegionID);
            modelBuilder.Entity<Region>().Property(r => r.RegionID)
                        .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
            }
        }
    }

My Tables are not being created with the IDENTITY SPECIFICATION (Is Identity) being set to Yes..... WHY IS THIS?? Because of this I cannot add any data to the tables or I get an error:

Cannot insert the value NULL into column 'RegionID', table 'XXXDb.dbo.Regions'; column does not allow nulls. INSERT FAILS.

My goal is to populate the Region & AssociateType tables with just a few rows of items.

Regions: (North Florida & South Florida) AssociateTypes (Distributors & Retailers)

This way when I add an Associate during a CRUD operation I would have two drop downs that have the options (Distributors & Retailers) for AssociateType and (N or S Florida) for that associates Region.

Any help would be very much appreciated. I'm really getting frustrated with MVC. I have made it pretty far, but starting to get discouraged.

Ok Now I got it, a region of course can have many Associates right? — Raphael, Oct 30 '13 at 07:14
YES. But I would phrase it differently. An associate can have one region. Represented by that regions RegionID. Same with AssociateType. — Eric Bishard, Oct 31 '13 at 12:09

Raphael · Accepted Answer · 2013-10-30T12:13:35.300

2

I did some test and here is the solution that work on my machine I just kept the navigation properties of your obejcts.

public class Associate
{
    public int AssociateID { get; set; }

    public int RegionID { get; set; }
    public virtual Region Region { get; set; }
    public int AssociateTypeID { get; set; }
    public virtual AssociateType AssociateType { get; set; }
}

public class Region
{
    public int RegionID { get; set; }
    [StringLength(50), Column(TypeName = "varchar")]
    public string IngredientNameEn { get; set; }
    [Column(TypeName = "varchar(Max)")]
    public string IngredientNameEs { get; set; }

    public virtual List<Associate> Associates { get; set; }
}

public class AssociateType
{
    public int AssociateTypeID { get; set; }
    [StringLength(50), Column(TypeName = "varchar")]
    public string AssociateTypeName { get; set; }

    public virtual List<Associate> Associates { get; set; }
}

Then in the OnModelCreating you have to add the following two commands and this should generate the database that you want

modelBuilder.Entity<Region>().HasMany(a => a.Associates)
            .WithRequired(r => r.Region).HasForeignKey(r => r.RegionID);
modelBuilder.Entity<AssociateType>().HasMany(a => a.Associates)
            .WithRequired(r => r.AssociateType).HasForeignKey(r => r.AssociateTypeID);

and in the class constructor you may add this code

 public XXXDb(): base("name=DefaultConnection")
 { 
     Database.SetInitializer(new DropCreateDatabaseIfModelChanges<XXXDb>());
     Database.Initialize(force: true);
 }

edited Oct 30 '13 at 12:13

answered Oct 30 '13 at 07:24

Raphael

1,677
1
15
23

I made the changes you supplied: – Eric Bishard Oct 30 '13 at 10:59
update-database -verbose -force No pending code-based migrations. Applying automatic migration: 201310301056183_AutomaticMigration. ALTER TABLE [dbo].[Associates] DROP CONSTRAINT [FK_dbo.Associates_dbo.Regions_AssociateID] System.Data.SqlClient.SqlException (0x80131904): 'FK_dbo.Associates_dbo.Regions_AssociateID' is not a constraint. Could not drop constraint. See previous errors. 'FK_dbo.Associates_dbo.Regions_AssociateID' is not a constraint. Could not drop constraint. See previous errors. – Eric Bishard Oct 30 '13 at 10:59
does this happens when you are initialize the database? if yes have tried to drop the db by hand? – Raphael Oct 30 '13 at 11:06
If it helps, each associate can only have 1 RegionID and 1 AssociateTypeID. This way I believe creates a one to many. – Eric Bishard Oct 30 '13 at 11:06
Last time I tired dropping db manually it royally screwed up my project. I'f like to achieve anything through codefirst if possible. I know your trying to be helpful but I'd rather not try that again and set myself back. – Eric Bishard Oct 30 '13 at 11:08
NOt sure what you mean by initialize. I have the above codefirst scenario and it generated a database then I changed it to your scenario and the above errors were created.....gotta run to work will check when I get back. Thanks for all your help. I appreciate the time. – Eric Bishard Oct 30 '13 at 11:09
i mean initialize when at the start of the program you check the program and try to initialize the db context.Database.Initialize(force: true); I updated the post above, I can't figure out what's going wrong. I started from you code, generated the db and the I changed it to the code I posted and I don't get any error – Raphael Oct 30 '13 at 12:15
This works, but may I ask why do I use a List? Thanks for the help..The initializer &Drop If Model Changes made it work. I just need to add a seed for the information in the database that needs to stay there. – Eric Bishard Oct 31 '13 at 12:49
I usually use list because I have to do a lot of calculation dto do after i retrieved my data, but here you can find a quick explaination http://stackoverflow.com/questions/11307171/ef-icollection-vs-list-vs-ienumerable-vs-iqueryable. You have to use a list because for every Region will get N associates (one to many relation) – Raphael Oct 31 '13 at 12:59
That makes sense. I appreciate the help Raphael. I come from asp classic and web forms. So I have had to learn a lot of new stuff with MVC. You shed a light on an area I was utterly confused with. I thank you! – Eric Bishard Oct 31 '13 at 22:21
AHHHHH I SEE! Yeah now that I scaffolded out my CRUD operations it automatically created a list for my dropdowns. Nice! I was doing that by hand before. – Eric Bishard Nov 01 '13 at 02:33
I had to do the same path... And when I realized what MVC can do I was super exited – Raphael Nov 01 '13 at 16:46

Fluent API not creating Identity Specification (Is Identity) in SQL Server

1 Answers1