How do I apply a Where clause to an entity that's a couple layers deep in a LINQ expression?

Question

I'm working on a LINQ expression that will pull in related tables to a Person table we've got. The query I've written does work, but it takes a long time to run and brings back more data than I need. Here's the LINQ expression I have now:

using (var ctx = new AppEntities())
{
    People = ctx.People.Where(p => p.Inactive == false)
        .Include(p => p.Agency)
        .Include(p => p.PersonnelCertifications.Select(pc => pc.CertificationType))
        .OrderBy(p => p.LastName)
        .ThenBy(p => p.FirstName)
        .ToList();
}

We're working with .NET 4.5.2 and EF 6.4. The Person table has a relatonship with the PersonnelCertification table. And that has a relationship with the CertificationType table. Ideally, what I need to add is a filter so that only CertificationType.CertType == "Operator". I tried adding a Where clause after the Include of PersonnelCertifications, but that didn't work. The second Where clause was still only working with the Person table. Is there a way of doing what I want? If so, how is this done?

Here's the table definitions with extraneous fields removed for brevity:

public partial class Person
{
    public Person()
    {
        PersonnelCertifications = new HashSet<PersonnelCertification>();
    }

    public long ID { get; set; }

    public virtual ICollection<PersonnelCertification> PersonnelCertifications { get; set; }
}

public partial class PersonnelCertification
{
    public long ID { get; set; }

    public long CertificationTypeID { get; set; }

    public long PersonID { get; set; }

    public virtual CertificationType CertificationType { get; set; }

    public virtual Person Person { get; set; }
}

public partial class CertificationType
{
    public CertificationType()
    {
        PersonnelCertifications = new HashSet<PersonnelCertification>();
    }

    public long ID { get; set; }

    [Required]
    [StringLength(30)]
    public string CertType { get; set; }

    public virtual ICollection<PersonnelCertification> PersonnelCertifications { get; set; }
}

I am not sure whether the provider understands `.Where(p => p.PersonnelCertifications.Any(pc => pc.CertificationType == "Operator"))`. — mm8, Apr 07 '21 at 15:13
Good question, Caius. I tried adding this Where clause immediately after the second Include clause: `.Where(p => p.PersonnelCertifications.Where(pc => pc.CertificationType.CertType == "Operator"))`. However, I get error "Cannot convert lambda expression to indended delegate type because some of the return types in the block are not implicitily convertible to the delegate return type" — Rod, Apr 07 '21 at 15:40
@mm8 your suggestion seems like it works. The provider (SQL Server) doesn't complain and at least the records I checked only included a `CertificationType.CertType == "Operator"`. Thank you! — Rod, Apr 07 '21 at 15:52
Joining the lists may improve performance https://stackoverflow.com/a/2767742/5107490 — ShayD, Apr 07 '21 at 15:53

score 0 · Answer 1 · answered Apr 07 '21 at 17:49

0

.Where(p => p.PersonnelCertifications.Any(pc => pc.CertificationType == "Operator")) should give you the people you are looking for.

answered Apr 07 '21 at 17:49

mm8

163,881
10
57
88

How do I apply a Where clause to an entity that's a couple layers deep in a LINQ expression?

1 Answers1