Solved

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

Posted on 2016-10-11
2
50 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 500 total points
Comment Utility
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
Comment Utility
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
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.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now