EF Core incorrectly doing multiple inserts for Array

Question

I'm still new with Entity Framework and I'm struggling with an issue while inserting records to the database on .NET Core API service I'm developing.

Summarizing what is going on, I'm not generating the schema from EF, instead I create the tables in SQL and just have EF access it.

I have a model objects similar to this:

[Table("events")]
public class Event
{
    [Key]
    [Required]
    [Column("id")]
    public Guid Id { get; set; }

    public string EventName { get; set; }

    public List<Session> Sessions { get; set; }
}

[Table("sessions")]
public class Session
{
    [Key]
    [ForeignKey("FK_events-sessions")]
    [Column("eventId")]
    public Guid EventId { get; set; }

    public byte Hour { get; set; }

    public byte Day { get; set; }

    public byte Month { get; set; }
}

Other then this, on the context class, I declare a one to many relationship: modelBuilder.Entity<Event>().HasMany(s => s.Sessions);

In the SQL DB, the table columns have matching names, but all start with lowercase letters.

The issue I'm having is that when I perform the POST operation, where an Event object with multiple session is added and saved, it throws an error:

Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while saving the entity changes. See the inner exception for details. ---> Microsoft.Data.SqlClient.SqlException (0x80131904): The column name 'eventId' is specified more than once in the SET clause or column list of an INSERT. A column cannot be assigned more than one value in the same clause. Modify the clause to make sure that a column is updated only once. If this statement updates or inserts columns into a view, column aliasing can conceal the duplication in your code.

I've enabled Information logging on EF and I see it doing this:

INSERT INTO [sessions] ([eventId], [Hour], [Day], [EventId], [Month])
VALUES (@p0, @p1, @p2, @p3, @p4);
      INSERT INTO [sessions] ([eventId], [Hour], [Day], [EventId], [Month])
      VALUES (@p0, @p1, @p2, @p3, @p4);

As you can see, it's adding the Id column twice for some reason. I wonder if the first one is my mapping that I annotate with [Column("eventId")] and the other one a generated one from the object Event.

Any idea?

Thank you.

To save your trouble, use scaffold-dbcontext command to generate dbcontext and models automatically for you. then everything will work for sure. and also make sure that your tables have relationships in db. — Asherguru
@heuristican Without it, it will do INSERT INTO [sessions] ([EventId], [Hour], [Day], [EventId1], [Month]) — Ralms
@Asherguru I've did some research and tried to compare what would be generated vs what I have, but I seem to be suffering from a bug: github.com/dotnet/efcore/issues/24251 — Ralms
Use stable NET 5. I used scaffold-dbcontext in NET 5. no issue. Preview/beta always have alot of bugs. — Asherguru

Ralms Ralms · Accepted Answer · 2021-04-03T03:46:45

I've fixed the issue.

My DB schema, had Sessions with its key being only the Foreign Key, which made the entries not 100% unique. It would allow to have duplicated entries.

For now, I've decided to change the primary key of Sessions to a composite key using EventId and Day.

My current OnModelCreating modelBuilder is the following:

modelBuilder.Entity<Session>()
  .HasOne(c => c.Event)
  .WithMany(s => s.Sessions)
  .HasForeignKey(k => k.EventId);

modelBuilder.Entity<Session>()
  .HasKey(k => new { k.EventId, k.Day});

Hope it helps other people in the future.

EF Core incorrectly doing multiple inserts for Array

1 Answers