Calling Include in Entity Framework Core 2.0 makes the entity null if the property you are trying to include is null

entity-framework-core

Question

I am getting an EF Core Entity like this:

Audit audit = _auditRepo.Audits
            .Include(a => a.Status)
            .Include(a => a.AuditType)
            .Include(a => a.Office)
            .Include(a => a.LeadOffice)
            .Include(a => a.External)
            .Include(a => a.External).ThenInclude(e => e.AuditResult)
            .Include(a => a.External).ThenInclude(e => e.PreparerType)
            .Include(a => a.External).ThenInclude(e => e.Auditee)
            .Where(a => a.AuditID == id)
            .FirstOrDefault();

My audit comes back null because it has no office. If I have an office it the audit gets populated.

If I don't have an office but I comment out:

.Include(a => a.Office) 

the audit also comes back populated.

Here is my Audit entity :

[Table("audit")]
public class Audit
{
    private string _auditAcnCd;
    private string _title;
    private string _summary;

    [Key]
    [Column("audit_id")]
    public int AuditID { get; set; }

    [Required(ErrorMessage = "ACN Required")]
    [Display(Name="ACN:")]
    [Column("audit_acn_cd")]
    public string AuditAcnCd
    {
        get
        {
            return _auditAcnCd;
        }
        set
        {
            _auditAcnCd = value?.Trim();
        }
    }

    [Required(ErrorMessage = "Title Required")]
    [Display(Name = "Title:")]
    [Column("audit_report_title_tx")]
    public string Title
    {
        get
        {
            return _title;
        }
        set
        {
            _title = value?.Trim();
        }
    }

    [StringLength(maximumLength: 1000, ErrorMessage = "Max Length: 1000")]
    [Display(Name = "Summary:")]
    [Column("audit_summary_tx")]
    public string Summary
    {
        get
        {
            return _summary;
        }
        set
        {
            _summary = value?.Trim();
        }
    }

    [Required(ErrorMessage = "Issuer Required")]
    [Display(Name="Issuer:")]
    [Column("audit_issuer_tx")]
    public string Issuer { get; set; }

    [RegularExpression("([1-9][0-9]*)", ErrorMessage = "Priority must be a number.")]
    [Display(Name = "Priority:")]
    [Column("audit_priority_cd")]
    public short? Priority { get; set; }

    [Display(Name = "Lead Office:")]
    [Column("audit_lead_office_id")]
    public short? LeadOfficeID { get; set; }

    #region Navigation Properties
    [Required(ErrorMessage = "Audit Type Required.")]
    [Display(Name = "Audit Type:")]
    [Column("audit_audit_type_id")]
    public short AuditTypeID { get; set; }
    [Display(Name = "Audit Type:")]
    public AuditType AuditType { get; set; }

    [Column("audit_status_id")]
    public int StatusID { get; set; } 
    public Status Status { get; set; }

    [Required(ErrorMessage = "Office is Required.")]
    [Display(Name = "Offices:")]
    [Column("audit_office_id")]
    public short? OfficeID { get; set; }
    // [ForeignKey("OfficeID")]
    public Office Office { get; set; }

    [ForeignKey("AuditID")]
    public External External { get; set; }

    public IEnumerable<AuditLog> AuditLogs { get; set; }
    public IEnumerable<Finding> Findings { get; set; }
    public IEnumerable<Assignment> Assignments { get; set; }

    [Column("audit_update_staff_id")]
    public short UpdateStaffID { get; set; }

    [Column("audit_fsa_office_id")]
    [Display(Name = "FSA Audit Lead:")]
    public int? FsaLeadOfficeId { get; set; }
    [Display(Name = "FSA Audit Lead:")]
    [ForeignKey("FsaLeadOfficeId")]
    public FSAOffice FsaLeadOffice { get; set; }

    [ForeignKey("LeadOfficeID")]
    public Office LeadOffice { get; set; }
}

Here is my Office entity:

[Table("office")]
public class Office
{
    private string _OfficeCd;
    private string _OfficeNm;
    private string _OfficeOrganizationCd;

        [Key]
    [Column("office_id")]
    public short OfficeID { get; set; }

    [Required(ErrorMessage = "Numeric Code is required")]
    [StringLength(2, ErrorMessage = "Max Length is two")]
    [Display(Name = "Office Numeric Code:")]
    [Column("office_cd")]
    public string OfficeCd
    {
        get
        {
            return _OfficeCd;
        }
        set
        {
            _OfficeCd = value?.Trim();
        }
    }
    [Required(ErrorMessage = "Office Name is required")]
    [Display(Name = "Office Name:")]
    [Column("office_nm")]
    public string OfficeNm
    {
        get
        {
            return _OfficeNm;
        }
        set
        {
            _OfficeNm = value?.Trim();
        }
    }


    [Required(ErrorMessage = "Office Abbreviation is required")]
    [Display(Name = "Office Abbreviation:")]
    [Column("office_organization_cd")]
    public string OfficeOrganizationCd
    {
        get
        {
            return _OfficeOrganizationCd;
        }
        set
        {
            _OfficeOrganizationCd = value?.Trim();
        }
    }


    [Display(Name = "Status:")]
    [Column("office_active_cd")]
    public string OfficeActiveCd { get; set; }

    [Display(Name = "Parent Office:")]
    [Column("office_parent_id")]
    public short? OfficeParentId { get; set; }
    [Display(Name = "Parent Office:")] 
    [ForeignKey("OfficeParentId")]
    public Office ParentOffice { get; set; }

    public List<StaffOffice> StaffOffices { get; set; }
}

All my other properties work ok. Do I need a configure this in onModelCreating?

1
1
9/14/2018 4:51:52 AM

Popular Answer

The explanation is simple - there is discrepancy between your entity model and the database.

When the FK is nullable like your OfficeId, the relationship is optional, EF Core expects that the column can be null and uses left outer join when retrieving the related data (requested via Include).

But at some point you seem to added [Required] attribute to the FK property:

[Required(ErrorMessage = "Office is Required.")] // <-- the problem
[Display(Name = "Offices:")]
[Column("audit_office_id")]
public short? OfficeID { get; set; }
// [ForeignKey("OfficeID")]
public Office Office { get; set; }

Note that the [Required] attribute and IsRequired() fluent API take precedence over the data type (of course you cannot make non nullable type nullable, but the other way around is possible). The effect is that now EF considers the OfficeID FK to be required (i.e. non nullable column in the database) and performs inner join, which of course filters the result in case the record contains null FK value.

The solution is to remove that attribute. And in general, always keep the model and the database in sync. Anytime you change something in the model, add new migration. In case it's empty, remove it, otherwise see what database changes EF assumes based on your model metadata/configuration.

2
9/14/2018 7:01:24 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow