Oto Dušek Oto Dušek - 4 months ago 27
MySQL Question

Entity Framework - mysql stored procedure

I mapped stored procedure to my model using following code:

modelBuilder.Entity<Account>()
.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:

bank.AccountsMoves.Add(entity);


it throws error:


An unhandled exception of type
'System.Data.Entity.Infrastructure.DbUpdateConcurrencyException'
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
)
BEGIN
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());

END


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?

Answer

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

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