blazor - Writing new EF core entities does not use the auto_increment but writes 0 value as ID

Question

I'm currently programming a modal to add some basic information to print an invoice with that information later on. The code is still messy but as soon as i figure out how to solve my problem, I'm going to smarten up the code a little bit.

I'm currently struggling in creating some input fields that are used to add or remove the items of the invoice. Currently it looks like that:

When I open that modal, I retrieve the OrderSpecifications (that's what I call these lines) from the DB and populate the input fields.

 protected override void OnInitialized()
            {
                specs = nfzContext.OrderSpecifications.Where(x => x.FkOrderNumber == order.Id).ToList();
                numberOfSpecLines = nfzContext.OrderSpecifications.Where(x => x.FkOrderNumber == order.Id).Count();
                SetupSpeclines();
            }

I have 5 input fields predefined, which are only hidden in case there are no specification lines already existing. If i press the + button, I show the a new line.

<div class="card-body">
        <div class="form-group">
            <div class="row">
                <div class="col">
                    <input class="form-control" type="text" @bind="specification1.ItemName" hidden="@specLine1Disabled" placeholder="Zeile 1" />
                </div>
            </div>
            <div class="row">
                <div class="col">
                    <input class="form-control" type="text" @bind="specification2.ItemName" hidden="@specLine2Disabled" placeholder="Zeile 2" />
                </div>
            </div>
        </div>
    </div>

The SetupSpecline method grabs the existing speclines and adds a reference for each to one of the five specification1 ... specification5 variables:

void SetupSpeclines() {
  if (numberOfSpecLines <= 1) {
    specLine1Disabled = false;
    if (numberOfSpecLines == 1) specification1 = specs.ElementAt(0);

    numberOfVisibleSpecLines = 1;
  }
  else if (numberOfSpecLines == 2) {
    specLine1Disabled = false;
    specLine2Disabled = false;
    specification1 = specs.ElementAt(0);
    specification2 = specs.ElementAt(1);

    numberOfVisibleSpecLines = 2;
  }
  else if (numberOfSpecLines == 3) {
    specLine1Disabled = false;
    specLine2Disabled = false;
    specLine3Disabled = false;

    specification1 = specs.ElementAt(0);
    specification2 = specs.ElementAt(1);
    specification3 = specs.ElementAt(2);

    numberOfVisibleSpecLines = 3;
  }
  else if (numberOfSpecLines == 4) {
    specLine1Disabled = false;
    specLine2Disabled = false;
    specLine3Disabled = false;
    specLine4Disabled = false;
    specification1 = specs.ElementAt(0);
    specification2 = specs.ElementAt(1);
    specification3 = specs.ElementAt(2);
    specification4 = specs.ElementAt(3);

    numberOfVisibleSpecLines = 4;
  }
  else if (numberOfSpecLines == 5) {
    specLine1Disabled = false;
    specLine2Disabled = false;
    specLine3Disabled = false;
    specLine4Disabled = false;
    specLine5Disabled = false;

    specification1 = specs.ElementAt(0);
    specification2 = specs.ElementAt(1);
    specification3 = specs.ElementAt(2);
    specification4 = specs.ElementAt(3);
    specification5 = specs.ElementAt(4);

    numberOfVisibleSpecLines = 5;
  }
}

This it the database model for OrderSpecification (ID = primary key):

namespace MyNamespace
{
    public class OrderSpecification
    {
        public OrderSpecification();

        public int Id { get; set; }
        public int FkOrderNumber { get; set; }
        public int SeqId { get; set; }
        public string ItemName { get; set; }
        public virtual Order FkOrderNumberNavigation { get; set; }
    }
}

You can unhide (+) up to five inputs and enter some data. After you press the OK button, the routine starts to check if individual lines have a) altered (=ItemName changed), if new ones were added or if some were removed (=empty input):

void Confirm()
            {
                List<OrderSpecification> linesToAdd = new List<OrderSpecification>();
                List<OrderSpecification> linesToRemove = new List<OrderSpecification>();

                if (!string.IsNullOrEmpty(specification1.ItemName))
                {
                    // Check if there is a spec at index 0
                    if (specs.ElementAtOrDefault(0) != null)
                    {
                        specs.ElementAtOrDefault(0).ItemName = specification1.ItemName; // Only itemName has changed
                    }
                    else
                    {   // Add new line
                        linesToAdd.Add(new OrderSpecification { FkOrderNumber = order.Id, ItemName = specification1.ItemName, SeqId = 1 });
                    }
                }
              

else if (!string.IsNullOrEmpty(specification1.ItemName) && specs.ElementAtOrDefault(0) != null)

Now, while all that works just fine, I have trouble writing the new speclines to the database. For example, When i run

    foreach (var spec in LinesToAdd)
    {
        nfzContext.Add(spec);
    }

nfzContext.SaveChanges(); 

I get the error message

{"Cannot insert explicit value for identity column in table 'OrderSpecifications' when IDENTITY_INSERT is set to OFF."}

What I assume is that EF Core tries to add the new OrderSpecification with the ID=0, which is the standard value when creating a new OrderSpecification element. I need to tell EF Core to not write the ID as 0 but to let the database set the value by using auto_increment.

And what's odd is, although I have assigned the Primary Key to the ID field, when I scaffold, the key is not set in the modelbuilder:

modelBuilder.Entity<OrderSpecification>(entity =>
            {
                entity.ToTable("OrderSpecifications", "samnfz");

                entity.Property(e => e.Id).HasColumnName("ID");

                entity.Property(e => e.FkOrderNumber).HasColumnName("FK_OrderNumber");

                entity.Property(e => e.ItemName).IsRequired();

                entity.Property(e => e.SeqId).HasColumnName("SeqID");

                entity.HasOne(d => d.FkOrderNumberNavigation)
                    .WithMany(p => p.OrderSpecifications)
                    .HasForeignKey(d => d.FkOrderNumber)
                    .OnDelete(DeleteBehavior.ClientSetNull)
                    .HasConstraintName("FK_OrderSpecifications_Orders");
            });

Any idea?

question from:https://stackoverflow.com/questions/65844051/writing-new-ef-core-entities-does-not-use-the-auto-increment-but-writes-0-value

Answer 1

Ugh, I think I have found the error. After analyzing the table structure in the modelbuilder, I recognized that the structure is not the same that I have in my database. So i scaffolded once again and the error is gone. obviously, I used model types that were not current and maybe the primary key was set to another attribute...