Question 169066 in ASP.NET Question posted in
The official documentation can be found here.

Can I join 3 tables in ASP.net where 1st is related to 2nd and 3rd to 2nd only?

I have a problem. I want to join 3 tables in my asp.net web app but I am unsure on how to do that. Could you please help me?

I want to join StudioId to a Movie table. However, Movie is not related in any way to Studio table. Movie is related to Movie_Producers (being a bridge table), Movie_Producers table is related to Producers and Producers are related to Studio. How can I somehow join Studio to Movie, considering previousely mentioned relations?

Here is my current index controller (I have tried to add Studio by "ThenInclude" – it does not work since I cannot pass Studio model to my Movie View):

 public async Task<IActionResult> Index()
        {
            var applicationDbContext = _context.Movie
            .Include(m => m.Cinema)
            .Include(m => m.Movie_Producers)
            .ThenInclude(mp => mp.Producer)
                .ThenInclude(mp => mp.Studio);
            return View(await applicationDbContext.ToListAsync());
        }

here is my Movie model:

public class Movie
    {
        [Key]
        public int Id { get; set; }

        public string Name { get; set; }
        public string Description { get; set; }
        public double Price { get; set; }
        public string ImageURL { get; set; }
        public DateTime StartDate { get; set; }
        public DateTime EndDate { get; set; }

        //Cinema
        public Cinema? Cinema { get; set; }

        public int CinemaId { get; set; }

        //Producer
        public List<Movie_Producer>? Movie_Producers { get; set; }

    }

here is my Movie_Producers model:


    public class Movie_Producer
    {    
        public int Id { get; set; }

        //Relationships
        public Movie? Movie { get; set; }

        public int MovieId { get; set; }

        //producer
        public Producer? Producer { get; set; }

        public int ProducerId { get; set; }
    }

here is my Producers model:

    public class Producer
    {
        [Key]
        public int Id { get; set; }

        [Display(Name = "Profile Picture")]
        [Required(ErrorMessage = "Profile Picture is required")]
        public string ProfilePictureURL { get; set; }

        [Display(Name = "Full Name")]
        [Required(ErrorMessage = "Full Name is required")]
        [StringLength(50, MinimumLength = 3, ErrorMessage = "Full Name must be between 3 and 50 chars")]
        public string FullName { get; set; }

        [Display(Name = "Biography")]
        [Required(ErrorMessage = "Biography is required")]
        public string Bio { get; set; }

        //Relationships

        //movie_producer
        public List<Movie_Producer>? Movie_Producers { get; set; }

        //studio

        public Studio? Studio { get; set; }

        public int StudioId { get; set; }
}

and finally Studio model:

 public class Studio
    {
        [Key]
        public int Id { get; set; }

        public string Studio_Name { get; set; }

        public string Owner { get; set; }

        //Relationships
        public List<Producer>? Producers { get; set; }
}

I have tried joining through linq but I am unsure on how to do that since it is necessary to provide a new view model.

On other hand, when I create a new view model I am unsure on how to create relationships allowing me to showcase related data in the view.

The final goal is to link Movies with Studio, not directly, but through Movie_Producers or through Producers. Is this even possible? Thank you!

Tags:

2

Answers


  1. 0

    Use LINQ queries to join the tables and retrieve the desired data.

    var result = context.Table1
    .Join(context.Table2,
        t1 => t1.Table2Id,
        t2 => t2.Id,
        (t1, t2) => new { T1 = t1, T2 = t2 })
    .Join(context.Table3,
        t => t.T2.Id,
        t3 => t3.Table2Id,
        (t, t3) => new { T1 = t.T1, T2 = t.T2, T3 = t3 })
    .Select(x => new
    {
        Table1Data = x.T1,
        Table2Data = x.T2,
        Table3Data = x.T3
    })
    .ToList();

    may help 🙂

    Login or Signup to reply.
  2. 0

    your code is right, but you had done the relationships wrong, adds virtual modifier and uses ICollection. Here is an example:

    public class Movie
{
    [Key]
    public int Id { get; set; }
    public string Name { get; set; }
    public int CinemaId { get; set; }
    [ForeignKey(nameof(CinemaId))]public Cinema? Cinema { get; set; }
    public virtual ICollection<Movie_Producer> Movie_Producers { get; } = new List<Movie_Producer>();
}
public class MovieConfig : IEntityTypeConfiguration<Movie>
{
    void IEntityTypeConfiguration<Movie>.Configure(EntityTypeBuilder<Movie> builder)
    {

    }
}
public class Cinema
{
    [Key]public int Id { get; set; }
    public string Name { get; set; } = null!;
}
public class CinemaConfig : IEntityTypeConfiguration<Cinema>
{
    void IEntityTypeConfiguration<Cinema>.Configure(EntityTypeBuilder<Cinema> builder)
    {
                
    }
}
public class Movie_Producer
{
    [Key]public int Id { get; set; }
    public string Name { get; set; } = null!;
    public int MovieId { get; set; }
    [ForeignKey(nameof(MovieId))] public virtual Movie? Movie { get; set; }
    public int ProducerId { get; set; }
    [ForeignKey(nameof(ProducerId))] public virtual Producer? Producer { get; set; }
}
public class Movie_ProducerConfig : IEntityTypeConfiguration<Movie_Producer>
{
    void IEntityTypeConfiguration<Movie_Producer>.Configure(EntityTypeBuilder<Movie_Producer> builder)
    {

    }
}
public class Producer
{
    [Key]
    public int Id { get; set; }
    public string Name { get; set; } = null!;
    public int StudioId { get; set; }
    [ForeignKey(nameof(StudioId))] public virtual Studio? Studio { get; set; }
    public virtual ICollection<Movie_Producer> Movie_Producers { get; } = new List<Movie_Producer>();
}
public class ProducerConfig : IEntityTypeConfiguration<Producer>
{
    void IEntityTypeConfiguration<Producer>.Configure(EntityTypeBuilder<Producer> builder)
    {

    }
}
public class Studio
{
    [Key]
    public int Id { get; set; }
    public string Name { get; set; } = null!;
    public virtual ICollection<Producer> Producers { get; } = new List<Producer>();
}
public class StudioConfig : IEntityTypeConfiguration<Studio>
{
    void IEntityTypeConfiguration<Studio>.Configure(EntityTypeBuilder<Studio> builder)
    {

    }
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search