Including Child from Table Valued Function In Parent Model

MaxPowers1982 81 Reputation points
2021-06-29T13:50:50.353+00:00

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
110323-image.png

v2 Error Message
110289-image.png

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

Entity Framework Core
Entity Framework Core
A lightweight, extensible, open-source, and cross-platform version of the Entity Framework data access technology.
701 questions
ASP.NET Core
ASP.NET Core
A set of technologies in the .NET Framework for building web applications and XML web services.
4,234 questions
0 comments No comments
{count} votes

Accepted answer
  1. Yiyi You - MSFT 521 Reputation points
    2021-06-30T02:17:15.167+00:00

    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


1 additional answer

Sort by: Most helpful
  1. Duane Arnold 3,216 Reputation points
    2021-06-30T00:09:37.843+00:00

    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

    0 comments No comments