Including Child from Table Valued Function In Parent Model

Question

How can I add a child entity that is derived from a table valued function to a parent model? I only need to read, no create, update or delete needed.

I've made two attemtps, v1 is to load the child into an IQuerayable and compose over it into the parent, v2 is listed below also.

Details.cshtml.cs v1
public class DetailsModel : PageModel
{
private readonly EmployeeCertification.Models.Scaffold.EmployeeCertificationDBContext _context;

    public DetailsModel(EmployeeCertification.Models.Scaffold.EmployeeCertificationDBContext context)  
    {  
        _context = context;  
    }  
  
    public Employee Employee { get; set; }  
  
    public async Task<IActionResult> OnGetAsync(int? id)  
    {  
        if (id == null)  
        {  
            return NotFound();  
        }  
  
        IQueryable<Models.EmployeeCertificationCompliance> employeeCertificationCompliaceIQ  
            = _context.EmployeeCertificationCompliances  
            .FromSqlRaw("SELECT * FROM dbo.EmployeeCertificationCompliance(DEFAULT,DEFAULT)");  
  
        Employee = await (from a in employeeCertificationCompliaceIQ join x in _context.Employees on a.EmployeeId equals x.EmployeeId where a.EmployeeId == id select new Employee { Department = x.Department, EmployeeStatus = x.EmployeeStatus, JobTitle = x.JobTitle }).FirstOrDefaultAsync();  
  
        if (Employee == null)  
        {  
            return NotFound();  
        }  
        return Page();  
    }  
}  

Details.csthml.cs v2

public class DetailsModel : PageModel  
{  
    private readonly EmployeeCertification.Models.Scaffold.EmployeeCertificationDBContext _context;  
  
    public DetailsModel(EmployeeCertification.Models.Scaffold.EmployeeCertificationDBContext context)  
    {  
        _context = context;  
        Employee.EmployeeCertificationCompliances = new List<EmployeeCertification.Models.EmployeeCertificationCompliance>();  
    }  
  
    public Employee Employee { get; set; }  
  
    public void OnGet(int id)  
    {  
        Employee = _context.Employees.Where(e => e.EmployeeId == id).FirstOrDefault();  
        if (Employee != null)  
        {  
            Employee.EmployeeCertificationCompliances = _context.EmployeeCertificationCompliances.FromSqlRaw("SELECT * FROM dbo.EmployeeCertificationCompliance(DEFAULT,DEFAULT)").ToList();  
        }  
    }  
  
  
}  

Details.csthml
@Anonymous
@默 EmployeeCertification.Pages.Employees.DetailsModel

@{  
    ViewData["Title"] = "Details";  
}  
  
<h1>Details</h1>  
  
<div>  
    <h4>Employee</h4>  
    <hr />  
    <dl class="row">  
        <dt class="col-sm-2">  
            @Html.DisplayNameFor(model => model.Employee.EmployeeCode)  
        </dt>  
        <dd class="col-sm-10">  
            @Html.DisplayFor(model => model.Employee.EmployeeCode)  
        </dd>  
        <dt class="col-sm-2">  
            @Html.DisplayNameFor(model => model.Employee.First)  
        </dt>  
        <dd class="col-sm-10">  
            @Html.DisplayFor(model => model.Employee.First)  
        </dd>  
        <dt class="col-sm-2">  
            @Html.DisplayNameFor(model => model.Employee.Initial)  
        </dt>  
        <dd class="col-sm-10">  
            @Html.DisplayFor(model => model.Employee.Initial)  
        </dd>  
        <dt class="col-sm-2">  
            @Html.DisplayNameFor(model => model.Employee.Last)  
        </dt>  
        <dd class="col-sm-10">  
            @Html.DisplayFor(model => model.Employee.Last)  
        </dd>  
        <dt class="col-sm-2">  
            @Html.DisplayNameFor(model => model.Employee.Suffix)  
        </dt>  
        <dd class="col-sm-10">  
            @Html.DisplayFor(model => model.Employee.Suffix)  
        </dd>  
        <dt class="col-sm-2">  
            @Html.DisplayNameFor(model => model.Employee.PersonalEmailAddress)  
        </dt>  
        <dd class="col-sm-10">  
            @Html.DisplayFor(model => model.Employee.PersonalEmailAddress)  
        </dd>  
        <dt class="col-sm-2">  
            @Html.DisplayNameFor(model => model.Employee.CompanyEmailAddress)  
        </dt>  
        <dd class="col-sm-10">  
            @Html.DisplayFor(model => model.Employee.CompanyEmailAddress)  
        </dd>  
        <dt class="col-sm-2">  
            @Html.DisplayNameFor(model => model.Employee.HireRehireDate)  
        </dt>  
        <dd class="col-sm-10">  
            @Html.DisplayFor(model => model.Employee.HireRehireDate)  
        </dd>  
        <dt class="col-sm-2">  
            @Html.DisplayNameFor(model => model.Employee.SupervisorId)  
        </dt>  
        <dd class="col-sm-10">  
            @Html.DisplayFor(model => model.Employee.SupervisorId)  
        </dd>  
        <dt class="col-sm-2">  
            @Html.DisplayNameFor(model => model.Employee.Department)  
        </dt>  
        <dd class="col-sm-10">  
            @Html.DisplayFor(model => model.Employee.Department.DepartmentCode)  
        </dd>  
        <dt class="col-sm-2">  
            @Html.DisplayNameFor(model => model.Employee.EmployeeStatus)  
        </dt>  
        <dd class="col-sm-10">  
            @Html.DisplayFor(model => model.Employee.EmployeeStatus.EmployeeStatusCode)  
        </dd>  
        <dt class="col-sm-2">  
            @Html.DisplayNameFor(model => model.Employee.JobTitle)  
        </dt>  
        <dd class="col-sm-10">  
            @Html.DisplayFor(model => model.Employee.JobTitle.JobTitleCode)  
        </dd>  
    </dl>  
</div>  
<div>  
    <a asp-page="./Index">Back to List</a>  
</div>  
  
<table class="table table-sm">  
    <thead>  
        <tr>  
            <th class="text-center" colspan="2">Test</th>  
        </tr>  
    </thead>  
    @foreach (var i in Model.Employee.EmployeeCertificationCompliances)  
    {  
        <tr>  
            <td>  
            {@i.CertificationName}  
            </td>  
        </tr>  
    }  
</table>  

v1 Error Message

v2 Error Message

Parent Model

public partial class Employee  
{  
    public Employee()  
    {  
        CertificationRuleEmployees = new HashSet<CertificationRuleEmployee>();  
        EmployeeCertifications = new HashSet<EmployeeCertification>();  
        EmployeeHistories = new HashSet<EmployeeHistory>();  
    }  
  
    public int EmployeeId { get; set; }  
    public string EmployeeCode { get; set; }  
    public string First { get; set; }  
    public string Initial { get; set; }  
    public string Last { get; set; }  
    public string Suffix { get; set; }  
    public byte? EmployeeStatusId { get; set; }  
    public int? JobTitleId { get; set; }  
    public string PersonalEmailAddress { get; set; }  
    public string CompanyEmailAddress { get; set; }  
    public int? DepartmentId { get; set; }  
    public DateTime? HireRehireDate { get; set; }  
    public int? SupervisorId { get; set; }  
  
    public virtual Department Department { get; set; }  
    public virtual EmployeeStatus EmployeeStatus { get; set; }  
    public virtual JobTitle JobTitle { get; set; }  
    public virtual ICollection<CertificationRuleEmployee> CertificationRuleEmployees { get; set; }  
    public virtual ICollection<EmployeeCertification> EmployeeCertifications { get; set; }  
    public virtual ICollection<EmployeeHistory> EmployeeHistories { get; set; }  
  
    /*manual*/  
    public virtual ICollection<Models.EmployeeCertificationCompliance> EmployeeCertificationCompliances { get; set; }  
}  

Child Model

public partial class EmployeeCertificationCompliance  
{  
    public int EmployeeCertificationComplianceId { get; set; }  
    public string ComplianceStatusName { get; set; }  
    public int EmployeeId { get; set; }  
    public string EmployeeCode { get; set; }  
    public string First { get; set; }  
    public string Last { get; set; }  
    public string JobTitleName { get; set; }  
    public string DepartmentCode { get; set; }  
    public string DepartmentName { get; set; }  
  
    [DisplayFormat(DataFormatString = "{0:yyyy/MM/dd}", ApplyFormatInEditMode = true)]  
    public DateTime? HireRehireDate { get; set; }  
    public string CertificationName { get; set; }  
    public string RuleName { get; set; }  
    public bool AppliesToAllEmployees { get; set; }  
    public int? NumberOfYearsDue { get; set; }  
  
    [DisplayFormat(DataFormatString = "{0:yyyy/MM/dd}", ApplyFormatInEditMode = true)]  
    public int? CertificationDate { get; set; }  
  
    public virtual Scaffold.Employee Employee { get; set; }  
}  

EmployeeCertificationDBContext.cs (only relevant excerpts shown)

modelBuilder.Entity<EmployeeCertificationCompliance>(entity =>  
    {  
  
        entity.HasKey(c => new { c.EmployeeCertificationComplianceId });  
  
        entity.HasOne(d => d.Employee)  
            .WithMany(p => p.EmployeeCertificationCompliances)  
            .HasForeignKey(d => d.EmployeeId);  
  
    });  
  
        modelBuilder.Entity<Employee>(entity =>  
        {  
            entity.ToTable("Employee");  
  
            entity.HasIndex(e => e.EmployeeCode, "IX_Employee_Code")  
                .IsUnique();  
  
            entity.Property(e => e.CompanyEmailAddress).HasMaxLength(100);  
  
            entity.Property(e => e.EmployeeCode)  
                .HasMaxLength(4)  
                .IsFixedLength(true);  
  
            entity.Property(e => e.First).HasMaxLength(50);  
  
            entity.Property(e => e.HireRehireDate).HasColumnType("date");  
  
            entity.Property(e => e.Initial).HasMaxLength(10);  
  
            entity.Property(e => e.Last).HasMaxLength(50);  
  
            entity.Property(e => e.PersonalEmailAddress).HasMaxLength(100);  
  
            entity.Property(e => e.Suffix).HasMaxLength(5);  
  
            entity.HasOne(d => d.Department)  
                .WithMany(p => p.Employees)  
                .HasForeignKey(d => d.DepartmentId)  
                .HasConstraintName("FK_Employee_Department");  
  
            entity.HasOne(d => d.EmployeeStatus)  
                .WithMany(p => p.Employees)  
                .HasForeignKey(d => d.EmployeeStatusId)  
                .HasConstraintName("FK_Employee_Status");  
  
            entity.HasOne(d => d.JobTitle)  
                .WithMany(p => p.Employees)  
                .HasForeignKey(d => d.JobTitleId)  
                .HasConstraintName("FK_Employee_JobTitle");  
        });  

dbo.EmployeeCertificationCompliance
CREATE FUNCTION [dbo].[EmployeeCertificationCompliance]
(
@CertificationRuleId INT = NULL,
@ComplianceStatus INT = NULL
)
RETURNS @returntable TABLE
(
EmployeeCertificationComplianceId INT IDENTITY PRIMARY KEY NOT NULL,
ComplianceStatusName NVARCHAR(50) NOT NULL,
EmployeeId INT NOT NULL,
EmployeeCode NVARCHAR(4) NULL,
[First] NVARCHAR(50) NULL,
[Last] NVARCHAR(50) NULL,
JobTitleName NVARCHAR(250) NULL,
DepartmentCode NVARCHAR(20) NULL,
DepartmentName NVARCHAR(250) NULL,
HireRehireDate DATE NULL,
CertificationName NVARCHAR(100) NOT NULL,
RuleName NVARCHAR(100) NOT NULL,
AppliesToAllEmployees BIT NOT NULL,
NumberOfYearsDue INT NULL,
CertificationDate DATETIME NULL
)
AS
BEGIN

    IF ISNULL(@ComplianceStatus,1) = 1  
    BEGIN  
        --Compliant  
        INSERT INTO @returntable  
        (  
            ComplianceStatusName,  
            EmployeeId,  
            EmployeeCode,  
            First,  
            Last,  
            JobTitleName,  
            DepartmentCode,  
            DepartmentName,  
            HireRehireDate,  
            CertificationName,  
            RuleName,  
            AppliesToAllEmployees,  
            NumberOfYearsDue,  
            CertificationDate  
        )  
        SELECT  
            'Compliant',  
            ec.EmployeeId,  
            e.EmployeeCode,  
            e.[First],  
            e.[Last],  
            jt.JobTitleName,  
            d.DepartmentCode,  
            d.DepartmentName,  
            e.HireRehireDate,  
            c.CertificationName,  
            CASE  
                WHEN r2.RuleName = 'Due Every X Years' THEN REPLACE(r2.RuleName,'X', cr.NumberOfYearsDue)  
                ELSE r2.RuleName  
            END AS [RuleName],  
            cr.AppliesToAllEmployees,  
            cr.NumberOfYearsDue,  
            ec.CertificationDate  
        FROM dbo.CompliantEmployeeCertification(@CertificationRuleId) r  
        JOIN dbo.CertificationRule cr ON cr.CertificationRuleId = r.CertificationRuleId  
        JOIN dbo.EmployeeCertification ec ON r.EmployeeCertificationId = ec.EmployeeCertificationId  
        JOIN dbo.Employee e ON e.EmployeeId = ec.EmployeeId  
        JOIN dbo.Certification c ON c.CertificationId = cr.CertificationId  
        JOIN dbo.[Rule] r2 ON r2.RuleId = cr.RuleId  
        JOIN dbo.JobTitle jt ON jt.JobTitleId = e.JobTitleId  
        JOIN dbo.Department d ON d.DepartmentId = e.DepartmentId  
    END  

    IF ISNULL(@ComplianceStatus,0) = 0  
    BEGIN  
        --Non Compliant  
        INSERT INTO @returntable  
        (  
            ComplianceStatusName,  
            EmployeeId,  
            EmployeeCode,  
            First,  
            Last,  
            JobTitleName,  
            DepartmentCode,  
            DepartmentName,  
            HireRehireDate,  
            CertificationName,  
            RuleName,  
            AppliesToAllEmployees,  
            NumberOfYearsDue  
        )  
        SELECT  
            'Noncompliant',  
            r.EmployeeId,  
            e.EmployeeCode,  
            e.[First],  
            e.[Last],  
            jt.JobTitleName,  
            d.DepartmentCode,  
            d.DepartmentName,  
            e.HireRehireDate,  
            c.CertificationName,  
            CASE  
                WHEN r2.RuleName = 'Due Every X Years' THEN REPLACE(r2.RuleName,'X', cr.NumberOfYearsDue)  
                ELSE r2.RuleName  
            END AS [RuleName],  
            cr.AppliesToAllEmployees,  
            cr.NumberOfYearsDue  
        FROM dbo.NonCompliantEmployeeCertification(@CertificationRuleId) r  
        JOIN dbo.CertificationRule cr ON cr.CertificationRuleId = r.CertificationRuleId  
        LEFT JOIN dbo.Employee e ON e.EmployeeId = r.EmployeeId  
        LEFT JOIN dbo.Certification c ON c.CertificationId = cr.CertificationId  
        LEFT JOIN dbo.[Rule] r2 ON r2.RuleId = cr.RuleId  
        LEFT JOIN dbo.JobTitle jt ON jt.JobTitleId = e.JobTitleId  
        LEFT JOIN dbo.Department d ON d.DepartmentId = e.DepartmentId  
END  

RETURN  

END

Answer 1

Hi,@MaxPowers1982 ,

Your Employee is null,so you cannot set Employee.EmployeeCertificationCompliance.Try to use the following code:

 public DetailsModel(EmployeeCertification.Models.Scaffold.EmployeeCertificationDBContext context)  
     {  
         _context = context;  
         Employee = new Employee { EmployeeCertificationCompliances = new List<EmployeeCertification.Models.EmployeeCertificationCompliance>()};  
     }  

----------

If the answer is helpful, please click "Accept Answer" and upvote it.
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,
YiyiYou

Answer 2

You need to learn about the Models used in MVC a razor page project is still using the MVC pipeline, and the project structure is the same.

https://deviq.com/terms/kinds-of-models

You need to understand the viewmodel. You shape the viewmodel that is sent into the view mapped from EF model, and you map the viewmodel back to the EF model for data persistence.

https://www.dotnettricks.com/learn/mvc/understanding-viewmodel-in-aspnet-mvc

The view.cshtml.cs should be void of any database code as talked about in the Understanding Models section in the link and kept thin.

https://learn.microsoft.com/en-us/aspnet/mvc/overview/older-versions-1/overview/understanding-models-views-and-controllers-cs

Here is a Razor page solution you can look at that implements the things talked about. DM = domain model and VM = view model. It has the name Blazor, but its a Razor project.

https://github.com/darnold924/PubCompanyCore3.x

HTH