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
?
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.