Query Parent's record where condition on a Grandchild

Question

I want to select all the Site_Report entries where Asset_Calcert has at least one entry with condition Asset_Calcert.Cert_type == 3. The relationships are 1-0..* as shown below.

How do I go about it if want to put conditions on multiple entities, in my case I want to put condition on both Parent and Grand child. i.e Site_report.report_status == 2 and Asset_Calcert.Cert_type == 3

I've tried something like following, but not doing it right as it giving error with this approach.

IEnumerable<Site_Report> model;
using (var ctx = new ApplicationDbContext())
{
    model = ctx.Site_Report.Include(i => i.Published_By)
                            .Include(i => i.Handled_By)
                            .Include(i => i.Report_Assets.Select(c => c.Asset_Calcerts))
                            .Where(report => report.report_status == DBConstant.REPORT_STATUS_CONCLUDED)
    // Error ------>     // .Where(rp => rp.Report_Assets.Where(c => c.Asset_Calcerts.Any(d => d.cert_type == 3)))
                            .OrderByDescending(o => o.publish_date);
}

Error

Cannot implicitly convert type 'System.collection.Generic .IEnumerable' to 'bool'.
Error CS1662 Cannot convert lambda expression to intended delegate type because some of the return types in the block are not implicitly convertible to the delegate return type

Modal Only adding necessary fields for brevity

    public class Site_Report
    {
        public int site_report_id { get; set; }
        public int report_status { get; set; }

        // Navigation Properties
        public virtual ICollection<Report_Asset> Report_Assets { get; set; }

        public Site_Report()
        {
            this.Report_Assets = new HashSet<Report_Asset>();
        }
    }

    public class Report_Asset
    {
        public int report_asset_id { get; set; }

        // Navigation Properties
        public int site_report_id { get; set; }
        public virtual Site_Report Site_Report { get; set; 

        public Report_Asset()
        {
            this.Asset_Calcerts = new HashSet<Asset_Calcert>();
        }
    }
    public class Asset_Calcert
    {
        public int asset_calcert_id { get; set; }
        public int cert_type { get; set; }

        // Navigation Properties
        public int report_asset_id { get; set; }
        public virtual Report_Asset Report_Asset { get; set; }
    }

Answer 1

Queryable.Where uses a predicate parameter. The predicate is usually written in the format:

report => Some expression that takes report as input, and a Boolean as output

Your predicate is:

rp => rp.Report_Assets.Where(c => c.Asset_Calcerts.Any(d => d.cert_type == 3))

So your expression is:

rp.Report_Assets.Where(c => c.Asset_Calcerts.Any(d => d.cert_type == 3))

The value of this expression is not a Boolean, it is a IQueryable<ReportAssert>! Surely visual studio will have told you so when you hovered your mouse over it?

You wrote: I want to select all the Site_Report entries where Asset_Calcert has at least one entry with condition Asset_Calcert.Cert_type == 3

Or in a little re-wording:

Requirement: I want all (or some) properties of all SiteReports that have at least one ReportAsset that has at least one AssetCalCert where AssetCalCert.CertType equals 3

If you want to select properties of "a sequence within a sequence", you use SelectMany instead of Select:

var result = myDbContext.SiteReports

   .Where(siteReport => siteReport.ReportAssets.SelectMany(reportAsset => reportAsset.AssetCalCerts)

       // I only want this siteReport if at least one of the AssertCalCerts
       // has a certType value equal to 3
       .Any(assertCalCert => assertCalCert.CertType == 3))

   // from the resulting SiteReports selecte the properties that you plan to use:
   .Select(siteReport => new
   {
       Id = siteReport.Id,
       Name = siteReport.Name,
       ...

       ReportAsserts = siteReport.ReportAssets.Select(reportAsset => new
       {
           Id = reportAssert.Id,
           ...

           AssetCalCerts = reportAsset.AssetCalCerts.Select(assetCalCert => new
           {
             ...
           })
           .ToList(),
       })
       .ToList(),
  };

When querying data, always use Select and select only the properties that you plan to use. Only use Include if you plan to update the fetched item.

If SiteReport [10] has 1000 ReportAssets, then every ReportAsset will have a foreign key to ths SiteReport with a value 10. It would be a waste to transfer the same value 10 more than a 1000 times.

Anonymous types

Note that I used anonymous types:

siteReport => new
{
    ...
}

Of course I could have used new SiteReport() instead. But if I'd used that I would have transported several variables that I don't plan to use. Of course I could just skip filling the properties that I don't use, but if my readers get a SiteReport object, they may expect that all values are filled.

So while it is more efficient to use an anonymous type, the disadvantage is that you can't use the anonymous type as a return value. If you need to do that, the best way would be to separate the class that represents the database table from the class that represents fetched data: (adapter design pattern? or facade?)

class AdaptedSiteReport()
{
    ... only properties that queriers plan to use
}

And in the LINQ statement

siteReport => new AdaptedSiteReport() {...}

The advantage of separating the actual database from the fetched representation is that you can change the internal database representation without users having to notice it.