0

I have entity with foreign keys Project:

public class Project : IEntity
{
    public int Id { get; set; }

    public int? TypeId { get; set; }
    public virtual Type? Type { get; set; } = null!;

    // other properties...
}

I want to create a new object (insert a new entity into the database):

await Context.Set<TEntity>().AddAsync(entity);

And I have the following problem:

Microsoft.Data.SqlClient.SqlException (0x80131904): It is not possible to insert an explicit value for the identifiers column in the "Types" table when the IDENTITY_INSERT parameter is set to OFF.

So, when trying to execute this SQL query, I have an exception:

SET NOCOUNT ON;
INSERT INTO [Types] ([Id], [Name])
VALUES (@p0, @p1);

There's no need to create a new Type in the Types table. How can I fix this? I understand that I can set IDENTITY_INSERT to OFF, but I think this isn't a good practice.

Drompai
  • 111
  • 1
  • 7
  • you want set id as both auto and manual, but this is not allowed because is not safe. You should prefer only one. Maybe sql sequence might be help in your case. see : [link](https://stackoverflow.com/questions/27077461/how-to-get-next-value-of-sql-server-sequence-in-entity-framework) – Okan Karadag Sep 03 '22 at 10:59

1 Answers1

0

Have you tried to execute an SQL query?

SET IDENTITY_INSERT Types ON;

INSERT INTO Types ([Id], [Name])
VALUES ...

SET IDENTITY_INSERT Types OFF;

Can you share an example of your SQL schema?

I think you need to configure the relationship as optional Required and optional relationships either via the fluent API, or with an explicit Nullable<> foreign key property

komluk
  • 437
  • 4
  • 18
  • No, I haven't tried. This is a query generated by EF. I believe your query will be executed successfully, but it doesn't need to be checked, because my question was how to make sure that this query doesn't exist at all, since I need to add Projects, not Types. – Drompai Aug 31 '22 at 11:18