none
ef with code first error InnerException_{"Cannot insert explicit value for identity column in table 'Agents' when IDENTITY_INSERT is set to OFF."} RRS feed

  • Question

  • Im really confussed Im getting the above error but just seem to be hitting brick walls trying to resolve, any help would be greatly appreciated.

    I am trying to create a new Agent record linked to SignErector. using the following

      agent.SignErector = signErector;
                  context.Agents.Add(agent);
                
                context.SaveChanges();

    I have the following structures with the API code following

     public partial class Agent
        {
     
            [Key]
            public int AgentID { getset; }
            public string Name { getset; }
            public virtual List<AgentContact> Contacts { getset; }
            public virtual List<Reminder> Reminders { getset; }
            public SignErector SignErector { getset; }
            public Address Address { getset; }
           public virtual AgentContact SalesContact { getset; }
            public virtual AgentContact AccountsContact { getset; }
            public virtual List<AgentAction> Actions { getset; }
            public virtual PostType Post { getset; }
            public virtual List<Job> Jobs { getset; }
        }
        public partial class SignErector
        {
           [Key]
            public int SignErectorID { getset; }
            public string Code { getset; }
            public string Name { getset; }
            public virtual List<Agent> Agents { getset; }
            public virtual List<PostType> PostTypes { getset; }
            public virtual List<Reminder> SignErectorReminders { getset; }
            public List<StandardTag> StandardTags { getset; }
        }
                modelBuilder.Entity<Agent>().HasRequired(v => v.Post).WithOptional(e => e.Agent);
                modelBuilder.Entity<AgentContact>().HasRequired(v => v.Agent).WithMany(e => e.Contacts).Map(e => e.MapKey("Agent_AgentID"));
                modelBuilder.Entity<Job>().HasRequired(v => v.Agent).WithMany(e => e.Jobs).Map(e => e.MapKey("Agent_AgentID"));
                modelBuilder.Entity<Job>().HasOptional(v => v.LastMovement);
                modelBuilder.Entity<Job>().HasMany(m => m.Movements).WithRequired(e => e.Job);
                modelBuilder.Entity<StandardTag>().HasRequired(v => v.SignErector).WithMany(e => e.StandardTags).Map(e => e.MapKey("SignErector_SignErectorID"));
                modelBuilder.Entity<Reminder>().HasOptional(v => v.Agent).WithMany(e => e.Reminders).Map(e => e.MapKey("Agent_AgentID"));
                modelBuilder.Entity<Reminder>().HasOptional(v => v.SignErector).WithMany(e => e.SignErectorReminders).Map(e => e.MapKey("SignErector_SignErectorID"));
                modelBuilder.Entity<Movement>().HasOptional(v => v.Reminder).WithOptionalPrincipal(e => e.AssociatedMovement).Map(e => e.MapKey("AssociatedMovement_MovementID"));
                modelBuilder.Entity<Agent>().HasOptional(v => v.Reminders).WithOptionalPrincipal().Map(e => e.MapKey("Agent_AgentID"));
                modelBuilder.Entity<SignErector>().HasOptional(v => v.SignErectorReminders).WithOptionalPrincipal().Map(e => e.MapKey("SignErector_SignErectorID"));
           

     

     


    rc
    Wednesday, December 21, 2011 2:45 PM

Answers

  • Hi rc,

    Welcome!

    Please feel free to test my code belows:

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.ComponentModel.DataAnnotations;
    using System.Data.Entity;
    
    namespace Onemany
    {
        class Program
        {
            static void Main(string[] args)
            {
                using (var db= new MyContext())
                {
                    Agent a = new Agent() {  AgentID=1, Name="test"};
                    SignErector s = new SignErector { SignErectorID=1, Code="test", Name="name" };
                    a.SignErector = s;
                    db.Agents.Add(a);
                    db.SaveChanges();
                }
            }
        }
        public partial class Agent
        {
    
            [Key]
            public int AgentID { get; set; }
            public string Name { get; set; }
           
            public SignErector SignErector { get; set; }
           
        }
        public partial class SignErector
        {
            [Key]
            public int SignErectorID { get; set; }
            public string Code { get; set; }
            public string Name { get; set; }
            public virtual List<Agent> Agents { get; set; }
          
        }
        public class MyContext:DbContext
        {
            public DbSet<Agent> Agents { get; set; }
            public DbSet<SignErector> SignErectors { get; set; }
            protected override void OnModelCreating(DbModelBuilder modelBuilder)
            {
                modelBuilder.Entity<Agent>().Property(a => a.AgentID).HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);
                modelBuilder.Entity<SignErector>().Property(s => s.SignErectorID).HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);
                modelBuilder.Entity<Agent>().HasRequired(e => e.SignErector).WithMany(s => s.Agents).Map(m => m.MapKey("SignErector_SignErectorID"));
            }
        }
    
    }
    
    

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, December 22, 2011 8:18 AM
    Moderator
  • I did reply a while back but it doesnt apear to of been published, I actually reverted to using a raw SQL insert and then doing an update on the inserted record
    rc
    • Marked as answer by Richc12345 Tuesday, January 3, 2012 4:43 PM
    Tuesday, January 3, 2012 4:43 PM

All replies

  • if you are using linq to sqk click on the table in the designer and make sure it is set to not null identity, auto generated value, if you have handcrafted it make sure it has these attributes  [Column(IsPrimaryKey = true, IsDbGenerated = true, AutoSync = AutoSync.OnInsert)]
    Wednesday, December 21, 2011 3:19 PM
  • Thanks i have tried that but get the following for all the attributes

    Error 5 'System.ComponentModel.DataAnnotations.ColumnAttribute' does not contain a definition for 'AutoSync' 

      public partial class Agent
        {
     
            [Key]
            [Column(IsPrimaryKey = trueIsDbGenerated = trueAutoSync = AutoSync.OnInsert)]
           public int AgentID { getset; }

    rc
    Wednesday, December 21, 2011 3:27 PM
  • using System.Data.Linq.Mapping;

     

    Wednesday, December 21, 2011 3:36 PM
  • :( nope same error ive set

      public partial class Agent
        {
            [Key]
            [System.Data.Linq.Mapping.Column(IsPrimaryKey = true, IsDbGenerated = true, AutoSync = AutoSync.OnInsert)]
           public int AgentID { getset; }	
    Ive also checked the mappings and set 
     modelBuilder.Entity<Agent>().HasRequired(v => v.SignErector).WithMany(e => e.Agents).Map(e => e.MapKey("SignErector_SignErectorID"));
    
           agent.SignErector = signErector;
                signErector.Agents.Add(agent);
                context.SaveChanges();

    rc
    Wednesday, December 21, 2011 3:42 PM
  • if you go to sql manager do the tables in question have identity turned on?  Perhaps you should a refresh of your model?
    Wednesday, December 21, 2011 3:52 PM
  • Yes  they do thats why Im so stuck, I have even dropped and re-created them to no avail :(
    rc
    Wednesday, December 21, 2011 4:14 PM
  • Hi rc,

    Welcome!

    Please feel free to test my code belows:

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.ComponentModel.DataAnnotations;
    using System.Data.Entity;
    
    namespace Onemany
    {
        class Program
        {
            static void Main(string[] args)
            {
                using (var db= new MyContext())
                {
                    Agent a = new Agent() {  AgentID=1, Name="test"};
                    SignErector s = new SignErector { SignErectorID=1, Code="test", Name="name" };
                    a.SignErector = s;
                    db.Agents.Add(a);
                    db.SaveChanges();
                }
            }
        }
        public partial class Agent
        {
    
            [Key]
            public int AgentID { get; set; }
            public string Name { get; set; }
           
            public SignErector SignErector { get; set; }
           
        }
        public partial class SignErector
        {
            [Key]
            public int SignErectorID { get; set; }
            public string Code { get; set; }
            public string Name { get; set; }
            public virtual List<Agent> Agents { get; set; }
          
        }
        public class MyContext:DbContext
        {
            public DbSet<Agent> Agents { get; set; }
            public DbSet<SignErector> SignErectors { get; set; }
            protected override void OnModelCreating(DbModelBuilder modelBuilder)
            {
                modelBuilder.Entity<Agent>().Property(a => a.AgentID).HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);
                modelBuilder.Entity<SignErector>().Property(s => s.SignErectorID).HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);
                modelBuilder.Entity<Agent>().HasRequired(e => e.SignErector).WithMany(s => s.Agents).Map(m => m.MapKey("SignErector_SignErectorID"));
            }
        }
    
    }
    
    

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, December 22, 2011 8:18 AM
    Moderator
  • Hi,

    I am writing to check the status of the issue on your side. Would you mind letting us know the result of the suggestions?

    If you need further assistance, please feel free to let me know. I will be more than happy to be of assistance.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, December 29, 2011 7:03 AM
    Moderator
  • I did reply a while back but it doesnt apear to of been published, I actually reverted to using a raw SQL insert and then doing an update on the inserted record
    rc
    • Marked as answer by Richc12345 Tuesday, January 3, 2012 4:43 PM
    Tuesday, January 3, 2012 4:43 PM