Breeze Web API, strange ID's

Question

                Hi all,

Environment : VS express 2013 for web, project template : hot towel angular (1.2), entity-framework ado .net 6, sql server 2012, Breeze.WebApi2, Breeze.ContextProvider.EF6.

In my little webApp angular - breeze, strange things are happening. I have a little function in the web site that is adding Missions in the database. It's working correctly.

To do that I have a controller.

The controller has been generated automaticakly, on controller's folder > add a controller, I choose the 2nd type of controller to have Breeze Web API Controller. The code generated :

[ResponseType(typeof(Mission))]
[System.Web.Http.AcceptVerbs("POST")]
public async Task<IHttpActionResult> PostMission(Mission mission)
{
    try
    {
        mission.UserId = 1;
        mission.RoleId = 1;

        mission.LibelleMission = mission.LibelleMission.Replace("\n", "<br/>");
        mission.Projet = mission.Projet.Replace("\n", "<br/>");
        mission.EnvironnementTechnique = mission.EnvironnementTechnique.Replace("\n", "<br/>");

        db.Missions.Add(mission);
        await db.SaveChangesAsync();

        return CreatedAtRoute("DefaultApi", new { id = mission.MissionId }, mission);
    }
    catch (Exception e)
    {
        return CreatedAtRoute("DefaultApi", new { id = mission.MissionId }, mission);
    }
}

The missions are correctly inserted in data base.

The primary key of the mission in Data Base is MissionId, it's an int, identity, starting from 1 and incremented of 1.

I have in the table these values :

MissionId   ClientId    UserId
1   28  1
2   28  1
3   2   1
4   28  1
5   5   1
6   9   1
7   6   1
1006    6   1
1007    10  1
1008    10  1
2006    11  1
2007    32  1
2008    6   1
2009    17  1
2010    6   1
2011    12  1
2012    2   1
2013    8   1
2014    6   1
2015    5   1
2016    11  1
2017    15  1
2018    7   1
2019    10  1
3016    6   1
4016    13  1
4017    18  1
4018    7   1
4019    16  1
4020    2   1
4021    4   1
4022    3   1

As you can see, the MissionId's are not (always) consecutives, spending from 7 to 1006, or from 1008 to 2006.

Have you any idea of this strange behavior ? To correct this ?

Thanx for your help.

Possible duplicate of identity increment is jumping in sql server database — Martin Smith

lysaghtm lysaghtm · Accepted Answer · 2014-03-20T09:43:12

It sounds like an issue answered in this question identity increment is jumping in sql server database question, see the answer from Martin Smith

SQL Server 2012 now uses a cache size of 1,000 when allocating IDENTITY values and restarting the service "loses" unused values.

See Martin's answer for a suggested workaround

If you do want the old behaviour you can set SQL Server to startup with trace flag 272 or you can use a SEQUENCE instead of an IDENTITY and set the Sequence to have a cache size of 0

Breeze Web API, strange ID's

1 Answers