Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2016-10-11
2
Medium Priority
?
120 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

722 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