none
Entity Framework - MySQL - stored procedure RRS feed

  • Question

  • 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 "link" 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?

    Thursday, July 28, 2016 6:50 PM

All replies

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

    You need to run the T-SQL  outside of EF with whatever tool that will allow you to run the T-SQL from an T-SQL execution  pane from a MySQL DB Management tool.

    Additional information: Store update, insert, or delete statement affected an unexpected number of rows (0).

    That message being put out on an exception by EF means that T-SQL was executed, but execution of the T-SQL affected zero rows (nothing happened) the T-SQL execution did not work, and 1 or more rows should have been inserted and it didn't happen.

    Thursday, July 28, 2016 7:40 PM
  • What SQL query should i run outside of EF?
    Thursday, July 28, 2016 8:38 PM
  • What SQL query should i run outside of EF?

    The T-SQL in the Sproc  and you should test it outside of EF and find out why it doesn't work.
    Friday, July 29, 2016 12:34 PM
  • when i exec sproc in Mysql workbench it works.

    The T-SQL in the Sproc  and you should test it outside of EF and find out why it doesn't work.

    It doesnt work because mysql sproc doesnt return number of affected work

    • Edited by Oto Dusek Friday, July 29, 2016 8:36 PM
    Friday, July 29, 2016 8:17 PM
  • when i exec sproc in Mysql workbench it works.

    The T-SQL in the Sproc  and you should test it outside of EF and find out why it doesn't work.

    It doesnt work because mysql sproc doesnt return number of affected work

    Comon man, figure it out put it together.  The sproc is not going to return the number of rows affected not unless you told the t-sql to return the number of rows affected count, because you wanted to do something with the count out of the sproc.

    The EF engine and the MySQL DB engine are talking to each other internally. Your sproc submitted by  the EF engine submitted some T-SQL to be executed by the DB engine for an insert and (nothing was inserted into a DB table no records were inserted into the table). The DB engine saw it and reported it to the EF engine that some records were supposed to be inserted into a table, but it didn't happen and 0 rows were affected.  So the DB engine passed the information on to the  EF DB engine and EF through the exception of the 0 rows affected  for an insert, update or delete the T-SQL was supposed to be doing.

    If you ran the T-SQL manually by doing a  copy/past of the T-SQL in the sproc, you ran the T-SQL with a tool and it came back with a 0 rows were affected, then it should be a clue to you that something is wrong with the T-SQL and it didn't work as expected.

    That's what is happening is that EF is notifying you that something is wrong. 

    If you had one record that was to be inserted into a table and it was successful, then the message is going to be 1 rows was affected.

    You got 0 rows affected, something is wrong and you got the notification that insert didn't work.

    Saturday, July 30, 2016 3:18 AM