Filtering with EF Core through multiple nested object relationships

Question

I have a new requirement to filter reports return from a query based on what amounts to a blacklist.

So originally I had this query which brings back all the pertinent information required and gets a list of reports associated to that particular user.

var reports = from r in Context.Reports
                          join ax in Context.AuthorizationXref on r.Id equals ax.ReportId
                          join g in Context.Groups on ax.GroupId equals g.Id
                          join ugx in Context.UsersGroupsXref on g.Id equals ugx.GroupId
                          where ugx.UserId == id
                          select r;

AuthorizationXref has ReportID and GroupId.

public partial class AuthorizationXref
    {
        public int Id { get; set; }
        public int ReportId { get; set; }
        public int GroupId { get; set; }

        public virtual Groups Group { get; set; }
        public virtual Reports Report { get; set; }
    }

public partial class Groups
    {
        public Groups()
        {
            AuthorizationXref = new HashSet<AuthorizationXref>();
            UsersGroupsXref = new HashSet<UsersGroupsXref>();
        }

        public int Id { get; set; }
        public string Name { get; set; }
        public string Description { get; set; }

        public virtual ICollection<AuthorizationXref> AuthorizationXref { get; set; }
        public virtual ICollection<UsersGroupsXref> UsersGroupsXref { get; set; }
    }

User many to many Groups many to many Reports (the many to many is done through the XREFs).

While in here I have benched it at 700ms which seems really slow and I realize I am doing at least 4 hits to the DB so I tried to do this which amounted to the same data more or less but ~7 times faster:

    var repo = context.Reports
        .Include(x => x.AuthorizationXref)
        .ThenInclude(x => x.Group)
        .ThenInclude(x => x.UsersGroupsXref)
        .ToList();

This benched at about 100ms but doesn't do any filtering on userId. These benchmarks are in Dev and will only worsen as we get into the higher environments where more and more reports are added to users. I know that using Selects is more efficient but I can't find examples of complex nested many-to-many selects on entities.

I can get about this far but don't know where to put the additional steps to drill deeper into the object.

var asdf = repo.Where(x=>x.AuthorizationXref.Any(y=>y.ReportId==x.Id));

End Goal is I need a list of Reports by userId removing those reports that appear by ID on another table. So there is a table called UserReportFilter and it has ReportId and UserId, any reports in that table should not appear in my end result.

As a side note if anyone can point me in the direction of a tutorial (preferably one that doesn't assume the reader knows absolutely everything) on how to use the Expressions I would appreciate it. I ran across this article and it would seem like a powerful thing to learn for things like this, however I will need a bit more meat in the explanation. I understand the concept and I have used basic Func returns for queries but nothing that extensive.

Answer 1

I assume you have relation between Reports and UserReportFilter tables since you have ReportId in UserReportFilter table. Below expression should work,

var reports = context.Reports
    .Include(x => x.AuthorizationXref)
    .ThenInclude(x => x.Group)
    .ThenInclude(x => x.UsersGroupsXref)
    .Include(x => x.UserReportFilters)
    .Where(x => x.AuthorizationXref.Any(y => y.Group.UsersGroupsXref.Any(z => z.UserId==id))
    .ToList();