Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2016-10-11
2
Medium Priority
?
133 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Hello there! As a developer I have modified and refactored the unit tests which was written by fellow developers in the past. On the course, I have gone through various misconceptions and technical challenges when it comes to implementation. I would…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses
Course of the Month12 days, 21 hours left to enroll

971 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