Adding an entity in Entity Framework with nested entities

Question

I am running into an interesting issue when trying to add an entity to the database with Entity Framework. When I try to add a new exception to the database, I run into the following error:

"Cannot insert explicit value for identity column in table 'Notification' when identity_insert is set to off"

I am guessing this error is caused due to the Notification entity already having an Identifier (Id property). However, my goal is not to store the Notification entity. It just so happens that my NotificationException entity has a reference to an existing Notification entity.

How can I update my NotificationException entities properly without running into this problem? Actually turning the identity_insert off does not seem like a viable solution.

My two model classes:

public class Notification
{
    // Primary Key
    public long Id { get; set; }

    // Properties
    public bool IsSent { get; set; }
    public bool IsExpired { get; set; }
    public int RetryCount { get; set; }
    public int RetryTime { get; set; }
}

public class NotificationException
{
    // Primary Key
    public long Id { get; set; }

    // Properties
    public int Timestamp { get; set; }
    public string Exception { get; set; }

    // Foreign Keys
    public long NotificationId { get; set; }

    // Navigation Properties
    public virtual Notification Notification { get; set; }
}

Entity Configuration with Fluent API:

private void ConfigureNotificationEntity(EntityTypeBuilder<Notification> builder)
{
    builder.ToTable("Notifications");
    builder.HasKey(i => i.Id);

    builder.Property(i => i.Id)
        .IsRequired()
        .ValueGeneratedOnAdd();

    builder.HasMany(n => n.Exceptions)
        .WithOne(e => e.Notification)
        .OnDelete(DeleteBehavior.Cascade);
}

private void ConfigureNotificationExceptionEntity(EntityTypeBuilder<NotificationException> builder)
{
    builder.ToTable("NotificationExceptions");
    builder.HasKey(i => i.Id);
    builder.Property(i => i.Id)
        .IsRequired()
        .ValueGeneratedOnAdd();

    builder.HasOne(i => i.Notification)
        .WithMany(j => j.Exceptions);
}

The main problem:

public async Task<NotificationException> Add (NotificationException item)
{
    _context.NotificationExceptions.Add(item);
    await _context.SaveChangesAsync();
    return item;
}

As soon as the _context.SaveChangesAsync(); is called, the error mentioned above is thrown.

///Edit

I tested this issue with different objects as well. If the entity has no nested entities, then storing them works just fine. The issue is quite likely with the already known ID of the nested entity.

Cannot insert explicit value for identity column in table 'Notification' when identity_insert is set to off => because you set a value to a column that is automatic set by database. it could be the id in NotificationException item — Erics Nguyen
– Erics Nguyen, Commented Aug 20, 2020 at 14:54
How does your NotificationException set its values before adding it? Because the message seems about setting id explcitly but on your config it should be autogenerated. — tontonsevilla
– tontonsevilla, Commented Aug 20, 2020 at 14:57
@EricsNguyen I am not setting an Id for NotificationException. It is an object I create at runtime without specifying an Id. I believe the error that is thrown also clearly states that the issue is with the identifier from 'Notification' — Robin van der Plas
– Robin van der Plas, Commented Aug 21, 2020 at 7:33

BM- · Accepted Answer · 2021-05-21 01:24:37Z

2

So it turns out in 2016, EF Core changed the behaviour of the Attach method from EF6: https://github.com/dotnet/efcore/issues/4424

to wit:

Add: Adds every reachable entity that is not already tracked

Attach: Attaches every reachable entity, except where a reachable entity has a store generated key and no key value is assigned, these will be marked as added.

Therefore, the solution is just to change
_context.NotificationExceptions.Add(item); to
_context.NotificationExceptions.Attach(item);

This will Add the new NotificationException pushed without a key; however the child Notification, with a declared key, will be attached as 'Unchanged'.

answered May 21, 2021 at 1:24

BM-

6366 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

bthn · Accepted Answer · 2020-08-21 10:05:34Z

1

The problem looks like an issue with how you add notification object to your notificationException object.

Change your Add function like so:

public async Task<NotificationException> Add (NotificationException item)
{
     var notification = _context.Notifications.Find( x => x.Id == yourNotificationId);
     item.Notification = notification;

    _context.NotificationExceptions.Add(item);
    await _context.SaveChangesAsync();
    return item;
}

The trick here is we got notification entity from same context that we will add notificationException object. Check this link for more info

answered Aug 21, 2020 at 10:05

bthn

1962 silver badges16 bronze badges

2 Comments

Robin van der Plas Over a year ago

Thanks for the reply. It looks like you posted this right before I posted the solution I found. I did still try your suggestion, but this did not end up working for me. Both the Notification and Exception entities had the State of 'Added'. However the person in your link also suggested their first option, which is evidently the same as I posted just now.

bthn Over a year ago

yeah actually you re right, first option will save you from a redundant fetch from database as that person says.

Robin van der Plas · Accepted Answer · 2020-08-21 10:09:54Z

1

Found a fix!

var exception = new NotificationException()
{
    Exception = "Very serious exception!",
    QueuedNotificationId = notification.Id,// <-- setting the foreign key
    Timestamp = 420
};

Instead of referencing the entire Notification object within the Exception entity, I just set the foreign key. This way I can store the entity just fine. Still a bit silly how EF doesn't automatically recognize the Notification entity already exists.

answered Aug 21, 2020 at 10:09

Robin van der Plas

471 silver badge8 bronze badges

Collectives™ on Stack Overflow

Adding an entity in Entity Framework with nested entities

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related