CODEWITHSUNDEEP
c#sql-serverentity-frameworkauto-increment
Zapnologica
Zapnologica

Reputation: 22556

PK Contraint with insert in EF and Auto Increment field

I have had code running for about a week now and all of a sudden it is giving me an error on db.saveContext(). It is inserting into a table and the PK is an Identity field with auto increment enabled.

Now i am gettign an exception in code saying: {"Violation of PRIMARY KEY constraint 'PK_Acks'. Cannot insert duplicate key in object 'dbo.Acks'. The duplicate key value is (12685028).\r\nThe statement has been terminated."}

This seems really odd to me that it would all of a sudden become an issue.

here is my code:

  if (logBufferCounter++ > 1000)
            {
                logBufferCounter = 0;
                db.SaveChanges();
                recreateContext();
            }

I have also tried removing the 1000 batch update for debugging. And still the same error. I have also removed recreateContext() and still same error.

There is only one instance of this code running.

What could be the issue? It seems as if I have stumbled into some lower level error.

Here is how I create my object:

  Ack tableItem = new Ack();
        try
        {
            tableItem.messageId = logUtil.getMessageId(tmpPacket);
            tableItem.serverId = logItem.serverId;
            tableItem.time = logUtil.getTime(tmpPacket.getValueOfTag(tcp_packet.pck_tag_types.TM));
            int mr = Convert.ToInt32(tmpPacket.getValueOfTag(tcp_packet.pck_tag_types.MR));
            tableItem.direction = logUtil.getDirection(mr);
            tableItem.isNack = !tmpPacket.is_ack();
            tableItem.unitId = logUtil.getAckUnitId(tmpPacket);

            // Calculate The Client Id
            tcp_packet.pck_item cl;
            if (tmpPacket.find(tcp_packet.pck_tag_types.CL, out cl))
            {
                tableItem.clientId = cl.value;
            }
            else
            {
                tableItem.clientId = null;
            }             

            tableItem.data = tableItem.data.Substring(0, 50);


            // Save to db
            db.Acks.Add(tableItem);
        }
        catch (Exception e)
        {
            log.Error("Could not build tmpItem for an ack packet: " + logItem.payload, e);
        }

I am using EF 6.1 Databse First with sql server 2012.

What value should you initialize the auto increment key / value to?

[EDIT] Ack model class:

public partial class Ack
{
    public long ackId { get; set; }
    public long messageId { get; set; }
    public byte serverId { get; set; }
    public System.DateTime time { get; set; }
    public bool direction { get; set; }
    public bool isNack { get; set; }
    public string unitId { get; set; }
    public string clientId { get; set; }
    public string data { get; set; }

    public virtual Server Server { get; set; }
}

|Here is the table in sql manager enter image description here

Upvotes: 0

Views: 1159

Answers (1)

JotaBe
JotaBe

Reputation: 39004

Sometimes, for some unexpected reason, the SEED of an IDENTITY column can get corrupted. As you say it's been working and you're getting errors lately, I suppose this is what have happened (unless you changed anything else!).

If so, you should try running this in your DB:

DBCC CHECKIDENT('SchemaName.TableName', NORESEED)

this will so the current seed (the next generated value is seed + 1)

If the next value exists in the DB you must change the seed using this command

DBCC CHECKIDENT('SchemaName.TableName', RESEED)

For more info, see this: DBCC CHECKIDENT.

Upvotes: 1

Related Questions