Oto Dušek Oto Dušek - 2 months ago 16x
MySQL Question

Entity Framework - mysql stored procedure

I mapped stored procedure to my model using following code:

.MapToStoredProcedures(p => p.Insert(sp => sp.HasName("sp_InsertAccount"))
.Update(sp => sp.HasName("sp_UpdateAccount"))
.Delete(sp => sp.HasName("sp_DeleteAccount"))

but when i add record using:


it throws error:

An unhandled exception of type
occurred in EntityFramework.dll

Additional information: Store update, insert, or delete statement
affected an unexpected number of rows (0). Entities may have been
modified or deleted since entities were loaded. See
http://go.microsoft.com/fwlink/?LinkId=472540 for information on
understanding and handling optimistic concurrency exceptions.

the sql statement for stored procedure is:

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_InsertAccountMove`(
IN type int(11),
IN account_id int(11),
IN accountant_id int(11),
IN amount int(11),
IN date datetime
INSERT INTO `bank`.`accounts_moves`
(`type`, `account_id`, `accountant_id`, `amount`, `date`)
VALUES (type, account_id,
(SELECT id FROM accountant
Where name = SUBSTRING_INDEX(USER() ,'@',1))
, amount, NOW());


i think that mysql stored procedure does not return the number of affected lines

I am using EF 6.0, mysql 5.7

Does anyone know how to fix this error the right way?


I solved the problem add to last line of sproc following line:

SELECT * FROM `bank`.`accounts_moves` WHERE id = LAST_INSERT_ID();