LINQ Any() method returning

Question

I have a typical Drill Down Application.

Here is the hierarchy of my application.

Audit
has many
Findings

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();
        }
    }

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

    [Display(Name = "Sensitive Designation")]
    [Column("audit_sensitive_cd")]
    public string AuditSensitiveCode { get; set; }

    [Display(Name = "Alternative Product")]
    [Column("audit_alternate_product_cd")]
    public string AuditAlternateProductCode { 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; }

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

    [Column("audit_gao_contact_tx")]
    [Display(Name = "GAO Contact:")]
    [StringLength(maximumLength: 200, ErrorMessage = "Max Length: 200")]
    public string AuditGaoContactText { get; set; }

    [Column("audit_gao_job_cd")]
    [Display(Name = "GAO Job Code:")]
    [StringLength(maximumLength: 200, ErrorMessage = "Max Length: 30")]
    public string AuditGaoJobCode { 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; }
    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_oig_manager_id")]
    [Display(Name = "OIG Audit Manager:")]
    public short? OigAuditManagerId { get; set; }
    [Display(Name = "OIG Audit Manager:")]
    [ForeignKey("OigAuditManagerId")]
    public Staff OigAuditManager { 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; }
    #endregion
}

[Table("finding")]
public class Finding
{
    private string _findingText;

    [Key]
    [Column("finding_id")]
    public int FindingId { get; set; }

    [Column("finding_audit_id")]
    public int FindingAuditId { get; set; }

    [Column("finding_cd")]
    [Display(Name = "Finding #")]
    [StringLength(15)]
    public string FindingCd { get; set; }

    [Column("finding_tx")]
    [Required(ErrorMessage = "Description Required")]
    [StringLength(7000)]
    public string FindingText
    {
        get
        {
            return _findingText;
        }
        set
        {
            _findingText = value?.Trim();
        }
    }

    [Column("finding_page_cd")]
    [StringLength(100)]
    public string FindingPageCd { get; set; }

    [Column("finding_joint_cd")]
    public string FindingJointCd { get; set; }

    [Column("finding_compliance_tx")]
    [StringLength(20)]
    public string FindingComplianceText { get; set; }

    [Column("finding_prior_year_cd")]
    [Display(Name = "Repeat Finding")]
    public string FindingPriorYearCd { get; set; }

    [Column("finding_decision_cd")]
    public string FindingDecisionCd { get; set; }

    [Column("finding_request_decision_cd")]
    public string FindingRequestDecisionCd { get; set; }

    [Column("finding_decision_ogc_concur_cd")]
    public string FindingDecisionOgcConcurCd { get; set; }

    [Column("finding_pdl_id")]
    public int? FindingPdlId { get; set; }

    [Display(Name = "Significant")]
    [Column("finding_significant_cd")]
    public string FindingSignificantCd { get; set; }

    [Column("finding_on_stay_cd")]
    public string FindingOnStayCd { get; set; }

    [Column("finding_stay_request_cd")]
    public string FindingStayRequestCd { get; set; }

    [Column("finding_last_update_dt")]
    public DateTime FindingLastUpdateDate { get; set; }

    [Column("finding_update_staff_id")]
    public short? FindingUpdateStaffId { get; set; }

    [Column("finding_cd_org")]
    public string FindingCdOrg { get; set; }

    [NotMapped]
    public string RepeatingYearsDisplayList
    {
        get
        {
            if (RepeatingYears?.Count > 0)
            {
                string repeatingYears = string.Empty;
                RepeatingYears.ForEach(ry =>
                    repeatingYears += $"{ry.FindingFyCd}, ");
                return repeatingYears.Remove(repeatingYears.Length - 2);
            }
            return string.Empty;
        }
    }

    #region Navigation Properties
    [Column("finding_finding_type_id")]
    public short? FindingTypeId { get; set; }
    [ForeignKey("FindingTypeId")]
    public FindingType FindingType { get; set; }

    [Column("finding_status_id")]
    public int? FindingStatusId { get; set; }
    [ForeignKey("FindingStatusId")]
    public Status FindingStatus { get; set; }

    public List<FindingFiscalYear> RepeatingYears { get; set; }
    public List<Recommendation> Recommendations { get; set; }

    [ForeignKey("FindingAuditId")]
    public Audit Audit { get; set; }
    #endregion
}

A
Finding
has many Recommendations.

[Table("recommend")]
public class Recommendation
{
    private string _recText;

    [Key]
    [Column("recommend_id")]
    public int RecommendationId { get; set; }

    [Column("recommend_finding_id")]
    public int RecFindingId { get; set; }

    [Column("recommend_cd")]
    public short? RecCd { get; set; }

    [StringLength(7000)]
    [Column("recommend_tx")]
    public string RecText
    {
        get
        {
            return _recText;
        }
        set
        {
            _recText = value?.Trim();
        }
    }

    [Column("recommend_contact_nm")]
    public string RecContactName { get; set; }

    [Column("recommend_status_id")]
    public int RecStatusId { get; set; }

    [Column("recommend_recommend_type_cd")]
    public short? RecTypeCd { get; set; }

    [Column("recommend_staff_id")]
    public short RecStaffId { get; set; }

    [Column("recommend_role_id")]
    public short? RecRoleId { get; set; }

    [Column("recommend_oig_decision_cd")]
    public string RecOigDecisionCd { get; set; }

    [Column("recommend_last_update_dt")]
    public DateTime RecLastUpdateDt { get; set; }

    [Column("recommend_action_determination_cd")]
    public string RecActionDeterminationCd { get; set; }

    #region Navigation Properties
    [ForeignKey("RecFindingId")]
    public Finding Finding { get; set; }

    [ForeignKey("RecTypeCd")]
    public RecommendationType RecommendationType { get; set; }

    [ForeignKey("RecStatusId")]
    public Status RecommendationStatus { get; set; }

    [ForeignKey("RecStaffId")]
    public Staff Staff { get; set; }

    public List<Assignment> Assignments { get; set; }
    #endregion

    #region NOT Mapped
    [NotMapped]
    public string RecDisplayShortDesc => $"{RecCd} {RecText}";
    #endregion
}

A Recommendation can have many Assignments:

[Table("assignment")]
public class Assignment
{
    [Key]
    [Column("assignment_id")]
    public int AssignmentId { get; set; }

    [Column("assignment_role_id")]
    public short AssignmentRoleId { get; set; }

    [Column("assignment_type_cd")]
    public string AssignmentTypeCd { get; set; }

    [Column("assignment_dt")]
    public DateTime AssignmentDate { get; set; }

    [Column("assignment_by_staff_id")]
    public short AssignmentByStaffId { get; set; }

    [Column("assignment_recommend_id")]
    public int? AssignmentRecommendId { get; set; }

    [Column("assignment_office_id")]
    public short? AssignmentOfficeId { get; set; } 

    #region Navigation Properties
    [Column("assignment_staff_id")]
    public short AssignmentStaffId { get; set; }
    [ForeignKey("AssignmentStaffId")]
    public Staff AssignmentStaff { get; set; }

    [Column("assignment_audit_id")]
    public int? AssignmentAuditId { get; set; }
    [ForeignKey("AssignmentAuditId")]
    public Audit Audit { get; set; }

    [ForeignKey("AssignmentOfficeId")]
    public Office AssignmentOffice { get; set; }

    [ForeignKey("AssignmentRecommendId")]
    public Recommendation Recommendation { get; set; }
    #endregion
}

I have a View Component with a LINQ Query using the Any() method all the way down from a top level Audits collection to get me all Audits with any Findings where any one of the Findings that has Recommendations where any of those recommendations are missing an assignment with a role id of 15 or 26. If either one or both of those assignment records are missing from the rec, then the rec's, finding's, audit should be added to the audit collection being returned in the model.

We thought it seemed to be working. But then we noticed when there is only one finding on the audit, it is not added to the collection. Only when the Audit has many findings and one of the findings has a rec with a missing assignment is the audit incorrectly not added.

This foreach statement seems to work for me:

        // Where audit has a recommendation without an assigned PO Authorizer
        //    OR without an assigned Responsible Manager (Rec Level).
        List<Audit> auditsToAssign = new List<Audit>();
        foreach (Audit audit in audits)
        {
            foreach (Finding finding in audit.Findings)
            {
                foreach (Recommendation rec in finding.Recommendations)
                {
                    if (!rec.Assignments.Any(asgn => asgn.AssignmentRoleId == 15)
                            || !rec.Assignments.Any(asgn => asgn.AssignmentRoleId == 26)
                        )
                    {
                        if (!auditsToAssign.Contains(rec.Finding.Audit))
                        {
                            auditsToAssign.Add(rec.Finding.Audit);
                        }
                    }
                }
            }
        }

But originally I was trying to write a LINQ query. This has been through many iterations but this is the current form:

audits = audits.Where(a =>
            a.Findings.Count() > 0 &&
            a.Findings.Any(f =>
                f.Recommendations.Count() > 0 &&
                (
                !f.Recommendations.Any(r =>
                    r.Assignments.Any(asgn => asgn.AssignmentRoleId == 15)) ||
                !f.Recommendations.Any(r =>
                    r.Assignments.Any(asgn => asgn.AssignmentRoleId == 26))
                )
            )
        );

Answer 1

I think you can go at this the reverse direction easier (assuming you have a reference to a full assignments Table):

var auditsToAssign = assignments.Where(asgn => asgn.AssignmentRoleId != 15 && asgn.AssignmentRoleId != 26)
                        .Select(asgn => asgn.Recommendation.Finding.Audit)
                        .Distinct();

Answer 2

I'm not 100% sure that I've followed your description, but try this:

        audits = audits.Where(a => 
                     a.Findings.Any(f => 
                         f.Recommendations.Any(r => 
                             !r.Assignments.Any(asgn => asgn.AssignmentRoleId == 15 || asgn.AssignmentRoleId == 26))));

Also, the Linq will be faster than your foreach because the Any() method will stop enumerating as soon as it finds a match, whereas your foreach's will continue to enumerate the entire collection even if they find a match at the very start. It's also easier to read IMHO (although it takes a while to get your Linq-eye in when you start working with it).