1

I am using Entity Framework Core on .NET 6, but the question is generic so I appreciate answers if they relate to other versions.

I have a table, something like

MyTable (
    int Id, 
    char Type, 
    varchar(50) Data
)

where Id is an identity column (auto increment) and is defined as the primary key.

I have a view, something like

create view MyView as
    select Id, Data 
    from MyTable
    where Type = 'A'

On the view I have created a trigger to allow insertion

alter trigger MyViewInsert on MyView
instead of insert as
    insert into MyTable(Type, Data)
    select 'A', Data 
    from Inserted

Also in the Entity Framework context, I have added

modelBuilder.Entity<MyView>().ToTable("MyView");

partial void OnModelCreatingPartial(ModelBuilder modelBuilder) 
{
    modelBuilder.Entity<MyView>(entity => {
        entity.HasKey(e => new { e.Id });
        entity.Property(e => e.Id).ValueGeneratedOnAdd();
    });
}

However, with all of this done, I get the following error when I try to do a Context.SaveChanges():

Microsoft.EntityFrameworkCore.DbUpdateConcurrencyException: 'Database operation expected to affect 1 row(s) but actually affected 0 row(s). Data may have been modified or deleted since entities were loaded..'

I believe this is because I am using the trigger to do the insert and so it is not receiving back the Id. Is there a better, recommended way of doing this? If not is there any work-around to the trigger issue?

Graeme
  • 333
  • 3
  • 14
  • Possible duplicate of: https://stackoverflow.com/questions/7461265/how-to-use-views-in-code-first-entity-framework – Ebbelink Sep 20 '22 at 06:39
  • Have you tried to insert data into your view in SSMS? I don't think it will work, as you are inserting an explicit value into an `identity` column. – Roger Wolf Sep 20 '22 at 08:40
  • Roger Wolf, you are right, that was just lazy typing when I was writing the question. I have updated the question to remove Id from the insert. – Graeme Sep 21 '22 at 23:49
  • You can use `ValueGeneratedNever()` instead of `ValueGeneratedOnAdd()`. The only issue is that EF won't set the Id property of a newly inserted object. – Gert Arnold Sep 22 '22 at 10:15

0 Answers0