inserting objects with entity framework

I have a problem with inserting objects with an entity data model, and despite my best efforts to find the solution, I'm coming up short.

I have a database with lots of tables which refer to others. two of these tables are vendor and gl_account. a vendor may have zero or more gl_account objects associated with it. I want to create a vendor object and all of its gl_accounts in one go, but I keep getting foreign key constraint errors.

Code:

vendor myVendor  = vendor.Createvendor(0); // the value for id_vendor
gl_account glAccount = gl_account.Creategl_account(0); // the value for id_gl_account
myVendor.gl_accounts.Add(glAccount);
dbContext.SaveChanges();

am I doing something horribly wrong? I don't want to call SaveChanges() after creating the vendor object, because then it can't be rolled back if something else goes wrong.

Generally the entity framework will automatically handle ordering operations so that you should not get constraint errors. You should not need to call SaveChanges twice or anything like that. When the EF is unable to do this correctly, it usually means that the information in the SSDL portion of the metadata is incorrect. The SSDL is essentially a copy of the database schema including not only the tables and columns but also constraints. If a constraint is missing from the SSDL but present in the database, then the EF is unaware of it and will incorrectly order operations which can cause constraint violation errors.

- Danny

Originally Posted by simmdan

Generally the entity framework will automatically handle ordering operations so that you should not get constraint errors. You should not need to call SaveChanges twice or anything like that. When the EF is unable to do this correctly, it usually means that the information in the SSDL portion of the metadata is incorrect. The SSDL is essentially a copy of the database schema including not only the tables and columns but also constraints. If a constraint is missing from the SSDL but present in the database, then the EF is unaware of it and will incorrectly order operations which can cause constraint violation errors.

- Danny

great... that's just what I wanted to hear

I am using the mysql connector for .net, and it appears that it is not generating a proper model... and I'm nowhere near familiar enough with the EF and ssdl files to try to troubleshoot this myself.

ok, so I tried creating a new model to test a few things with, and this is what I came up with:

these are the tables I tested with:

Code:

CREATE TABLE t1_guid (
  id_t1 char(36) NOT NULL,
  PRIMARY KEY (id_t1)
) ENGINE=InnoDB;

CREATE TABLE t2_guid (
  id_t2 char(36) NOT NULL,
  id_t1 char(36) NOT NULL,
  PRIMARY KEY (id_t2),
  FOREIGN KEY (id_t1) REFERENCES t1_guid (id_t1)
) ENGINE=InnoDB;

CREATE TABLE t1_int (
  id_t1 int NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (id_t1)
) ENGINE=InnoDB;

CREATE TABLE t2_int (
  id_t2 int NOT NULL AUTO_INCREMENT,
  id_t1 int NOT NULL,
  PRIMARY KEY (id_t2),
  FOREIGN KEY (id_t1) REFERENCES t1_int (id_t1)
) ENGINE=InnoDB;

and after generating the model, this is the code that I used:

Code:

testEntities te = new testEntities();

Guid t1guid = Guid.NewGuid();
t1_guid t1 = t1_guid.Createt1_guid(t1guid);

Guid t2guid = Guid.NewGuid();
t2_guid t2_0 = t2_guid.Createt2_guid(t2guid);
t2guid = Guid.NewGuid();
t2_guid t2_1 = t2_guid.Createt2_guid(t2guid);
t2guid = Guid.NewGuid();
t2_guid t2_2 = t2_guid.Createt2_guid(t2guid);
t2guid = Guid.NewGuid();
t2_guid t2_3 = t2_guid.Createt2_guid(t2guid);
t2guid = Guid.NewGuid();
t2_guid t2_4 = t2_guid.Createt2_guid(t2guid);

t1.t2_guids.Add(t2_0);
t1.t2_guids.Add(t2_1);
t1.t2_guids.Add(t2_2);
t1.t2_guids.Add(t2_3);
t1.t2_guids.Add(t2_4);

te.AddTot1_guids(t1);

te.SaveChanges();

and this worked.

but when I tried the following:

Code:

testEntities te = new testEntities();

t1_int t1 = t1_int.Createt1_int(0);

t2_int t2_0 = t2_int.Createt2_int(0);
t2_int t2_1 = t2_int.Createt2_int(0);
t2_int t2_2 = t2_int.Createt2_int(0);
t2_int t2_3 = t2_int.Createt2_int(0);
t2_int t2_4 = t2_int.Createt2_int(0);

t1.t2_ints.Add(t2_0);
t1.t2_ints.Add(t2_1);
t1.t2_ints.Add(t2_2);
t1.t2_ints.Add(t2_3);
t1.t2_ints.Add(t2_4);

te.AddTot1_ints(t1);

te.SaveChanges();

it failed with the error "Object reference not set to an instance of an object."

it seems that it doesn't generate proper ssdl code, or something, and that causes it to fail to get the insert id of the dependent row. if I specify a value for the field the other rows depend on, it works just fine, because it already has it on hand.

has anyone else seen this with the mysql .net connector?

One thing to keep in mind is that you need to set StoreGeneratedPattern to identity for any properties whose value will automatically be computed by the database and set when you insert an entity. Not setting this should not result in the exception you are seeing, so it does sound like there is an issue with the provider, but that's also a consideration to keep in mind.

Originally Posted by simmdan

One thing to keep in mind is that you need to set StoreGeneratedPattern to identity for any properties whose value will automatically be computed by the database and set when you insert an entity. Not setting this should not result in the exception you are seeing, so it does sound like there is an issue with the provider, but that's also a consideration to keep in mind.

unfortunately, there is no such property on any of the objects in this model. perhaps this is an oversight in the mysql .net connector. I know it's not complete, production-ready code, but this seems like a pretty big hole in the functionality of the connector.