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

Question

I am working on creating WCF restful Services and when I try adding a record via entity framework into my database I get this exception:

Store update, insert, or delete statement affected an unexpected number of rows (0). Entities may have been modified or deleted since entities were loaded.

I have a trigger created at my database which works as:

GO
CREATE TRIGGER [dbo].[Trigger_groupLocation_date]
    ON [dbo].[groupLocation]
    INSTEAD OF INSERT
    AS
    BEGIN
        SET NoCount ON
        DECLARE @s int
        DECLARE @g_id int
        DECLARE @lat float
        DECLARE @lon float
        SELECT @s = sender FROM inserted
        SELECT @g_id = g_id FROM inserted
        SELECT @lat = latitude FROM inserted
        SELECT @lon = longitude FROM inserted

        insert into [groupLocation] (sender,g_id,latitude,longitude) values(@s,@g_id,@lat,@lon)
    END

One thing to note is that I am able to add records directly into the database. I read some details regarding the exception from here, and After some research I found a similar question at stackoverflow. But while trying the solution I read that DbContext doesn't have a Refresh() method so Instead I tried:

var ctx = ((IObjectContextAdapter)db).ObjectContext;
ctx.Refresh();

But I still gets the same exception. Then I read here that only if you are using code first approach then this work around can work, I am using "EF designer from database approach". How can I get access to Refresh method in data first approach?

for some time you can comment the trigger and then try, what exception are thrown now. because EF may not allow other changes at same time — Nazir Ullah, Jun 12 '16 at 07:38
If i comment out the trigger then service runs smoothly and there is no exception thrown. — Saif, Jun 12 '16 at 08:01

score 3 · Accepted Answer · edited May 23 '17 at 11:58

Try adding this line of SQL code inside your trigger AFTER the insert:

select g_id from [groupLocation] where @@ROWCOUNT > 0 and g_id = scope_identity();

I assume g_id is your identity column, if not replace both occurences of g_id in the statement above with the identity column.

Also read this SO question - Error when inserting into table having instead of trigger from entity data framework

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

1 Answers1