Entity-Framework: Create Many-To-Many-Relations

Question

We are using the "DB-First" approach for an application, cause the Database is shared between various applications, so it should be the "master". (MySQL)

We have 3 simple tables, responsible for the Role-To-Permission Assignment, like this:

The Visual-Studio Model Designer (after building the model from the database) perfectly fine recocnices this as a "Many-To-Many" relation, not even generating a "Role_to_permission"-Entity, as there are no further attributes on the assignment)

Until now, we created those entries in the database, which lead to the expected outcome within the application. (Accessing mappings)

Currently we are working on an Interface, allowing to assign "Permissions" to "Roles". And here, I'm a little stuck:

• If such a relation has another attribute (such as required or a date), the EMF creates an own entity for the relation - let's assume Permission_To_Role.

then, I can "easily" create a relation, with the following code:

using (MyDb db = new MyDB()){
   Permission_To_Role ptr = new Permission_To_Role();
   ptr.PermissionId = 5;
   ptr.RoleId = 8;
   ptr.CreationDate = DateTime.Now();

   db.Permission_To_Role.Add(ptr);
   db.SaveChanges();
}

Whatsoever - in this case - we don't have any additional attribute on the mapping, so the additional class is avoided by the EF Framework.

Im now trying hard to create a relation, but without success:

using (MyDB db = new MyDB())
{
    //Get ids.
    long permissionId = 2;
    long roleID = 5;

    Permission p = db.Permission.Find(permissionId);
    Role r = db.Role.Find(roleID);

    r.Permissions.Add(p);

    db.SaveChanges();
}

This always results in an Exception, and I can't figure out why (IDs are existing and correct) ...

Exception of db.SaveChanges():

An exception of type 'System.Data.Entity.Infrastructure.DbUpdateException' occurred in EntityFramework.dll but was not handled in user code

Additional information: An error occurred while saving entities that do not expose foreign key properties for their relationships. The EntityEntries property will return null because a single entity cannot be identified as the source of the exception. Handling of exceptions while saving can be made easier by exposing foreign key properties in your entity types. See the InnerException for details.

Inner Exception:

An error occurred while updating the entries. See the inner exception for details.

Inner Inner Exception:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(SELECT Permission_to_Role.PermissionId, Permission_to_Role.RoleId FROM' at line 1

Ideas?

---

Update:

SHOW CREATE TABLE Permission_to_Role;

output:

CREATE TABLE `Permission_to_Role` (
  `PermissionId` bigint(19) NOT NULL,
  `RoleId` bigint(19) NOT NULL,
  UNIQUE KEY `Permission_to_Role_unique` (`PermissionId`,`RoleId`),
  KEY `Permission_Mapping_idx` (`PermissionId`),
  KEY `Role_Mapping_idx` (`RoleId`),
  CONSTRAINT `Permission_Mapping` FOREIGN KEY (`PermissionId`) REFERENCES `permission` (`Id`) ON DELETE CASCADE ON UPDATE NO ACTION,
  CONSTRAINT `Role_Mapping` FOREIGN KEY (`RoleId`) REFERENCES `role` (`Id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Update2:

As of the current comments: I enabled the output for queries generated by the EF, and found this charm - which is obviously a malformated Query:

INSERT INTO 
  (SELECT 
    Permission_to_Role.PermissionId,
    Permission_to_Role.RoleId  
   FROM 
     Permission_to_Role AS Permission_to_Role
  )
  ( PermissionId, RoleId) VALUES ( 2, 1)

The query actual should be:

INSERT INTO 
  Permission_To_Role
  ( PermissionId, RoleId) VALUES ( 2, 1)

So, I think this looks like a "bug"? As mentioned above:

Whatsoever - in this case - we don't have any additional attribute on the mapping, so the additional class is avoided by the EF Framework.

there is no intermediate Permission_To_Role Entity, hence it seems like EF is trying to replace this table name with the query

  SELECT 
    Permission_to_Role.PermissionId,
    Permission_to_Role.RoleId  
   FROM 
     Permission_to_Role AS Permission_to_Role

EVEN upon inserts... (Maybe this works for MsSQL and is a bad implementation for the MySQL Connector?)

Answer 1

I'd really like to figure out the "cause" of this issue - but for now, I added another column grantedById (referencing user.id) to that relation table.

So, this caused the intermediate entity Permission_To_Role to be generated in the Model, and made me believe it's going to work now! (Cause done that hundreds of times, like this: )

 using (MyDb db = new MyDB()){
   Permission_To_Role ptr = new Permission_To_Role();
   ptr.PermissionId = 5;
   ptr.RoleId = 8;
   ptr.GrantedById = Session.CurrentUser.Id;

   db.Permission_To_Role.Add(ptr);
   db.SaveChanges();
}

But It should be possible to do Database-Inserts for Many-To-Many-Mappings with 2 Foreign-Key-Constraint Columns ONLY as well, shouldn't it?

Answer 2

I'm a little tired right now, but finally got it to work.

I'm not 100% sure to outline the real problem, cause I changed a lot of things - but the following "findings" are kind of milestones while resolving this problem:

First,

I tried to add more columns as mentioned above, which did nor work out. It caused the Mapping-Table to be present as entity, but the insert showed the same problem. (Insert Query, containing wired "SELECT" Statement instead of table name)

Second,

I noted, that the generated Mapping-Table (In EM-Designer) took ALL columns into account for the primary key, while I designed the table without any (composite) Primary Key in the MySQL-Designer (only a unique key was setup accross all columns):

Third,

I was like f*** y** - and added a surrogate Primary Key-Column to the mapping-table (In the database designer)...

and unchecked any Primary-Key-Membership in the EF-Designer for any remaining column:

And guess what? It works :-)

using (MyDb db = new MyDB()){
    Permission_to_Role ptr = new Permission_to_Role();
    ptr.PermissionId = permissionId;
    ptr.RoleId = r.Id;
    ptr.GrantedById = u.Id;
    ptr.GrantedAt = DateTime.Now;

    db.Permission_to_Role.Add(ptr);
    db.SaveChanges();
}

So, there are three things left to say:

• First (I noted this quite some time) - When synchronsizing your Model with the database - there are changes that aren't picked up... And you'll have a hard time figuring it out. It might only be "Indexes", but it also can be stuff like "Foreign Key Constraints" or even "Primary-Key-Settings". (Consider Hibernate (Java) as the master of the 80/20 rule: Hibernate does 80% - Searching the final 20% is up to the user!)
• Second: This problem might be a combination of a special database-schema, relying on third-party-frameworks and expecting about everything to be nice and clean automatically... Only trust "yourself"!
• Third: Always use a surrogate Primary Key for every table. It doesn't hurt at all, but avoids all the hassle with native-primary keys. (Which you can setup as unique keys anyway)