Using a database first approach in Entity Framework Core, is there any way to use a view as the equivalent of a table?

Viewed 28

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?

0 Answers
Related