none
Identity Column Insert

    Question

  • Hello,

    I have what seems like a simple issue and I seem to be missing something about it.

    I have a Silverlight application which is leveraging RIA services. My database has a table named Adjustment_Action. The Adjustment_Action table has a column named Id. The Id column is an identity column (generated from teh DB) and it is the tabled primary key. I generated an entity data model from the database. The entity day model has an object called Adjustment_Action. The Adjustment_Action object has a property named Id which is identified as the Key for the entity and is also set to Identity for its Store Generated Pattern in the .edmx designer.

    Whenever I try to add a new Adjustment_Action object to the data context I must specify the Id or I get a duplicate entity exception. The Id column to setup as an identity column in the edmx file. Is there another step I should be taking?

    There are other relations that I have omitted since setting the Id column solved the issue I feel the issue deals with that specific constrain. I can include a more complete model if needed. The only relation which may impact this is the AdjustmentId which references a separate entity called Adjustment. The Adjustment_Action has a foreign key to this object and the Adjustment is loaded into the same data context as Adjustment_Action.

     C# Code

    //Fails
    var adjustmentAction = new Adjustment_Action();
    _dataContext.Adjustment_Actions.Add(adjustmentAction);
    
    //Works - Id column is set correctly after save, set to random number before save
    var adjustmentAction = new Adjustment_Action();
    adjustmentAction.Id = new Random().Next();
    _dataContext.Adjustment_Actions.Add(adjustmentAction);

     SQL Code

    CREATE TABLE [dbo].[Adjustment_Action](
    	[Id] [int] IDENTITY(1,1) NOT NULL,
    	[AdjustmentId] [int] NOT NULL,
    	[CreatedOn] [smalldatetime] NOT NULL,
    	[CreatedBy] [varchar](50) NOT NULL,
    	[ModifiedOn] [smalldatetime] NOT NULL,
    	[ModifiedBy] [varchar](50) NOT NULL,
     CONSTRAINT [PK_AdjustmentType_ent_Rules_1] PRIMARY KEY CLUSTERED ([Id] ASC)
    
    ALTER TABLE [dbo].[Adjustment_Action]  WITH CHECK ADD  CONSTRAINT [FK_AdjustmentType_ent_Rules_AdjustmentType_ent_Master] FOREIGN KEY([AdjustmentId])
    REFERENCES [dbo].[Adjustment] ([Id])
    
    CREATE TABLE [dbo].[Adjustment](
    	[Id] [int] IDENTITY(1,1) NOT NULL,
    	[Name] [varchar](50) NOT NULL,
    	[ModifiedOn] [smalldatetime] NOT NULL,
    	[ModifiedBy] [varchar](200) NOT NULL,
     CONSTRAINT [PK_AjustmentType_ent_Master] PRIMARY KEY CLUSTERED ([Id] ASC)  
      

    Thanks,

    Greg

    Tuesday, July 06, 2010 12:01 PM

Answers

  • Hello Again,

    Thanks for your comments they were helpful in confirming this should work.

    After more debugging I determined that the Adjustment_Action table was not always an identity based table. There was an adjustment_action in the table with an Id of 0 that existed in the database. Because this adjustment_action had an Id of 0 existed in the database as a persisted entity the default Id of 0 would not work.

    This is why the random number worked... I further confirmed this when I tried a -1 and was able to add multiple records.

    In the end I moved the adjustment_action with an Id of 0 to the next available id and reseeded the table to the correct new value. Thanks for your help.

    Monday, July 12, 2010 10:53 AM

All replies

  • A table named Adjustment_Action has a column called Id which is a primary key and identify column.

     

    Do you mean the Id is an Identity field which is generated in DB? If yes, then you should not provide the value for this field. It should be generated from DB when you insert a new record.

    Even this Id is not generated from DB, you can not use Random generator to generate the ID and assign to this PK field, how can you guarantee the uniqueness of this ID if you use a Random generator? 

     

    Tuesday, July 06, 2010 12:35 PM
  • Since Adjustment_Action need AdjustmentId (not null field), you might need to set AdjustmentId before you save it. I don't see you set this AdjectmentId in your code and I don't know why you don't get an error when you submit the changes.

    But Id is generated field, I don't see why you have to set it with some random number before you add it to the _dataContext.Adjustment_Actions collection. I use identity ID fields for my tables all the time, and I never have to set the ID when I create a new entity. 

     

    Tuesday, July 06, 2010 3:54 PM
  • Sorry if I wasn't clear in my post. I updated it and included the SQL that the model is generated from.

    The Id is an Identity field and is generated from the DB. The issue I am running into is on the Silverlight client when I add a record to the data context I get an exceptionof the following [An entity with the same identity already exists in this EntitySet.].When I look into the collection I am adding the entity to there is an entity with an id of 0. The new entity which I am trying to add also has an Id of zero on the client. If I set the id of the new entity to a value not already in the clients collection, when I hit submit changes the id is the correct id from the database.

    Example:

    var adjustment = SelectedAdjustment;
    var adjustmentAction = new Adjustment_Action();
    adjustmentAction.AdjustmentId = adjustment.Id;
    _dataContext.Adjustment_Actions.Add(adjustmentAction);
    
     
    Tuesday, July 06, 2010 3:59 PM
  • When I look into the collection I am adding the entity to there is an entity with an id of 0. The new entity which I am trying to add also has an Id of zero on the client.

     

    That should fine. If the ID is generated in DB, you should not have to set anything (default is 0 because this is a int field). I have no idea why you got Duplicated Key error. 

    I just tested in my code. I have Product entity with ID generated from DB. I just did the following and two new products added to the table:

                context.Products.Add(new Product()); 
                context.Products.Add(new Product());
                context.SubmitChanges();

     

     

    Tuesday, July 06, 2010 4:44 PM
  • Hello Again,

    Thanks for your comments they were helpful in confirming this should work.

    After more debugging I determined that the Adjustment_Action table was not always an identity based table. There was an adjustment_action in the table with an Id of 0 that existed in the database. Because this adjustment_action had an Id of 0 existed in the database as a persisted entity the default Id of 0 would not work.

    This is why the random number worked... I further confirmed this when I tried a -1 and was able to add multiple records.

    In the end I moved the adjustment_action with an Id of 0 to the next available id and reseeded the table to the correct new value. Thanks for your help.

    Monday, July 12, 2010 10:53 AM