Question: How to perform Identity Inserts when inserting an entity?

[pmarflee]

I understand that RepoDB does not natively support the SQL Server SET IDENTITY_INSERT ON | OFF operation because it is vendor-specific. However, if I have manually enabled Identity Insert on the current connection, how can I actually set the identity value when inserting an entity into my SQL Server database? I'm getting the following error when performing the insert:

Microsoft.Data.SqlClient.SqlException
  HResult=0x80131904
  Message=Explicit value must be specified for identity column in table '<TableName>' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column.
  Source=Core Microsoft SqlClient Data Provider

I can see that RepoDB runs a query to obtain the column metadata the first time an entity is used. One of the metadata values returned by this query is 'IsIdentity'. I'm guessing that this metadata value is then used to determine whether an explicit value for a field should be included in the INSERT statement sent to the database.

Is there a way to tell RepoDB to override the default behaviour for a specific operation and allow identity inserts?

[mikependon]

Hmmmm... A very interesting question. First, RepoDB does not understand that the underlying database IDENTITY INSERT setting has been turned ON/OFF. As RepoDB is eliminating the column in the push operations (Insert, Merge) if it is set as identity in the table, it therefore triggers that problem.

Have you tried inserting via anonymous types?

Let us say you have table like below.

CREATE TALBE [schema].[Table]
(
    [Id] INT PRIMARY IDENTITY (1, 1),
    [Name] NVARCHAR(256),
    [Description] NVARCHAR(MAX)
);

Then, you can insert all the data using the code below. (Without using any class model)

using (var connection = new SqlConnection(connectionString))
{
     connection.ExecuteNonQuery("SET IDENTITY_INSERT [schema].[Table] ON;");
     var entities = GetEntitiesAsAnonymousTypes();
     connection.InsertAll("[schema].[Table]", entities);
     connection.ExecuteNonQuery("SET IDENTITY_INSERT [schema].[Table] OFF;");
}

In which the method GetEntitiesAsAnonymousTypes() returns the list of entities as anonymous types.

public List<EntityModel> GetEntitiesAsAnonymousTypes()
{
     for (var i = 0; i <= 100; i++)
     {
          yield return new { Id = (i + 1), Name = "Name", Description = "Description };
     }
}

Note: The code above is adhocly written and is not tested, but it should work if corrected any wrong typos.

[pmarflee]

Thanks for replying. I believe I did end up using anonymous types to work around this problem when I encountered it last week. I'll continue to use this approach if it isn't possible to use defined types.

On Thu, 16 Mar 2023, 20:30 Michael Camara Pendon, @.***> wrote:

Hmmmm... A very interesting question. First, RepoDB does not understand that the underlying database IDENTITY INSERT setting has been turned ON/OFF. As RepoDB is eliminating the column in the push operations (Insert, Merge) if it is set as identity in the table, it therefore triggers that problem.

Have you tried inserting via anonymous types?

Let us say you have table like below.

CREATE TALBE [schema].[Table] ( [Id] INT PRIMARY IDENTITY (1, 1), [Name] NVARCHAR(256), [Description] NVARCHAR(MAX) );

Then, you can insert all the data using the code below. (Without using any class model)

using (var connection = new SqlConnection(connectionString)) { connection.ExecuteNonQuery("SET IDENTITY_INSERT [schema].[Table] ON;"); var entities = GetEntitiesAsAnonymousTypes(); connection.InsertAll("[schema].[Table]", entities); connection.ExecuteNonQuery("SET IDENTITY_INSERT [schema].[Table] OFF;"); }

In which the method GetEntitiesAsAnonymousTypes() returns the list of entities as anonymous types.

public List GetEntitiesAsAnonymousTypes() { for (var i = 0; i <= 100; i++) { yield return new { Id = (i + 1), Name = "Name", Description = "Description }; } }

Note: The code above is adhocly written and is not tested, but it should work if corrected any wrong typos.

— Reply to this email directly, view it on GitHub https://github.com/mikependon/RepoDB/issues/1135#issuecomment-1472697590, or unsubscribe https://github.com/notifications/unsubscribe-auth/AACJYYFMZVEMFLD65BJCOFLW4NZ77ANCNFSM6AAAAAAVTYBJ24 . You are receiving this because you authored the thread.Message ID: @.***>