Getting Data in a List using four different tables

Question

I have the following database tables

Employee table -Database name Employee - EmployeeID is the primary key

JobTitleID    EmployeeNumber   SectionID    EmployeeId 
9               1234          2              1
2               8976          3              2
4               7654          1              3

I have another table called Job Title --Database name Employee - prmary key JobTitleID

JobTitleID    JobTitle
9               CEO
2               Developer
4               Executive

Section Table - Database name Employee- prmary key SectionID

SectionID        Section
1               manager
2               IT
3               Branch

Another table called tblEmployee that reside in another database name - Tracking - primary Key EmployeeNumber

EmployeeNumber       EmployeeName
1234                 TestEmpName1
8976                 TestEmpName2
7654                 TestEmpName3 

I am using database first approach to create the context class. I need this data to populate a table in the Razor view. How can I get the EmployeeName, JobTitle, section in a LINQ query and then populate a List with this data. I tried writing a stored prcoedure called GetEmployeeData like this:

SELECT JobTitleID, EmployeeNumber, SectionID, JobTitle, section, EmployeeName
FROM Employee e LEFT OUTER JOIN JOBTitle j ON e.JobTitleID = j.JobTitleID
LEFT OUTER JOIN SECTION s ON e.SectionID  = s.SectionID 
LEFT OUTER JOIN  Tracking.dbo.tblEmployee t ON t.EmployeeNumber = e.EmployeeNumber

I need to populate a List with this data and then populate a table in razor view. I can go with LINQ or SQL stored prcoedure.

I tried doing this:

List<Employee> query = await _empContext.Set<Employee>().FromSqlRaw($"EXECUTE dbo.GetEmployeeData").ToListAsync();
            foreach(var item in query)

I need JobTitle Section EmployeeName in a table in razor view.

any help will be greatly appreciated.

Answer 1

Hi @Anjali Agarwal

From your description, you have already created the stored procedure, then, you can be based on the return data to create an Entity as below:

    public class EmployeeSPResult
    { 
        public int JobTitleID { get; set; }
        public int EmployeeNumber { get; set; }
        public int SectionID { get; set; }
        public string JobTitle { get; set; }
        public string section { get; set; }
        public string EmployeeName { get; set; } 
    }

Then, add DbSet for the EmployeeSPResult in the DBContext, like this:

    public class ApplicationDbContext : IdentityDbContext
    {
        public DbSet<Employee> Employees { get; set; }
        public DbSet<JOBTitle> JOBTitles { get; set; }
        public DbSet<SECTION> SECTIONs { get; set; }
        public DbSet<tblEmployee> tblEmployees { get; set; }

        public DbSet<EmployeeSPResult> EmployeeSPResult { get; set; }
        protected override void OnModelCreating(ModelBuilder builder)
        {
            base.OnModelCreating(builder);
            builder.Entity<EmployeeSPResult>().HasNoKey();
        }

After that use the following command to enable migration:

add-migration addEmployeeSPResult
update-database

Then, create a EmployeeIndex razor page to display the result:

EmployeeIndex.cshtml.cs:

    public class EmployeeIndexModel : PageModel
    {
        private readonly ApplicationDbContext _dbContext;

        public EmployeeIndexModel(ApplicationDbContext applicationDbContext)
        {
            _dbContext=applicationDbContext;
        }

        [BindProperty]
        public List<EmployeeSPResult> Employees { get; set; } 
        public void OnGet()
        {
           Employees =  _dbContext.Set<EmployeeSPResult>().FromSqlRaw($"EXECUTE dbo.GetEmployeeData").ToList();
 
            EmployeeList = EmployeeList.Select(c => new EmployeeViewModel()
            {
                EmployeeName = c.EmployeeName,
                section = c.section,
                JobTitle = c.JobTitle
            }).ToList();
        }
    }

EmployeeIndex.cshtml: loop through the Employees and display the required properties:

@page "/EmployeeIndex"
@model RazorTest.Pages.EmployeeIndexModel
<table class="table">
    <thead>
        <tr> 
            <th>
                JobTitle
            </th>
            <th>
                Section
            </th>
            <th>
                EmployeeName
            </th>
        </tr>
    </thead>
    <tbody>
        @foreach (var item in Model.Employees)
        {
            <tr> 
                <td>
                    @Html.DisplayFor(modelItem => item.JobTitle)
                </td>
                <td>
                    @Html.DisplayFor(modelItem => item.section)
                </td>
                <td>
                    @Html.DisplayFor(modelItem => item.EmployeeName)
                </td>
            </tr>
        }
    </tbody>
</table>

The result as below:

In the above code, it will return all the properties from back-end to view page, if you don't want to use this method, you can also create a EmployeeViewModel like this:

    public class EmployeeViewModel
    {
        public string JobTitle { get; set; }
        public string section { get; set; }
        public string EmployeeName { get; set; }
    }

Then, in the EmployeeIndex.cshtml.cs, after getting the return data from Stored procedure, you can use the LINQ select clause to convert the model to view model, and then return the view model to the razor page.

        [BindProperty]
        public List<EmployeeSPResult> Employees { get; set; }
        [BindProperty]
        public List<EmployeeViewModel> EmployeeList { get; set; }
        public void OnGet()
        {
           Employees =  _dbContext.Set<EmployeeSPResult>().FromSqlRaw($"EXECUTE dbo.GetEmployeeData").ToList();

            EmployeeList = Employees.Select(c => new EmployeeViewModel()
            {
                EmployeeName = c.EmployeeName,
                section = c.section,
                JobTitle = c.JobTitle
            }).ToList();
        }

Then, in the EmployeeIndex.cshtml page, loop through the EmployeeList and display the data.

---

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,

Dillion