0 votes
1 view
in Azure by (33.1k points)
edited by

Currently working on an ASP.Net MVC 4 application using Entity Framework 5. Used CodeFirst for the initial development phase. But have now disabled the Automatic Migrations and designing new tables directly using SSMS and writing POCO. Everything is working good.

Recently, identified a weird issue in Production. The records in one of the initially designed tables skipped auto-increment identity value by more than 900 numbers. This has happened 3 times within the last 3 months. Debugged the application locally but could not reproduce. There isn't any pattern or trend observed.

Model:

public class Enquiry

{

    [Key]

  [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]

    public Int64 EnquiryId { get; set; }

    [Required]

    public int UserId { get; set; }

    [Required]

    public byte Bid { get; set; }

    ...

    [Required]

    public DateTime Created { get; set; }

    [Required]

    public DateTime Modified { get; set; }

}

public class EnquiryDetail

{

    [Key]

    public Int64 EnquiryId { get; set; }

    [Required]

    public int CreditScore { get; set; }

    [Required]

    public byte BidMode { get; set; }

    public virtual Enquiry Enquiry { get; set; }

}

DBContext:

public class EscrowDb : DbContext

{

    public EscrowDb()

        : base("name=DefaultConnection")

    {

    }

    public DbSet<Enquiry> Enquiries { get; set; }

    public DbSet<EnquiryDetail> EnquiryDetails { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)

    {

        modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();

        modelBuilder.Entity<EnquiryDetail>()

            .HasRequired<Enquiry>(ed => ed.Enquiry)

            .WithRequiredDependent(e => e.EnquiryDetail);

    }

}

Controller:

[Authorize]

public class EnquiryController : Controller

{

    private EscrowDb _db = new EscrowDb();

    [HttpPost]

    [ValidateAntiForgeryToken]

    public ActionResult Create(EnquiryViewModel core)

    {

       var enquiry = new Enquiry();

       // Some code to set properties using passed ViewModel

       ...

       var enquiryDetail = new EnquiryDetail();

       // Some code to set properties using passed ViewModel

       ...

       enquiry.EnquiryDetail = enquiryDetail;

       _db.Enquiries.Add(enquiry);

       _db.SaveChanges();

    }

}

All this code has been working fine so far except the identity value getting skipped sporadically by large gaps of almost 1000 numbers.

Has anybody come across such kind of issue? Please share your thoughts.

1 Answer

0 votes
by (16.8k points)

Try to reseed the approach for triggering.

Check this link to know more: https://docs.microsoft.com/en-us/collaborate/connect-redirect.

USE [TEST]

CREATE TABLE TEST(ID INT IDENTITY(1,1),VAL VARCHAR(10))

CREATE TRIGGER TGR_TEST_IDENTITY ON TEST

FOR INSERT

AS

DECLARE @RESEEDVAL INT

SELECT @RESEEDVAL = MAX(ID) FROM TEST

DBCC CHECKIDENT('TEST', RESEED, @RESEEDVAL)

INSERT INTO TEST(VAL)VALUES('a')

SELECT * FROM TEST

'DBCC CHECKIDENT' is not supported in Azure anymore.

  1. Use GUID as key when using auto key of your SqlAzure
  2. If integer key like my case let the record insert and go back and delete it and re insert it with the right key by Turing off identity with set identity_insert XXXTable on -- this basically turns off IDENTITY
  3. And, then turning back on identity again when i am through with the insertion with the right key using

set identity_insert XXXTable off --this basically turns on IDENTITY

Note: this is not a good solution for a table that is receiving a massive insert request but might be useful for someone looking for a temporary way out

Related questions

Welcome to Intellipaat Community. Get your technical queries answered by top developers !


Categories

...