2

I am trying to use a custom identity column in a table using Entity Framework. What I have is a table used to control the next available Id to use for each table.

CREATE TABLE [dbo].[SystemNumbers](
    [SystemNumberDesc] [varchar](30) NOT NULL,
    [LastId] [Int] NOT NULL
 CONSTRAINT [PK__SystemNumbers] PRIMARY KEY CLUSTERED 
(
    [SystemNumberDesc] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO

with a row containing a row counter for the table, inserted as follows

INSERT INTO SystemNumbers VALUES ('WS', 0)

I then create the table that I want to use

CREATE TABLE [dbo].[WS](
    [WorkstationID]  INT NOT NULL,
    [WorkstationName] [varchar](50) NULL,
    [WSTS] TIMESTAMP,
 CONSTRAINT [PK_WS] PRIMARY KEY CLUSTERED 
(
    [WorkstationID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO

and then create the trigger to get the next Id from the SystemCounters table and update the ID as follows

  CREATE TRIGGER WSId 
    ON  WS     AFTER INSERT     
    AS   BEGIN   
  DECLARE @LastId INT  
  DECLARE @TableName VARCHAR(100)   
    DECLARE @INSERTID INT
    SET @TableName = 'WS'   
  SET NOCOUNT ON   
    SELECT @INSERTID = SystemID, @CREATETS = SystemTS, @StoreId = StoreId FROM Inserted 
  IF (@INSERTID = 0)   BEGIN  
      SELECT @LastId = LastId + 1 FROM SystemNumbers WHERE SystemNumberDesc = @TableName    
      UPDATE SystemNumbers SET LastId = @LastId WHERE SystemNumberDesc = @TableName          
      UPDATE WS SET SystemId = @LastId, WorkstationID=@LastId WHERE SystemId = @INSERTID 
  END 
  END

GO

This works fine using SQL to perform the insert. I then add an ADO.Net Entity Framework to my project and attempt to insert a record.

This give me the initial problem that the column is defined as Computed and needs to be changed to Identity. I change this and then get

An unhandled exception of type 'System.Data.Entity.Infrastructure.DbUpdateConcurrencyException' occurred in EntityFramework.dll

Additional information: Store update, insert, or delete statement affected an unexpected number of rows (0). Entities may have been modified or deleted since entities were loaded. Refresh ObjectStateManager entries.

I suspect this is because it inserts the record and then attempts to reload it to get the Db values such as the Timestamp.

I have followed a number of thread but just ended up in a circle. I need to set the column from the table as it is multi-store and so an Identity field will not work.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Maurice
  • 21
  • 1
  • 2
    Your trigger has a **massive flaw** in that you seem to assume the trigger is called **once per row** - this is **NOT** the case with SQL Server. If your `INSERT` or `UPDATE` affects multiple rows, the trigger will be called **once per statement**, and the pseudo tables `Inserted` and `Deleted` can contain multiple rows. So which of these rows are you selecting when doing `select @id1 = i.id from inserted i;` ??? It's not defined - you get **one, arbitrary** row and all other are ignored.... you need to re-write your trigger to handle **multiple rows** in `Inserted` and `Deleted` ! – marc_s Jul 13 '14 at 17:20
  • I will take this into account in the final version as I have a Timestamp column which I use in the WHERE clause of the update. At the moment the table is empty and I can be confident that only 1 record is being inserted. - Cheers Mark – Maurice Jul 13 '14 at 18:48
  • I don't think you can work around this. Some alternatives: http://stackoverflow.com/q/24162895/861716, http://stackoverflow.com/q/20469208/861716 – Gert Arnold Jul 13 '14 at 19:47
  • There's nothing to work around. Looking at the code, you're trying to generate a unique series of integer values based on table name. You can just as well create an identity key for those tables and let the system do all the work for you. – TommCatt Jul 14 '14 at 19:03
  • Hi TommCatt, you didn't read the post. I need to generate the Id as there will at some stage be 2 databases merged together so when you merge the data the id start creating gaps. As part of this there is a DbId which is prefixed to the Id to ensure it is unique. – Maurice Jul 14 '14 at 20:17
  • Hi Gert, this looks exactly what I was looking for, the way I wanted to achieve it was to intercept the insert and do my magic in there. I will let you know how I get on. – Maurice Jul 14 '14 at 20:18

0 Answers0