How to create ViewModels in ASP.NET CORE MVC application for multiple tables?

Question

I have 3 tables: 1)Agents 2)Agent Queue 3)Agent Nodes. The AgentId is a primary key in 1) and the tables 2) & 3) are dependent on 1. I want to create a model to access properties of these tables for one view. I am new to ASP.NET CORE and it would be great if anyone could point me to the right direction to do the same.

My Model Classes are:

public class Agents
    {
        [Key]
        [DisplayName("Agent Id")]
        public long AgentId { get; set; }
        public string Name { get; set; }
        public string Description { get; set; }
        public string Status { get; set; }
        [DisplayName("Last Checked")]
        public DateTime LastChecked { get; set; }
        public bool Deleted { get; set; }
        public IEnumerable<Agents> agents { get; set; }
        public IEnumerable<AgentMachines> agentMachines { get; set; }
        public IEnumerable<AgentQueues> agentQueues { get; set; }
    }

public class AgentMachines
    {
        [Key]
        [DisplayName("Machine Id")]
        public long MachineId { get; set; }
        [DisplayName("Agent Id")]
        public long AgentId { get; set; }
        public string Address { get; set; }
        public string IP { get; set; }
        [DisplayName("Logged User")]
        public string LoggedUser { get; set; }
        [DisplayName("AD User Account")]
        public string ADUserAccount { get; set; }
        public IEnumerable<Agents> agents { get; set; }
    }

public class AgentQueues
    {
        [Key]
        [DisplayName("Agent Queue Id")]
        public long AgentQueueId { get; set; }
        [DisplayName("Agent Id")]
        public long AgentId { get; set; }
        [DisplayName("Test Id")]
        public long TestId { get; set; }
        [DisplayName("Value Set Id")]
        public long ValueSetId { get; set; }
        [DisplayName("Process Id")]
        public string ProcessId { get; set; }
        public string Status { get; set; }
        [DisplayName("Update User")]
        public string UpdateUser { get; set; }
        [DisplayName("Schedule Time")]
        public DateTime? ScheduleTime { get; set; }
        [DisplayName("Update Date")]
        public DateTime? UpdateDate { get; set; }
        public bool Deleted { get; set; }
        [DisplayName("Test")]
        public string TestName { get; set; }
        [DisplayName("Value Set")]
        public string ValueSetName { get; set; }
        public IEnumerable<Agents> agents { get; set; }
    }

public class TestAgentDetail
    {
        public IEnumerable<AgentMachines> agentMachines { get; set; }
        public IEnumerable<AgentQueues> agentQueues { get; set; }
        public IEnumerable<Agents> agents { get; set; }
    }

The controller code I have so far is:

public async Task<IActionResult> Details(long? id)
        {
            var viewModel = new TestAgentDetail();
            viewModel.agents = await _context.Agents
                  .Include(i => i.agentMachines)
                  .Include(i => i.agentQueues)
                  .AsNoTracking()
                  .ToListAsync();

            if (id != null)
            {
                Agents agent = viewModel.agents.Where(
                    i => i.AgentId == id.Value).Single();
                viewModel.agentMachines = agent.agentMachines.Select(s => s.LoggedUser);
            }

            return View(viewModel);
        }

Answer 1

Not sure the detailed relationship between your three tables.Here is a working demo with Agents and AgentQueue one-to-many relationship,with Agents and AgentsNodes one-to-many relationship.Create a AgentViewModel to show the data:

Model:

public class Agents
{
    [Key]
    public int AgentId { get; set; }
    public string AgentName { get; set; }
    public AgentQueue AgentQueue { get; set; }
    public List<AgentNodes> AgentNodes { get; set; }
}
public class AgentQueue
{
    public int Id { get; set; }
    public string QueueName { get; set; }
    public int AgentId { get; set; }
    public Agents Agents { get; set; }
}
public class AgentNodes
{
    public int Id { get; set; }
    public string NodeName { get; set; }
    public Agents Agents { get; set; }
}
public class AgentViewModel
{
    public int AgentId { get; set; }
    public string AgentName { get; set; }
    public string QueueName { get; set; }
    public List<string> NodeName { get; set; }
}

View:

@model IEnumerable<AgentViewModel>

<h1>Index</h1>

<p>
    <a asp-action="Create">Create New</a>
</p>
<table class="table">
    <thead>
        <tr>
            <th>
                @Html.DisplayNameFor(model => model.AgentId)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.AgentName)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.NodeName)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.QueueName)
            </th>
            <th></th>
        </tr>
    </thead>
    <tbody>
@foreach (var item in Model) {
        <tr>
            <td>
                @Html.DisplayFor(modelItem => item.AgentId)
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.AgentName)
            </td>
            <td>
                @foreach (var node in item.NodeName)
                {
                    @Html.DisplayFor(modelItem => node)
                    <br />
                }
            </td>
            <td>
                @Html.DisplayFor(model => item.QueueName)
            </td>
        </tr>
}
    </tbody>
</table>

Controller:

public class AgentsController : Controller
{
    private readonly MvcProjContext _context;

    public AgentsController(MvcProjContext context)
    {
        _context = context;
    }

    // GET: Agents
    public async Task<IActionResult> Index()
    {
        var model = await _context.Agents.Include(a => a.AgentQueue).Include(a => a.AgentNodes)
                                         .Select(a => new AgentViewModel()
                                         {
                                             AgentId=a.AgentId,
                                             AgentName = a.AgentName,
                                             NodeName = a.AgentNodes.Select(an=>an.NodeName).ToList(),
                                             QueueName = a.AgentQueue.QueueName
                                         }).ToListAsync();
        return View(model);
    }
}

DbContext:

public class MvcProjContext : DbContext
{
    public MvcProjContext (DbContextOptions<MvcProjContext> options)
        : base(options)
    {
    }

    public DbSet<Agents> Agents { get; set; }
    public DbSet<AgentNodes> AgentNodes { get; set; }
    public DbSet<AgentQueue> AgentQueue { get; set; }
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        //configure one-to-one relationship
        modelBuilder.Entity<Agents>()
            .HasOne(a => a.AgentQueue)
            .WithOne(b => b.Agents)
            .HasForeignKey<AgentQueue>(b => b.AgentId);
    }
}

Result: