none
Primary key violation with GUID RRS feed

  • Question

  • I have a code-first EF 4.5 model and I am having trouble inserting records into the database.

    I need to create multiple objects (Id's are populated using Guid.NewGuid), add them to the context and then call the SaveChanges method; but when I do so I get primary key violation exception.

    I did insert a breakpoint on SaveChanges and all Id's are set properly but when the SaveChanges is invoked (and I found that out using profiler) all Id's are set to the Guid for the first object.

    Thursday, June 21, 2012 11:09 AM

Answers

  • Hi Vahid Tavana,

    After researching, I find the error is caused by there're two primary keys in Article class. When you create the database, you will find the PK of the Article table is also the FK. The two articles are pointing to the same member, so the PK is duplicate. If there's only one PK in Article table, EF will help you to create a FK column references to Member table's PK.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    • Marked as answer by Vahid Tavana Monday, June 25, 2012 7:52 AM
    Monday, June 25, 2012 7:50 AM
    Moderator

All replies

  • Hi Vahid Tavana,

    Welcome to MSDN Forum.

    Could you please post the code here? This is, so I can help you more effectively.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Friday, June 22, 2012 3:07 AM
    Moderator
  • Thanks Allen

    I have two classes, Article and Member both derived from a class named EntityObject with an Id property of type Guid.

    and here is the code where the problem occurs:

    var member = new Member(){ Id = Guid.NewGuid(), Name = name, DateTime = DateTime.Now };
    var article = new Article(){Id = Guid.NewGuid(), Title = title, Content = content, DateTime = DateTime.Now };
    context.Members.Add(member);
    context.Articles.Add(article);
    context.SaveChanges();


    Friday, June 22, 2012 5:34 AM
  • Hi Vahid Tavana,

    The code seems no problem. I have written a test project to repro the issue, but it works well. Please refer to the code below.

    class Program
        {
            static void Main(string[] args)
            {
                using (testContext context = new testContext())
                {
                    var member = new Member() { id = Guid.NewGuid(), name = "test", time = DateTime.Now };
                    var article = new Article() { id = Guid.NewGuid(), title = "test", content = "test", time = DateTime.Now };
                    context.memberSet.Add(member);
                    context.articleSet.Add(article);
                    context.SaveChanges();
                }
            }
        }
    
        class Article
        {
            public Guid id { get; set; }
            public string title { get; set; }
            public string content { get; set; }
            public DateTime time { get; set; }
        }
    
        class Member
        {
            public Guid id { get; set; }
            public string name { get; set; }
            public DateTime time { get; set; }
        }
    
        class testContext : DbContext
        {
            public DbSet<Article> articleSet { get; set; }
            public DbSet<Member> memberSet { get; set; }
        }

    Best Regards

    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Friday, June 22, 2012 6:29 AM
    Moderator
  • Hi Allen,

    I checked your test case and it seems to work just fine so I made you a test case that does fail. I think it has something to do with key combination.

     class Program
        {
            static void Main(string[] args)
            {
                var context = new Context();
                var member = new Member() { Id = Guid.NewGuid(), Name = "some name", DateTime = DateTime.Now };
                var article = new Article() { Id = Guid.NewGuid(), Title = "some title", Content = "some content", Member = member, DateTime = DateTime.Now };
                var article2 = new Article() { Id = Guid.NewGuid(), Title = "some title", Content = "some content", Member = member, DateTime = DateTime.Now };
                context.Articles.Add(article);
                context.Articles.Add(article2);
                context.SaveChanges();
            }
        }
        public class Member
        {
            [Key]
            public Guid Id { get; set; }
            public string Name { get; set; }
            public DateTime DateTime { get; set; }
        }
        public class Article
        {
            [Key, Column(Order = 0)]
            public Guid Id { get; set; }
            [Key, Column(Order = 1)]
            public int Langauge { get; set; }
            public DateTime DateTime { get; set; }
            public string Title { get; set; }
            public string Content { get; set; }
            public virtual Member Member { get; set; }
        }
        public class Context : DbContext
        {
            public DbSet<Member> Members { get; set; }
            public DbSet<Article> Articles { get; set; }
    
            protected override void OnModelCreating(DbModelBuilder modelBuilder)
            {
                base.OnModelCreating(modelBuilder);
                Database.SetInitializer<Context>(new DropCreateDatabaseAlways<Context>());
            }
        }

    Thank you again

    Friday, June 22, 2012 7:44 AM
  • Hi Vahid Tavana,

    Yes, the code will not work. the 'member' has already assigned to 'article', and you assign it to 'article2' again. After calling SaveChanges, the 'member' will insert into database along with 'article', however, it will insert again along with 'article2', so the exception will be thrown.

    Based on this issue, please add a navigation property to the Member class, so that we can write code as below.

    var context = new Context();
    var member = new Member() { Id = Guid.NewGuid(), Name = "some name", DateTime = DateTime.Now };
    var article = new Article() { Id = Guid.NewGuid(), Title = "some title", Content = "some content", DateTime = DateTime.Now };
    var article2 = new Article() { Id = Guid.NewGuid(), Title = "some title", Content = "some content", DateTime = DateTime.Now };
    member.Articles.Add(article);
    member.Articles.Add(article2);
    context.Members.Add(member);
    context.SaveChanges();

    Best Regards

    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Friday, June 22, 2012 8:13 AM
    Moderator
  • I'm not sure if I got the navigation property part correctly from your response. You do understand there is a one-to-many relationship here between Member and Article so I really need the navigation property to be on Article side. 

    Do you mean I should also add an ICollection<Article> to Member side?

    Friday, June 22, 2012 9:11 AM
  • Hi Vahid Tavana,

    Yes, you should add an ICollection<Article> property to Member class.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Friday, June 22, 2012 1:25 PM
    Moderator
  • I did what you suggested but I still had the exception. I made the change bellow to the model builder and it is now working fine;

    class Program
        {
            static void Main(string[] args)
            {
                var context = new Context();
                var member = new Member() { Id = Guid.NewGuid(), DateTime = DateTime.Now, Name = "some member name" };
                var article = new Article() { Id = Guid.NewGuid(), Title = "some title", Content = "some content", DateTime = DateTime.Now, Member = member };
                var article2 = new Article() { Id = Guid.NewGuid(), Title = "some title", Content = "some content", DateTime = DateTime.Now, Member = member};
                context.Articles.Add(article);
                context.Articles.Add(article2);
                context.SaveChanges();
            }
        }
        public class Member
        {
            [Key]
            public Guid Id { get; set; }
            public string Name { get; set; }
            public DateTime DateTime { get; set; }
        }
        public class Article
        {
            [Key, Column(Order = 0)]
            public Guid Id { get; set; }
            [Key, Column(Order = 1)]
            public int Langauge { get; set; }
            public DateTime DateTime { get; set; }
            public string Title { get; set; }
            public string Content { get; set; }
            public virtual Member Member { get; set; }
        }
        public class Context : DbContext
        {
            public DbSet<Member> Members { get; set; }
            public DbSet<Article> Articles { get; set; }
    
            protected override void OnModelCreating(DbModelBuilder modelBuilder)
            {
                base.OnModelCreating(modelBuilder);
                modelBuilder.Entity<Article>().HasOptional<Member>(a=>a.Member).WithOptionalDependent();
                Database.SetInitializer<Context>(new DropCreateDatabaseAlways<Context>());
            }
        }
    I happened to learn without the line before the last line there is no foreign key field Member_Id in the Articles table; I feel this is kind of odd since with the Member property in the Article class I expect EF to know there should be a foreign key!

    Friday, June 22, 2012 6:01 PM
  • Hi Vahid Tavana,

    After researching, I find the error is caused by there're two primary keys in Article class. When you create the database, you will find the PK of the Article table is also the FK. The two articles are pointing to the same member, so the PK is duplicate. If there's only one PK in Article table, EF will help you to create a FK column references to Member table's PK.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    • Marked as answer by Vahid Tavana Monday, June 25, 2012 7:52 AM
    Monday, June 25, 2012 7:50 AM
    Moderator