[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Entity Framework many-to-many relationship not getting set up in code first approach

Posted on 2016-10-11
2
Medium Priority
?
162 Views
Last Modified: 2016-10-12
So in my User class I have this:
public virtual ICollection<DataSystem> Systems { get; set; }

Open in new window


In my DataSystem cass I gave this:
public virtual ICollection<User> Users { get; set; }

Open in new window


This magic usually does the trick to create a many to many relationship. Here I want that magic to happen between User and DataSystem. But it doesn't happen. When looking at the generated database, there is no in between table DataSystemUser or anything similar.

So, optimistic as I am, I Google a bit and try this:
modelBuilder.Entity<User>()
           .HasMany(v => v.Systems)
           .WithMany(p => p.Users);

Open in new window


But then my  browser fills with nasty messages saying such as:

Exception Details: System.Data.SqlClient.SqlException: Introducing FOREIGN KEY constraint 'FK_dbo.UserDataSystems_dbo.DataSystems_DataSystem_DataSystemId' on table 'UserDataSystems' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
Could not create constraint or index.

I don't give up just yet. I try this:'
         modelBuilder.Entity<DataSystem>().
             HasOptional(e => e.Users).
             WithMany()
             .WillCascadeOnDelete(false);

         modelBuilder.Entity<User>().
            HasOptional(e => e.Systems).
            WithMany()
            .WillCascadeOnDelete(false);

Open in new window


But Entity Framework still is not letting me off the hook:

Exception Details: System.Data.SqlClient.SqlException: Introducing FOREIGN KEY constraint 'FK_dbo.UserDataSystems_dbo.DataSystems_DataSystem_DataSystemId' on table 'UserDataSystems' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
Could not create constraint or index.

So how do I do this right?
0
Comment
Question by:itnifl
2 Comments
 
LVL 23

Accepted Solution

by:
Ioannis Paraskevopoulos earned 2000 total points
ID: 41839518
Hi,

Take a look here. Though it suggests that you should define the ICollection in your models, it seems that it also needs to instantiate a HashSet on one of the your models. Digging a bit further it seems that it does not have to be a HashSet, but it could also be a List or any other of the likes, but HashSet is the default.

The other thing in this same article is about the way of doing this in the fluent api:

modelBuilder.Entity<User>()
           .HasMany<System>(v => v.Systems)
           .WithMany(p => p.Users)
           .Map(cs =>
                    {
                        cs.MapLeftKey("UserId");
                        cs.MapRightKey("SystemId");
                        cs.ToTable("UserSystem");
                    });

Open in new window


There is also a third way, that suggests to explicitly define this intermediate table in your Model. You may find some details here.

Giannis
0
 
LVL 2

Author Closing Comment

by:itnifl
ID: 41839617
Actually tried with the HashSet also, gave the same error as the last one I referred to in my question. In the end I thought it was only for the naming of the columns because I had the same result , but maybe not. Ended up with using a custom intermediate table like the one shown in your last link. Was hoping to get the auto magic working, but I guess there are some dark clouds in the horizon for that to happen that EF isn't telling me about directly. But custom intermediate table works. Thank you for your reply.
0

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
The video will let you know the exact process to import OST/PST files to the cloud based Office 365 mailboxes. Using Kernel Import PST to Office 365 tool, one can quickly import numerous OST/PST files to Office 365. Besides this, the tool also comes…
Free Data Recovery software is an advanced solution from Kernel Tools to recover data and files such as documents, emails, database, media and pictures, etc. It supports recovery from physical & logical drive after a hard disk crash, accidental/inte…
Suggested Courses
Course of the Month11 days, 5 hours left to enroll

612 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question