Can't save Data into Db because Identity Insert set to off

Question

I've looked at several questions with this problem but many of them can be solved with a quick class annotation, but I'm using metadata for my class files so I have to use ModelBuilder to configure the relationships in my Database from classes. I already know what the problem is, my table can't have Id data inserted into it, but I'm not sure how to change that in this current scenario as I'm using metadata. I will go through my code below to show you why this is different.

Here is my code.

public class CrayonDbContext : DbContext
        {
            private const string connectionString = @"myserver";
    
            protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
            {
                optionsBuilder.UseSqlServer(connectionString);
            }
    
            public DbSet<BillingStatement> BillingStatements { get; set; }
    
            protected override void OnModelCreating(ModelBuilder modelBuilder)
            {
    
                modelBuilder.Entity<BillingStatement>().OwnsOne<Price>(s => s.TotalSalesPrice);
                modelBuilder.Entity<BillingStatement>().OwnsOne<ObjectReference>(s => s.InvoiceProfile);
                modelBuilder.Entity<BillingStatement>().OwnsOne<ObjectReference>(s => s.Organization);

                base.OnModelCreating(modelBuilder);
            }
        }

Here is the BillingStatement class from the metadata that it's using from a CrayonApi NuGet Package here.

namespace Crayon.Api.Sdk.Domain.Csp
{
    public class BillingStatement
    {
        public BillingStatement();

        public int Id { get; set; }
        public Price TotalSalesPrice { get; set; }
        public ObjectReference InvoiceProfile { get; set; }
        public ObjectReference Organization { get; set; }
        public DateTimeOffset StartDate { get; set; }
        public DateTimeOffset EndDate { get; set; }
        public ProvisionType ProvisionType { get; set; }
    }
}

Now here are the referenced classes from the previously shown billing statement class.

public class Price
{
    public Price();

    public decimal Value { get; set; }
    public string CurrencyCode { get; set; }
}

public class ObjectReference
{
    public ObjectReference();

    public int Id { get; set; }
    public string Name { get; set; }
}

public enum ProvisionType
{
    None = 0,
    Seat = 1,
    Usage = 2,
    OneTime = 3,
    Crayon = 4,
    AzureMarketplace = 5
}

This is the current table that's being generated.

CREATE TABLE [BillingStatement] (
      [Id] int NOT NULL IDENTITY,
      [TotalSalesPrice_Value] decimal(18,2) NULL,
      [TotalSalesPrice_CurrencyCode] nvarchar(max) NULL,
      [InvoiceProfile_Id] int NULL,
      [InvoiceProfile_Name] nvarchar(max) NULL,
      [Organization_Id] int NULL,
      [Organization_Name] nvarchar(max) NULL,
      [StartDate] datetimeoffset NOT NULL,
      [EndDate] datetimeoffset NOT NULL,
      [ProvisionType] int NOT NULL,
      CONSTRAINT [PK_BillingStatement] PRIMARY KEY ([Id])
  );

The issue now, is that I can't save API data into the Db. This is my main class for billing statement, that takes the API data through the Crayon NuGet Package and I want it to store into an SQL server.

private static void BillingStatement()
{
var billingStatements = ApiClient.BillingStatements.Get(GetToken(), new BillingStatementFilter { OrganizationId = organization.Id }).GetData().Items;
using (var db = new CrayonDbContext())
            {
                db.BillingStatements.AddRange(billingStatements);
                db.SaveChanges();
            }
}

var billingStatements is carrying the data correctly, I can see that through debug mode. The data has also scaffolded correctly, however when I run the application and attempt to save that API data to the Db, I get this error

"Inner Exception 1: SqlException: Cannot insert explicit value for identity column in table 'BillingStatements' when IDENTITY_INSERT is set to OFF."

I can't find a way to rectify this in my current instance. I've tried adding a "db.Database.ExecuteSqlRaw("SET IDENTITY_INSERT dbo.BillingStatement ON");" but I get an error with the "ExecuteSqlRaw" for some reason Visual Studio doesn't recognise it. Is there any way I can fix this problem I'm having in my code so I can save the data into a table? Any help would be appreciated, thank you.

You can use the .ValueGeneratedOnAdd() in your modelBuilder - see https://learn.microsoft.com/en-us/ef/core/modeling/generated-properties?tabs=fluent-api — Steve Ford, Jan 20 '21 at 13:50
@SteveFord Lol after writing such a big problem out it was really something that simple, thank you very much Steve, I appreciate it. — KyleAT, Jan 20 '21 at 14:33

score 1 · Answer 1 · answered Jan 20 '21 at 14:34

Thanks to Steve Ford in the comments for the answer.

It turns out I can add a ModelBuilder solution. I implemented this on my DbContext:

modelBuilder.Entity().Property(b => b.Id).ValueGeneratedNever();

And it works, thank you. To anyone else having a similar problem, try that solution.

Can't save Data into Db because Identity Insert set to off

1 Answers1