getting the data from two different tables in a list

Question

I have following two tables Employee and file Path

Employee - employeeId is primary key with Identity specification

EmployeeId   FirstName   lastName              
1               Mario		Gonazales
2               Mario2       Gonazales2
3               Mario3       Gonazales3

FilePath-FilePathID is primary key with Identity specification- employeeId is the foreign key in employee Table

FilePathID      EmployeeId  UploadFilePath
1                3            C:\File1.pdf
2                3            C:\File2.pdf
3                3            C:\File3.pdf
4                2            C:\File5.pdf
5                2            C:\File9.pdf
6                1            C:\File1.pdf

Basically there can be 1 or multiple files associated with each employee. I am storing the files in a FilePath table and employee data

in employee Table

This is how my model looks like

public partial class Employee
{
public int EmployeeId { get; set; }
public string FirstName { get; set; }
public string lastName { get; set; }
public List<FilePath> Files { get; set; }
}

 public partial FilePath
 {
 public int FilePathID { get; set; }
public string EmployeeId { get; set; }
public string UploadFilePath { get; set; }
 
 }

How can I create a list like this getting the data from the database. One of the list item looks like this:

List<Employee> = new List<Employee>()
            {
                new EmployeeModel() {
                    EmployeeID = 1,
                    FirstName = "Mario",
                    lastname ="Gonazales",
                    Files = new List<FileModel>()
                    {
                        new FileModel {EmployeeId = 1, FilePath = "DisplayFiles//employee1File_1"},
                        new FileModel {EmployeeId = 1, FilePath = "DisplayFiles//employee1File_3"},
                    }
                   
                }

			There will be several items getting the data from the database.

any help will be greatly appreciated.

Answer 1

Hi @Anjali Agarwal,

Here is my test result, you can check it first and follow my steps to fix it.

I follow your steps and test in my local, here is my data in Database.

My DbContext

using AspCore7_Web_Identity.Areas.Identity.Data;
using AspCore7_Web_Identity.Models;
using Microsoft.AspNetCore.Identity;
using Microsoft.AspNetCore.Identity.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore;
using System.Data;

namespace AspCore7_Web_Identity.Areas.Identity.Data;

public class SqlDataContext : IdentityDbContext<Users>
{
    public SqlDataContext()
    {
    }

    public SqlDataContext(DbContextOptions<SqlDataContext> options)
        : base(options)
    {
    }

    public DbSet<EmployeeModel> Employee { get; set; }
    public DbSet<FilePathModel> FilePath { get; set; }

    protected override void OnModelCreating(ModelBuilder builder)
    {
        base.OnModelCreating(builder);
        // Customize the ASP.NET Identity model and override the defaults if needed.
        // For example, you can rename the ASP.NET Identity table names and more.
        // Add your customizations after calling base.OnModelCreating(builder);
    }
}

My Model

using Microsoft.CodeAnalysis.Elfie.Serialization;
using System.ComponentModel.DataAnnotations;

namespace AspCore7_Web_Identity.Models
{
    public partial class EmployeeModel
    {
        [Key]
        public int EmployeeId { get; set; }
        public string? FirstName { get; set; }
        public string? lastName { get; set; }
        public List<FilePathModel>? Files { get; set; }
    }

    public class EmployeeDTO
    {
        public int EmployeeId { get; set; }
        public string? FirstName { get; set; }
        public string? LastName { get; set; }
        public List<FileDTO>? Files { get; set; }
    }
}

and

using System.ComponentModel.DataAnnotations;

namespace AspCore7_Web_Identity.Models
{
    public partial class FilePathModel
    {
        [Key]
        public int FilePathID { get; set; }
        public int EmployeeId { get; set; }
        public virtual EmployeeModel? Employee { get; set; }
        public string? UploadFilePath { get; set; }

    }

    public class FileDTO
    {
        public int EmployeeId { get; set; }
        public string? UploadFilePath { get; set; }
    }
}

My test method in controller.

using AspCore7_Web_Identity.Areas.Identity.Data;
using AspCore7_Web_Identity.Models;
using Microsoft.AspNetCore.Identity;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using Service.IService;
using System.Data;

namespace AspCore7_Web_Identity.Controllers
{
    public class TestController : Controller
    {
        private readonly ILogger<TestController> _logger;

        private readonly SqlDataContext _dbContext;

        public TestController(ILogger<TestController> logger, SqlDataContext dbContext)
        {
            _logger = logger;
            _dbContext = dbContext;
        }
        public IActionResult testsql()
        {
            using (var context = new SqlDataContext())
            {
                var employees = _dbContext.Employee.Include(e => e.Files).ToList();

                var result = employees.Select(e => new EmployeeDTO
                {
                    EmployeeId = e.EmployeeId,
                    FirstName = e.FirstName,
                    LastName = e.lastName,
                    Files = e.Files?.Select(f => new FileDTO
                    {
                                EmployeeId = f.EmployeeId,
                                UploadFilePath = $"DisplayFiles//employee{f.EmployeeId}File_{f.FilePathID}" 
                            }).ToList()
                }).ToList();
                return Ok(result);
            }
   
        }
    }
}

---

If the answer is the right solution, please click "Accept Answer" and kindly 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.

Best regards,

Jason