Entity Framework, view and instead of insert trigg

问题:

I can't insert an entity into the mapped view. The error I get is:

Store, insert or delete statement affected an unexpected number of rows (0)

I know how to use stored procedures, but it is more interesting for me to try a solution with an instead of trigger on insert row view event. I don't get any error when I delete, update or insert in t-sql code, but I can't INSERT a row using EF. Update and delete work in EF, but INSERT doesn't.

Code:

create view TestInsert
as
    select a.table_id, a.name
    from TableA as a

create trigger tr_works_via_tsql_but_not_ef_for_some_reason
on TestInsert
instead of isert
begin
   insert into TableA (table_id, name)
   select table_id, name from inserted;
end


[Table(TestInsert)]
public class TestInsert
{
   [Key]
   public int table_id { get; set; }
   public string name { get; set; }
}

Can anyone help me?

回答1:

Hooray, I have found the solution! The body of instead of trigger must return an id for the table.

create trigger tr_works_via_tsql_but_not_ef_for_some_reason
on TestInsert
instead of isert
begin
   insert into TableA (table_id, name)
   select table_id, name from inserted;

   **select id from TableA where @@ROWCOUNT > 0 and id = scope_identity()**
end

I use direct mapping and there is no edmx file in my project. This is the source of the answer: Entity Framework with Instead Of triggers

回答2:

You can't insert into a SQL View. Views are only for showing you data determined by some query logic that is defined by the user. You can't actually save to the View, just to the underlying tables themselves. If you want to adjust what comes out of the View, you need to save to your 2 tables first, and then it will pop up in the View.

EDIT: You can't do that in EF. When you call the SaveChanges() method of your context, it tries to save the records and return a count to you (hence why the method returns an int). The problem is that when it does try to save it, it fails to save, and throws the exception that you're seeing where it gets an unexpected amount of rows saved, namely 0 (instead of the number of rows it notices are changed in the context). If you want to save the changes via EF, you'll need to do it via the actual tables themselves. Sorry.