Question: How to perform Identity Inserts when inserting an entity? #1135
Assignees
Labels
question
Further information is requested
Comments
|
Hmmmm... A very interesting question. First, RepoDB does not understand that the underlying database 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 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. |
|
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<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.
—
Reply to this email directly, view it on GitHub
<#1135 (comment)>,
or unsubscribe
<https://github.com/notifications/unsubscribe-auth/AACJYYFMZVEMFLD65BJCOFLW4NZ77ANCNFSM6AAAAAAVTYBJ24>
.
You are receiving this because you authored the thread.Message ID:
***@***.***>
|
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
I understand that RepoDB does not natively support the SQL Server
SET IDENTITY_INSERT ON | OFFoperation 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: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?
The text was updated successfully, but these errors were encountered: