074 Modeling Composite Lookup Resource Entities for PhbkPhone - chempkovsky/CS82ANGULAR GitHub Wiki

Notes

Lookup dictionaries

refs tables

modification of PhbkPhone table

modification of PhbkEmployee table

modification of PhbkPhoneType table

Notes

  • We are going to create Lookup Resource for Phone-scalar property of PhbkPhoneView.
  • According to the requirements 033 we need to create three lookup dictionary-table and THREE lookup refs-table.
    • The number of the lookup refs-tables depends on the number of the foreign keys.
      • For all entities before current we created two lookup refs-tables, since each of those entities had only one foreign key.
      • Current PhbkPhone-entity has two foreign keys. Thus, we need one lookup refs-table for the first foreign key, one lookup refs-table for the second foreign key, and one lookup refs-table for the pair of the foreign keys (first + second)
  • Reminder: each dictionary table
    • should have two columns:
      • row id column with a primary key on it
      • data column with a unique key on it
      • the name of the data column must be the same as column name of the PhbkPhone-table
  • Naming prefix for the lookup dictionary-tables will be Lpd (which means lookup dictionary)
  • Naming prefix for the lookup refs-tables will be Lpr (which means lookup refs)

Lookup dictionaries

Lookup dictionary for Phone

  • LpdPhone will be the name of the dictionary table
  • According to the requirements 033 the class declaration must be inside of #if (!NOTMODELING) ... #endif-operator.
Click to show the code 
#if (!NOTMODELING)
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace PhBkEntity.PhBk
{
    public class LpdPhone
    {
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        [Display(Description = "Row id", Name = "Id of the Row", Prompt = "Enter Row Id", ShortName = "Row Id")]
        [Required]
        public int LpdPhoneId { get; set; }

        [StringLength(20, MinimumLength = 3, ErrorMessage = "Invalid")]
        [Display(Description = "Name of the Phone Type", Name = "Phone", Prompt = "Enter Phone", ShortName = "Phone")]
        [Required]
        public string Phone { get; set; } = null!;

        public List<LprPhone01> PhoneRef01 { get; set; } = null!;
        public List<LprPhone02> PhoneRef02 { get; set; } = null!;
        public List<LprPhone03> PhoneRef03 { get; set; } = null!;
        public List<LprPhone04> PhoneRef04 { get; set; } = null!;

    }
}
#endif

refs tables

lookup refs for along mode

  • along-mode is a mode in which the component is not used as detail node in master-detail navigation chain and in which the component is not used as detail panel on the one-to-many-page.

  • According to the requirements 033

    • lookup refs-table must have the foreign key which references lookup dictionaries
    • lookup refs-table must have the foreign key which references PhbkPhone. It is a table for which we are going to create lookup resource

  • LprPhone01 will be the name for the table

  • According to the requirements 033 the class declaration must be inside of #if (!NOTMODELING) ... #endif-operator.

Click to show the code 
#if (!NOTMODELING)
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace PhBkEntity.PhBk
{
    public class LprPhone01
    {
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        [Display(Description = "Row id", Name = "Phone Id", Prompt = "Enter Phone Id", ShortName = "Phone Id")]
        [Required]
        public int PhoneId { get; set; }
        public PhbkPhone Phone { get; set; } = null!;

        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        [Display(Description = "Row id", Name = "Id of the Row", Prompt = "Enter Row Id", ShortName = "Row Id")]
        [Required]
        public int LpdPhoneIdRef { get; set; }

        public LpdPhone PhoneDict { get; set; } = null!;
    }
}
#endif

lookup refs for one to many mode for PhbkEmployeeView

  • one-to-many-mode is a mode in which the component is used as detail node in master-detail navigation chain or in which the component is used as detail panel on the one-to-many-page.
  • According to the requirements 033
    • lookup refs-table must have the foreign key which references lookup dictionaries
    • lookup refs-table must have the foreign key which references PhbkPhone. It is a table for which we are going to create lookup resource
    • lookup refs-table must have the foreign key which references PhbkEmployee-table. This is because PhbkPhone references PhbkEmployee-table as well.
  • According to the requirements 033 the class declaration must be inside of #if (!NOTMODELING) ... #endif-operator.
Click to show the code 
#if (!NOTMODELING)
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace PhBkEntity.PhBk
{
    public class LprPhone02
    {
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        [Display(Description = "Row id", Name = "Phone Id", Prompt = "Enter Phone Id", ShortName = "Phone Id")]
        [Required]
        public int PhoneId { get; set; }
        public PhbkPhone Phone { get; set; } = null!;

        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        [Display(Description = "Row id", Name = "Id of the Row", Prompt = "Enter Row Id", ShortName = "Row Id")]
        [Required]
        public int LpdPhoneIdRef { get; set; }

        public LpdPhone PhoneDict { get; set; } = null!;

        [Display(Description = "Row id", Name = "Id of the Employee", Prompt = "Enter Employee  Id", ShortName = "Employee Id")]
        [Required]
        public int EmployeeIdRef { get; set; }

        public PhbkEmployee Employee { get; set; } = null!;

    }
}
#endif

lookup refs for one to many mode for PhoneTypeView

  • one-to-many-mode is a mode in which the component is used as detail node in master-detail navigation chain or in which the component is used as detail panel on the one-to-many-page.
  • According to the requirements 033
    • lookup refs-table must have the foreign key which references lookup dictionaries
    • lookup refs-table must have the foreign key which references PhbkPhone. It is a table for which we are going to create lookup resource
    • lookup refs-table must have the foreign key which references PhoneType-table. This is because PhbkPhone references PhoneType-table as well.
  • According to the requirements 033 the class declaration must be inside of #if (!NOTMODELING) ... #endif-operator.
Click to show the code 
#if (!NOTMODELING)
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace PhBkEntity.PhBk
{
    public class LprPhone03
    {
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        [Display(Description = "Row id", Name = "Phone Id", Prompt = "Enter Phone Id", ShortName = "Phone Id")]
        [Required]
        public int PhoneId { get; set; }
        public PhbkPhone Phone { get; set; } = null!;

        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        [Display(Description = "Row id", Name = "Id of the Row", Prompt = "Enter Row Id", ShortName = "Row Id")]
        [Required]
        public int LpdPhoneIdRef { get; set; }

        public LpdPhone PhoneDict { get; set; } = null!;

        [Display(Description = "Row id", Name = "Phone Type Id", Prompt = "Enter Phone Type Id", ShortName = "Phone Type Id")]
        [Required]
        public int PhoneTypeIdRef { get; set; }

        public PhbkPhoneType PhoneType { get; set; } = null!;
    }
}
#endif

Wrong solution for PhoneTypeView

  • Lookup resource allows you to filter the PhbkPhone-table only by primary keys. And that's our goal.
  • But what about a request to a Lookup resource?. When we navigate from the selected row in the PhoneType-table to the detailed rows in the Phone- table.
  • Let's clarify:
    • There are only four rows in the PhoneType-table
    • The following primary key will be generated for LprPhone03-table
modelBuilder.Entity<LprPhone03>().HasKey(p => new { p.PhoneTypeIdRef, p.LpdPhoneIdRef, p.PhoneId });
  • The following query will be executed during the transition from the selected row of the PhoneType-table to the detailed rows of the Phone-table.
select * from LprPhone03 where PhoneTypeIdRef = ?

  • The Sql-optimizer will ignore primary key of the LprPhone03-table. A full table scan will be applied and we'll get into a performance problem.

  • The simplest solution is to deny navigation form PhoneType-table to Phone-table. If the business requirements for the application are still met even after the abandonment of such navigation, then that's it. We should remove LprPhone03-table from the project and remove any navigations from the PhoneType-table to the Phone-table in the app.

  • Suppose, for business reasons, such navigation is mandatory for the application.

  • Any way, current article is devoted the case when there is more than one foreign key, thus the structure of the table is as shown above.

lookup refs for one to many mode for PhbkEmployeeView and PhoneTypeView

  • last case when two foreign keys are included. LprPhone04 is a name of the table:
Click to show the code 
#if (!NOTMODELING)
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace PhBkEntity.PhBk
{
    public class LprPhone04
    {
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        [Display(Description = "Row id", Name = "Phone Id", Prompt = "Enter Phone Id", ShortName = "Phone Id")]
        [Required]
        public int PhoneId { get; set; }
        public PhbkPhone Phone { get; set; } = null!;

        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        [Display(Description = "Row id", Name = "Id of the Row", Prompt = "Enter Row Id", ShortName = "Row Id")]
        [Required]
        public int LpdPhoneIdRef { get; set; }

        public LpdPhone PhoneDict { get; set; } = null!;


        [Display(Description = "Row id", Name = "Id of the Employee", Prompt = "Enter Employee  Id", ShortName = "Employee Id")]
        [Required]
        public int EmployeeIdRef { get; set; }

        public PhbkEmployee Employee { get; set; } = null!;

        [Display(Description = "Row id", Name = "Phone Type Id", Prompt = "Enter Phone Type Id", ShortName = "Phone Type Id")]
        [Required]
        public int PhoneTypeIdRef { get; set; }

        public PhbkPhoneType PhoneType { get; set; } = null!;
    }
}
#endif

modification of PhbkPhone table

  • LprEmployee01 and LprEmployee02 and LprEmployee03 and LprEmployee04 reference PhbkPhone-table. To accomplish the foreign key declaration we need to add inverse reference in the PhbkPhone-table.
  • According to the requirements 033 the inverse reference code must be inside of #if (!NOTMODELING) ... #endif-operator.
  • Here is a new version PhbkPhone-table.
Click to show the code 
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace PhBkEntity.PhBk
{
    public class PhbkPhone
    {
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        [Display(Description = "Row id", Name = "Phone Id", Prompt = "Enter Phone Id", ShortName = "Phone Id")]
        [Required]
        public int PhoneId { get; set; }

        [StringLength(20, MinimumLength = 3, ErrorMessage = "Invalid")]
        [Display(Description = "Name of the Phone Type", Name = "Phone", Prompt = "Enter Phone", ShortName = "Phone")]
        [Required]
        public string Phone { get; set; } = null!;

        [Display(Description = "Row id", Name = "Phone Type Id", Prompt = "Enter Phone Type Id", ShortName = "Phone Type Id")]
        [Required]
        public int PhoneTypeIdRef { get; set; }

        public PhbkPhoneType PhoneType { get; set; } = null!;


        [Display(Description = "Row id", Name = "Id of the Employee", Prompt = "Enter Employee  Id", ShortName = "Employee Id")]
        [Required]
        public int EmployeeIdRef { get; set; }

        public PhbkEmployee Employee { get; set; } = null!;

#if (!NOTMODELING)
        public List<LprPhone01> PhoneRefs01 { get; set; } = null!;
        public List<LprPhone02> PhoneRefs02 { get; set; } = null!;
        public List<LprPhone03> PhoneRefs03 { get; set; } = null!;
        public List<LprPhone04> PhoneRefs04 { get; set; } = null!;
#endif

    }
}

modification of PhbkEmployee table

  • LprEmployee02 and LprEmployee04 reference PhbkEmployee-table. To accomplish the foreign key declaration we need to add inverse reference in the PhbkEmployee-table.
  • According to the requirements 033 the inverse reference code must be inside of #if (!NOTMODELING) ... #endif-operator.
  • Here is a new version PhbkEmployee-table.
Click to show the code 
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace PhBkEntity.PhBk
{
    public class PhbkEmployee
    {
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        [Display(Description = "Row id", Name = "Id of the Employee", Prompt = "Enter Employee  Id", ShortName = "Employee Id")]
        [Required]
        public int EmployeeId { get; set; }

        [Display(Description = "First Name of the Employee", Name = "Employee First Name", Prompt = "Enter Employee First Name", ShortName = "First Name")]
        [StringLength(25, MinimumLength = 3, ErrorMessage = "Invalid")]
        [Required]
        public string EmpFirstName { get; set; } = null!;

        [Display(Description = "Last Name of the Employee", Name = "Employee Last Name", Prompt = "Enter Employee Last Name", ShortName = "Last Name")]
        [StringLength(40, MinimumLength = 3, ErrorMessage = "Invalid")]
        [Required]
        public string EmpLastName { get; set; } = null!;

        [Display(Description = "Row id", Name = "Employee Second Name", Prompt = "Enter Employee Second Name", ShortName = "Second Name")]
        [StringLength(25, ErrorMessage = "Invalid")]
        public string? EmpSecondName { get; set; }

        [Display(Description = "Row id", Name = "Id of the Division", Prompt = "Enter Division Id", ShortName = "Division Id")]
        [Required]
        public int DivisionIdRef { get; set; }

        public PhbkDivision Division { get; set; } = null!;

        public List<PhbkPhone> Phones { get; set; } = null!;


#if (!NOTMODELING)
        public List<LprEmployee01> EmployeeRefs01 { get; set; } = null!;
        public List<LprEmployee02> EmployeeRefs02 { get; set; } = null!;
        public List<LprPhone02> PhoneRefs02 { get; set; } = null!;
        public List<LprPhone04> PhoneRefs04 { get; set; } = null!;
#endif

    }
}

modification of PhbkPhoneType table

  • LprEmployee03 and LprEmployee04 reference PhbkPhoneType-table. To accomplish the foreign key declaration we need to add inverse reference in the PhbkPhoneType-table.
  • According to the requirements 033 the inverse reference code must be inside of #if (!NOTMODELING) ... #endif-operator.
  • Here is a new version PhbkPhoneType-table.
Click to show the code 
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace PhBkEntity.PhBk
{
    public class PhbkPhoneType
    {
        [DatabaseGenerated(DatabaseGeneratedOption.None)]
        [Display(Description = "Row id", Name = "Phone Type Id", Prompt = "Enter Phone Type Id", ShortName = "Phone Type Id")]
        [Required]
        public int PhoneTypeId { get; set; }

        [StringLength(20, MinimumLength = 3, ErrorMessage = "Invalid")]
        [Display(Description = "Name of the Phone Type", Name = "Phone Type Name", Prompt = "Enter Phone Type Name", ShortName = "Phone Type Name")]
        [Required]
        public string PhoneTypeName { get; set; } = null!;

        [Display(Description = "Description of the Phone Type", Name = "Phone Type Description", Prompt = "Enter Phone Type Description", ShortName = "Phone Type Description")]
        [StringLength(250, ErrorMessage = "Invalid")]
        public string? PhoneTypeDesc { get; set; }

        public List<PhbkPhone> Phones { get; set; } = null!;

#if (!NOTMODELING)
        public List<LprPhone03> PhoneRefs03 { get; set; } = null!;
        public List<LprPhone04> PhoneRefs04 { get; set; } = null!;
#endif


    }
}
⚠️ **GitHub.com Fallback** ⚠️