Create a view that contains columns from 3 tables

user_14001541 0
Viewed 71

I am new to Visual Studio and I need your help.

I'm working with .NET Core 3.1, C#, MVC and Identity, I am trying to create a view that contains columns from 3 tables.

The expected result it would be like this:

Personal info
Last name: xxx
First name: xxx

Skills
Skill ID: 1   Skill: xxx
Skill ID: 2   Skill: xxx
Skill ID: 3   Skill: xxx

Jobs
Job ID: 1   Employee: xxx   Subject: xxx
Job ID: 2   Employee: xxx   Subject: xxx

What can I do to have the above view?

The 3 models that I have are:

public class ApplicationUser : IdentityUser
{
   public string StuLna { get; set; } // Last name
   public string StuFna { get; set; } // First name   
   …
   public virtual ICollection<TblSkill> TblSkills { get; set; }
   public virtual ICollection<TblJobHistory> TblJobHistories { get; set; }
}

public class TblSkill
{
   [Key]
   public int    SkiIde { get; set; } // Skill ID
   public string SkiSki { get; set; } // Skill
   …
   [ForeignKey("ApplicationUser")]
   public string  AppUserId { get; set; } // ApplicationUser Id
   public virtual ApplicationUser ApplicationUser { get; set; }
}

public class TblJobHistory
{
   [Key]
   public int    JobIde { get; set; } // Job ID
   public string JobEmp { get; set; } // Employee
   public string JobSub { get; set; } // Subject
   …
   [ForeignKey("ApplicationUser")]
   public string  AppUserId { get; set; } // ApplicationUser Id
   public virtual ApplicationUser ApplicationUser { get; set; }
}

The DbContext:

public class AppDBContext : IdentityDbContext<ApplicationUser, IdentityRole, string>
{
   public AppDBContext(DbContextOptions<AppDBContext> options)
   : base(options) 
   { 
   }

   public virtual DbSet<TblSkill> TblSkills { get; set; }
   public virtual DbSet<TblJobHistory> TblJobHistories { get; set; }

   protected override void OnModelCreating(ModelBuilder modelBuilder)
   {
      base.OnModelCreating(modelBuilder);

      modelBuilder.Entity<TblSkill>()
         .HasOne(a => a.ApplicationUser)
         .WithMany(s => s.TblSkills)
        .HasForeignKey(a => a.AppUserId);

      modelBuilder.Entity<TblJobHistory>()
         .HasOne(a => a.ApplicationUser)
         .WithMany(j => j.TblJobHistories)
        .HasForeignKey(a => a.AppUserId);

      public DbSet<MyApp.ViewModels.PersonalInfoVM> PersonalInfoVM { get; set; }
}

So far I have created these:

View model:

public class PersonalInfoVM
{
   [Key]
   public string  AppUserId { get; set; } // ApplicationUser Id
   public virtual ApplicationUser ApplicationUsers { get; set; }
   public virtual TblSkill TblSkills { get; set; }
   public virtual TblJobHistory TblJobHistories { get; set; }
}

Controller:

public IActionResult PersonalInfoPreview()
{
   var userid = _userManager.GetUserId(HttpContext.User);
      
   List<ApplicationUser> users = _context.Users.ToList();
   List<TblSkill> skills = _context.TblSkills.ToList();
   List<TblJobHistory> jobHistories = _context.TblJobHistories.ToList();
         
   var model = from u in users

   join s in skills on u.Id equals s.AppUserId into table1
      from s in table1.ToList()
      join j in jobHistories on u.Id equals j.AppUserId into table2
      from j in table2.ToList()
      select new PersonalInfoVM
         {
            ApplicationUsers = u,
            TblSkills = s,
            TblJobHistories = j
         };

   return View(model);
}

View page:

@model IEnumerable<MyApp.ViewModels.PersonalInfoVM >
@{ViewData["Title"] = "PersonalInfoPreview";}

<table class="table">
    <thead>
        <tr>
            <th></th>
        </tr>
    </thead>
    <tbody>
        @foreach (var item in Model)
        {
            <tr>
                <td>@item.ApplicationUsers.StuLna</td>
            </tr>
        }

        @foreach (var item in Model)
        {
            <tr>
                <td>@item.TblSkills.SkiSki</td>
            </tr>
        }

        @foreach (var item in Model)
        {
            <tr>
                <td>@item.TblJobHistories.HisSta</td>
            </tr>
        }

    </tbody>
</table>

This is the result for the current user with 2 skills and 2 jobs:

Papadopoulos
Papadopoulos
Papadopoulos
Papadopoulos
user1-skill1
user1-skill1
user1-skill2
user1-skill2
user1-employee1
user1-employee2
user1-employee1
user1-employee2
asp.net-core-mvc asp.net-identity .net-core
3 Answers

There are a couple of issues in your code application what surpasses the scope of your question.

The first thing to do is change you View Model:

public class ViewModel
{
    public string FirstName { get; set; }
    public string LastName { get; set; }

    public List<Skills> Skills { get; set; }
    public List<Jobs> Jobs { get; set; }

}

Your code int he Controller can then be replaced by:

var model = _context.Users.Where(x => xAppUserId == userid).Include(x => x.TblSkills).Include(x => x.TblJobHistories).Select(x => new ViewModel()
{
    FirstName = x.FirstName,
    LastName = x.LatNsme,
    Skills = x.TblSkills.ToList(),
    Jobs = x.TblJobHistories.ToList()
}).FirstOrDefault();

return View(model);

This should output the result as your example at the start of the question. In your view it looks like you want to return all users, you will then just have to remove the where clause and change the FirstOrDefault to ToList()

Please note that your view will have to reflect the update ViewModel

user_25212370

I would set your ViewModel as below:

public class PersonalInfoVM
{
   public string  AppUserId { get; set; } // ApplicationUser Id
   public ApplicationUser ApplicationUser { get; set; }
   public IEnumerable<TblSkill> TblSkills { get; set; }
   public IEnumerable<TblJobHistory> TblJobHistories { get; set; }
}

and load it like:

public IActionResult PersonalInfoPreview()
{
   var vm = new PersonalInfoVM();
   vm.AppUserId = _userManager.GetUserId(HttpContext.User);
   vm.ApplicationUser = _userManager.FindByName(User.Identity.Name);
   vm.TblSkills = _context.TblSkills.Where(x=>x.AppUserId == vm.AppUserId).ToList();
   vm.TblJobHistories = _context.TblJobHistories.Where(x=>x.AppUserId == vm.AppUserId).ToList();
   return View(vm);
}

and finally show it like:

@model  MyApp.ViewModels.PersonalInfoVM;
@{ViewData["Title"] = "PersonalInfoPreview";}

<table class="table">
    <tbody> 
            <tr>
                <td colspan="3">Personal info</td>
            </tr>
            <tr>
                <td colspan="2">First name</td>
                <td>@Model.ApplicationUser.FirstName</td>
            </tr>
         
            <tr>
                <td colspan="2">Last name</td>
                <td>@Model.ApplicationUser.LastName</td>
            </tr>
         

        @foreach (var item in Model.TblSkills)
        {
            <tr>
                <td colspan="2">@item.SkiIde</td>
                <td>@item.SkiSki</td>
            </tr>
        } 
        @foreach (var item in Model.TblJobHistories )
        {
            <tr>
                <td>@item.JobIde </td>
                <td>@item.JobEmp </td>
                <td>@item.JobSub </td>
            </tr>
        }

    </tbody>
</table>
user_10739730

According to your code, I suppose you want to query the specific user information and get the related Skills and JobHistories. If that the is the case, since you have already configure one-to-many relationship for these table, I think you could use the Include method to find the related entities. please refer the following sample code:

DBcontext:

public class ApplicationDbContext : IdentityDbContext<ApplicationUser>
{
    public DbSet<Projects> Projects { get; set; }
    public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
        : base(options)
    {
        
    }

    public DbSet<Blog> Blogs { get; set; }
    public DbSet<Post> Posts { get; set; }
    public DbSet<TestTable> TestTables { get; set; }



    public virtual DbSet<TblSkill> TblSkills { get; set; }
    public virtual DbSet<TblJobHistory> TblJobHistories { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
        modelBuilder.Entity<Blog>()
            .HasMany(b => b.Posts)
            .WithOne();
    }
}

Code in the controller:

    public IActionResult Details()
    {
        //query the application user table and find the related entities.
        var result = _context.Users
            .Include(c=>c.TblSkills)
            .Include(c=>c.TblJobHistories)
            .Where(c => c.UserName == "Dillion")
            .Select(c=> new PersonalInfoVM() { AppUserId = c.Id, ApplicationUsers = c }).FirstOrDefault();
        return View(result);
    }

View Model:

public class PersonalInfoVM
{
    [Key]
    public string AppUserId { get; set; } // ApplicationUser Id
    public virtual ApplicationUser ApplicationUsers { get; set; }
}

Code in the View page:

@model EFCore.Models.PersonalInfoVM

@{
    ViewData["Title"] = "Details";
    Layout = "~/Views/Shared/_Layout.cshtml";
}

<h1>Details</h1>

<div>
    <h4>PersonalInfoVM</h4>
    <hr />
    <div>
        <dl class="row">
            <dt class="col-sm-2">
                @Html.DisplayNameFor(model => model.AppUserId)
            </dt>
            <dd class="col-sm-10">
                @Html.DisplayFor(model => model.AppUserId)
            </dd>
        </dl>
        <dl class="row">
            <dt class="col-sm-2">
                @Html.DisplayNameFor(model => model.ApplicationUsers.UserName)
            </dt>
            <dd class="col-sm-10">
                @Html.DisplayFor(model => model.ApplicationUsers.UserName)
            </dd>
        </dl>
        @foreach (var item in Model.ApplicationUsers.TblSkills)
        {
            <dl class="row">
                <dt class="col-sm-2">
                    SkiSki
                </dt>
                <dd class="col-sm-10">
                    @item.SkiSki
                </dd>
            </dl>
        }
        @foreach (var item in Model.ApplicationUsers.TblJobHistories)
        {
            <dl class="row">
                <dt class="col-sm-2">
                    Employee:
                </dt>
                <dd class="col-sm-10">
                    @item.JobEmp
                </dd>
            </dl>
            <dl class="row">
                <dt class="col-sm-2">
                    Subject:
                </dt>
                <dd class="col-sm-10">
                    @item.JobSub
                </dd>
            </dl>
        }
    </div>
</div>
<div>
    @Html.ActionLink("Edit", "Edit", new { /* id = Model.PrimaryKey */ }) |
    <a asp-action="Index">Back to List</a>
</div>

The result like this:

enter image description here

More detail information about reading related data, you could check this document.

user_70622910
Related