question

MaxPowers1982-8385 avatar image
0 Votes"
MaxPowers1982-8385 asked MaxPowers1982-8385 edited

Including Child from Table Valued Function In Parent Model

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
@page
@model 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





dotnet-entity-framework-coredotnet-aspnet-core-razor
image.png (38.4 KiB)
image.png (86.9 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

YiyiYou-MSFT avatar image
0 Votes"
YiyiYou-MSFT answered MaxPowers1982-8385 edited

Hi,@MaxPowers1982-8385,

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



· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Perfect, thank you so much! One more thing before I accept your answer - now that results are displaying I notice that it looks like there is a cross apply happening. Did I miss a step to join on EmployeeId?

0 Votes 0 ·

@YiyiYou-MSFT just tagging you to make sure you saw my comment. :)

0 Votes 0 ·

Hi,@MaxPowers1982-8385,

You mean the your step in OnGet(int Id)?If so,constructor will before OnGet.

0 Votes 0 ·
Show more comments
DuaneArnold-0443 avatar image
0 Votes"
DuaneArnold-0443 answered

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://docs.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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.