How to save a many to many relationship with Entity Framework

Question

Hi, I have a many to many relationship between Flight and Journey, so I have created an intermediate table to create the relationship:

the thing is, I have a list of flights and one Journey, so how can I create the code to make the assembly so I can after save it using Entity Framework?

The models are created and the context, this code seems to be ok:

//process to create the Flight with the Transport list

 var journey = new Journey()

{

                    JourneyOrigin = origin,

                    JourneyDestination = destination,

                    JourneyPrice = SumPrice

};


var response = new JourneyFlight()

{

                    Journey = journey,

                    Flight = flights[0]

};


            

//Saving here

But this only saves the first flight, and I have another one

Answer 1

@Jhon Hernandez, Welcome to Microsoft Q&A, If you want to save many to many relationship in ef core, I recommend that you use the code first to generate the models and define some relationship in OnModelCreating event.

Here is a code example you could refer to.

Model and Dbcontext:

public class JourneyFlight
    {
        public int JourneyFlightId { get; set; }

        public int JourneyId { get; set; }
        public int FlightId { get; set; }

        public Journey? journey { get; set; }

        public Flight? flight { get; set; }


    }

    public class Journey
    {
        public int JourneyId { get; set; }

        public double JourneyPrice { get; set; }

        public ICollection<JourneyFlight>? journeyFlights { get; set; }
    }
    public class Flight
    {
        public int FlightId { get; set; }
        public double FlightPrice { get; set;}

        public int TransportId { get; set; }
        public Transport? transport { get; set; }
        public ICollection<JourneyFlight>? journeyFlights { get; set; }
    }

    public class Transport
    {
        public int TransportId { get; set; }

        public int TransportFlightNumber { get; set; }

        public ICollection<Flight>? flights;
    }

    public class MyContext:DbContext
    {
        public DbSet<JourneyFlight> journeyFlights { get; set; }

        public DbSet<Journey> journeys { get; set; }

        public DbSet<Flight> flights { get; set; }

        public DbSet<Transport> transports { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer("connstr");
        }
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<JourneyFlight>().HasKey(m => new { m.JourneyId, m.FlightId });
            modelBuilder.Entity<JourneyFlight>().HasOne(m => m.journey).WithMany(m => m.journeyFlights).HasForeignKey(m=>m.JourneyId);
            modelBuilder.Entity<JourneyFlight>().HasOne(m => m.flight).WithMany(m => m.journeyFlights).HasForeignKey(m=>m.FlightId);
            modelBuilder.Entity<Flight>().HasOne(m => m.transport).WithMany(m => m.flights).HasForeignKey(m => m.TransportId);

        }
    } 

How to add data in main method:

MyContext context=new MyContext();
           JourneyFlight journeyFlight1=new JourneyFlight();
            JourneyFlight journeyFlight2 = new JourneyFlight();
            var j1=new Journey();
            j1.JourneyPrice = 100;
            var j2=new Journey();
            j2.JourneyPrice = 200; 
            var f1=new Flight();
            f1.FlightPrice = 50;
            var f2=new Flight();    
            f2.FlightPrice = 70;
            journeyFlight1.journey = j1;
            journeyFlight1.flight = f1;
            journeyFlight2.journey = j2;
            journeyFlight2.flight = f2;
            Transport transport = new Transport();
            transport.flights = new List<Flight>
            {
                f1,
                f2
            };
            transport.TransportFlightNumber = 1001;
            context.journeyFlights.Add(journeyFlight1);
            context.journeyFlights.Add(journeyFlight2);
            context.transports.Add(transport);
            context.SaveChanges();

Based on my test, I could get the following result in sql server:

Hope my code example could help you.

Best Regards,

Jack

---

If the answer is the right solution, please click "Accept Answer" and upvote it.If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Can someone explain how many-to-many relationship can be established when you are adding a new object, for eg; journey or flight in the above case. Can this be carried out at the same time as we add a new entity to the database ? The scenario is that, if i want to add a new student and in the meantime add the same student to a course, how should i proceed ? Should i add the student and then later establish the many-to-many relationship ? Can someone explain, how it should be done.

public class Student : IdentityUser
{
    public string Name { get; set; }
    public List<Course> Courses { get; set; } = new List<Course>();
    public List<StudentCourse> StudentCourses { get; set; } = new List<StudentCourse>();
    public DateTime LastUpdatedOn { get; set; }
}

public class Course
{
    public int Id { get; set; }
    public string Name { get; set; }
    public List<Student> Students { get; set; } = new List<Student>();
    public List<StudentCourse> StudentCourses { get; set; } = new List<StudentCourse>();
}

public class StudentCourse
{
    public int StudentId { get; set; }
    public Student Student { get; set; }
    public int CourseId { get; set; }
    public Course Course { get; set; }
}

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Student>(entity =>
    {
        entity.HasKey(e => e.Id);
        entity.Property(e => e.Id).ValueGeneratedOnAdd();
        entity.HasMany(e => e.Courses)
            .WithMany(m => m.Students)
            .UsingEntity<StudentCourse>(
                j => j.HasOne(pt => pt.Course).WithMany(t => t.StudentCourses).HasForeignKey(pt => pt.CourseId),
                j => j.HasOne(pt => pt.Student).WithMany(p => p.StudentCourses).HasForeignKey(pt => pt.StudentId),
                j =>
                {
                    j.HasKey(t => new { t.StudentId, t.CourseId });
                });
    });
    modelBuilder.Entity<Course>(entity =>
    {
        entity.HasKey(e => e.Id);
        entity.Property(e => e.Id).ValueGeneratedOnAdd();
    });
}

public async Task AddStudentWithCourses(List<Student> newStudents, Course course)
{
	
	foreach (var newStudent in newStudents) 
	{
	    if (course != null)
	    {
	        if (newStudent.Courses == null)
	        {
	            newStudent.Courses = new List<Course>();
	        }
	       
	        newStudent.Courses.Add(course);        
	    }
	    
	    _context.Students.Add(newStudent);
	}
    await _context.SaveChangesAsync();
}

The problem is that when i try to add students to the db with the studentCourses, ef core does not create new entries in the studentCourses table. And when i tried to establish the many-to-many relationship by using the following code instead of newStudent.Courses.Add(course)

   
StudentCourse = new StudentCourse() 
{
	Student = new
}

newStudent.StudentCourses.Add(newStudentCourse);

i get dbConccurrency issues and the exception is that it is something related to the Course entity.