Entity Framework5 Code First Exception "Duplicate entry '0' for key 'PRIMARY'"

Question

I did try to make sample by EF5 and MySQL.

[Table("agency")]
class Agency
{
    [Key]
    [Required]
    public int agency_id { get; set; }
    public string name { get; set; }
}

class MyContext : DbContext
{
    public DbSet<Agency> Agency { get; set; }
}

class Sample
{
    public static void Main()
    {
        using (var db = new MyContext())
        {
            var query = from x in db.Agency select x.agency_id;
            var new_num = query.DefaultIfEmpty<int>().Max(p => p == null ? 0 : p);

            new_num++;

            Agency a = new Agency
            {
                agency_id = new_num,
                name = "Test"
            };

            db.Agency.Add(a);
            db.SaveChanges();
        }
    }
}

At first. It was successful.
But at second time, It threw the exception in db.SaveChanges().
{"Duplicate entry '0' for key 'PRIMARY'"}

With the thrown exception, I traced variable.
agency_id had '1', certainly.

What was wrong?

Update 1

Update 2

!!!!???????????????????????????

score 2 · Accepted Answer · edited May 23 '17 at 12:01

2

Ok, I found a solution.

https://stackoverflow.com/a/6752692/415682

DatabaseGeneratedAttribute is point!!!

[Table("agency")]
class Agency
{
    [Required, Key, DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int my_id { get; set; }
    public string name { get; set; }
}

edited May 23 '17 at 12:01

Community

1
1

answered Mar 13 '13 at 12:06

Jaime

2,148
1
16
19

score 0 · Answer 2 · answered Mar 13 '13 at 10:08

0

Try this one:

        int new_num = 0;
        if (db.Agency.Any())
        {
            new_num = db.Agency.Max(a => a.agency_id) + 1;
        }

answered Mar 13 '13 at 10:08

Denys Denysenko

7,598
1
20
30

I thought that the problem is somwhere in querying for max identifier. Is db.Agency is empty in your case at second run? – Denys Denysenko Mar 13 '13 at 10:45
That's my guess but try to remore [Required] attribute from your agency_id property. – Denys Denysenko Mar 13 '13 at 11:20

score 0 · Answer 3 · answered Mar 13 '13 at 10:34

0

Probably the mapping is not correct. Make sure the name of the id in the database is 'agency_id'.

answered Mar 13 '13 at 10:34

L-Four

13,345
9
65
109

After truncated table, it was successful. agency_id is correct. – Jaime Mar 13 '13 at 10:44

score 0 · Answer 4 · answered Mar 13 '13 at 11:07

0

Why are you manually incrementing your primary key? I see you are using MySQL. Just make it an Auto Incremented column and be done with it. See here: http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html

Try this and stop manually assigning agency_id... It may solve your problems.

answered Mar 13 '13 at 11:07

Matt

6,787
11
65
112

I know. but It's just sample. I curious why it throw exception. – Jaime Mar 13 '13 at 11:10

Entity Framework5 Code First Exception "Duplicate entry '0' for key 'PRIMARY'"

4 Answers4