I'm using the Oracle doConnect 5.55 beta and I'm stuck at a problem.
My model is basically 2 entities (Role and User) linked together by a table that stores both table's ID fields.
When I do someRole.Add(someUser) or someUser.Roles.Add(someRole) I get the following exception when I run SaveChanges() on the Entity Framework model:
{"ORA-01400: cannot insert NULL into (\"AM\".\"AM_USER_ROLES\".\"ID_USER\")\nORA-06512: at line 4"
My user has been retrieved from the DB correctly and of course has an ID with a proper value. As the many-to-many associations in EF 4.0 are "automagic", I can't find anything wrong in my code and suspect a bug in Oracle dotConnect.
Entity Framework 4.0 RC - Problem in many-to-many
Ok so now I know exactly what's wrong: many-to-many associations use the StoreGeneratedPattern of the source tables.
So a table with UserID and RoleID will generate an INSERT statement with no VALUES at all!
This is Ok behaviour for a table's ID field but not for foreign keys. If the many-to-many associative table has other fields, Entity Framework will generate a entity for that table and in that case it works out fine as the ID fields are considered foreign field.
My suspicion is that dotConnect should ignore StoreGeneratedPattern on *-* associations foreign key fields.
Bellow the output SQL from DB Monitor:
Note how it's trying to get the values it's supposed to insert back as if they where generated IDs.
So a table with UserID and RoleID will generate an INSERT statement with no VALUES at all!
This is Ok behaviour for a table's ID field but not for foreign keys. If the many-to-many associative table has other fields, Entity Framework will generate a entity for that table and in that case it works out fine as the ID fields are considered foreign field.
My suspicion is that dotConnect should ignore StoreGeneratedPattern on *-* associations foreign key fields.
Bellow the output SQL from DB Monitor:
declare
updatedRowid ROWID;
begin
insert into AM.USER_ROLES()
values ()
returning ROWID into updatedRowid;
open :outParameter for select ID_USER, ID_ROLE from AM.USER_ROLES where ROWID = updatedRowid;
end;
Note how it's trying to get the values it's supposed to insert back as if they where generated IDs.