1

I have a issue when I try to inserts data to the table. I am using a EF, MariaDB and 'unfortunatelly' trigger. When I try to insert new data, EF throws exception OptimisticConcurrencyException.

Here is my trigger:

CREATE TRIGGER AutoIncrdata BEFORE INSERT ON data
FOR EACH ROW BEGIN
DECLARE new_id integer;
SET new_id = (select max(id_data) + 1 from data);
IF (NEW.id_data < 1) then
    SET NEW.id_data = COALESCE(new_id, 1 );
END IF;
END

The exception shows after simple insert:

var v = new Data
{
    Id = someObject.Id,
    Location = "text",
    Name = "Data_" + someObject.Id
};
mariaDb.Add(v);
mariaDb.SaveChanges();

Exception:

InnerException  {"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."}  System.Exception {System.Data.Entity.Core.OptimisticConcurrencyException}

SaveChanges() implementations - RepositoryPattern:

    public virtual void SaveChanges()
    {
        DbContext.SaveChanges();
    }

Is there any way how to teach EF the trigger or I should modify the trigger? I need a table with autoincrement primary key and with possibility of inserting pk via EF. If I have a table just with autoincrement on id column so the EF overrides inserted id after SaveChanges().

Thanks for any advice.

wolen
  • 705
  • 2
  • 8
  • 21
  • What happens if you leave out setting the Id property? – Devon Burriss Sep 15 '15 at 18:51
  • Same problem, EF does not like my trigger. – wolen Sep 15 '15 at 18:52
  • Take a look at this. Seems to be same issue. Not sure about the MariaDB part though. http://stackoverflow.com/questions/5820992/error-when-inserting-into-table-having-instead-of-trigger-from-entity-data-frame – Devon Burriss Sep 15 '15 at 18:58
  • It looks like same problem, but MariaDb does not support INSTEAD OF INSERT. – wolen Sep 15 '15 at 19:08
  • For now, I create workaround via `MySqlCommand`. It works but it is a little bit hack in my code because I am using EF everywhere but for this one operation is used completely different technology. – wolen Sep 17 '15 at 07:24

0 Answers0