MVC join multiple tables with IEnumerable model-CodePudding

I am very very new to entity framework so I might have done terrible mistakes. I have two tables in database and I want to join these tables to show in one view page.My code:

CompanyController

using CompanyData.DAL;
using Microsoft.AspNetCore.Mvc;
using CompanyData.Models;

 
namespace CompanyData.Controllers
{
    public class CompanyController : Controller
    {
        private readonly CompanyDbContext _context;
        private readonly JoinAndViewModel _joinAndViewModel;
 
 
        public CompanyController(CompanyDbContext context, JoinAndViewModel joinAndViewModel)
        {
            this._context = context;
            this._joinAndViewModel = joinAndViewModel;
        }
 
        //private ProjectContext db = new ProjectContext();
 
        public IActionResult YetkiYoksa()
        {
            IEnumerable<Company> objCompaniesList = _context.Companies;
            IEnumerable<Component> objComponentList = _context.Components;
            
            var joinAndViewModel = from c in objCompaniesList join co in objComponentList on c.id equals co.company_id
            select new JoinAndViewModel { CustC = (IEnumerable<Company>)c, CustCo = (IEnumerable<Component>)co };
            return View(joinAndViewModel); 
       
        }
 
    }
}

CompanyDbContext

using CompanyData.Models;
using Microsoft.EntityFrameworkCore;
using Microsoft.AspNetCore.Mvc;
using System.Collections.Generic;
 
namespace CompanyData.DAL
{
    public class CompanyDbContext : DbContext
    {
    
 
        public CompanyDbContext(DbContextOptions options) : base(options)
        {
 
        }
        public DbSet<Company> Companies { get; set; }
        public DbSet<Component> Components { get; set; }
        
    }
 
 
}

I also prepared Component and Company classes.

and one model to join (?) these tables: JoinAndViewModel

namespace CompanyData.Models
{
    public class JoinAndViewModel
    {
        public IEnumerable<Company> CustC { get; set; }
        public IEnumerable<Component> CustCo { get; set; }      
    }
}

And a view that I don't know what to use as model to be able to use "foreach": `

@model IEnumerable<JoinAndViewModel>
<table  id="dataTable" width="100%" cellspacing="0">

    <thead>
        <tr>
            <th>
                Company Name
            </th>

            <th>
                Company Type
            </th>
            <th>
                HQ Region
            </th>        
        </tr>
    </thead>
    <tbody>
        @foreach (var company in Model)
        {

            <tr>
                <td>@company.CustC.company_name</td> 
                <td>@company.CustC.company_type</td>
                <td>@company.CustC.hq_region</td>        
        }
    </tbody>
</table>

Any help is greatly appreciated.

CodePudding user response：

so from my understanding of everything is.Are you doing code first approach or SQL ? are your Models a Many to many relationship? or One company and many components?

If Your doing a code first approach your Moduls would be something like example below. This way gives asp.net.core gives you a shared table. Of CompanyComponentTable where The Ids are the shared identifier

 public class Company
  {[Key]
public int CompanyId { get; set; }
public int ComponentId { get; set; }
public ICollection<Component> Components{ get; set; }
 }  
public class Component
{   [Key]
    public int ComponentId { get; set; }

   public int CompanyId { get; set; }
   public ICollection<BookCategory> BookCategories { get; set; }
}

This would be a one to many.

public class Component
{   [Key]
    public int ComponentId { get; set; }

   public int CompanyId { get; set; }
   public BookCategory BookCategories { get; set; }
}

The one to many gives you a List of components inside every Company. Either way gives you a table where you can fetch the other part you need. Depends on what you want with the code.

CodePudding user response：

you have to fix model

 var joinAndViewModel = new JoinAndViewModel  
{
 CustC = _context.Companies.ToList(),
 CustCo = _context.Components.ToList()
};
return View(joinAndViewModel);

and the view, replace IEnumerable with JoinAndViewModel

@model JoinAndViewModel

....

    <tbody>
        @foreach (var c in Model.CustC)
{
 <tr>
                <td>@c.company_name</td> 
                <td>@c.company_type</td>
                <td>@c.hq_region</td>  
</tr      
}
....