Solved

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

Posted on 2016-10-11
2
77 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
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

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.
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

856 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