CODEWITHSUNDEEP
c#linqentity-framework-coreasqueryable
haydnD
haydnD

Reputation: 2293

EF Core how to add a filter to .ThenIncludes

I'm using EF Core 5.0.4

I have these three entities that have relations.

public class Parent
{
    [Key]
    public int ParentId { get; set; }
    [Key]
    public short ChildId { get; set; }
    public string ParentProp1 { get; set; }
    public string ParentProp2 { get; set; }
    public DateTime ProcessedDate { get; set; }
    public DateTime Anniversary { get; set; }

    public Child Child { get; set; }
}

public class Child
{
    public Child()
    {
        Animals = new HashSet<Animals>();
    }

    [Key]
    public short ChildId { get; set; }
    public string ChildName { get; set; }

    public virtual ICollection<Animals> Animals { get; set; }
    //UPDATED TO INCLUDE PARENT
    public Parent Parent {get;set;}
}

public class Animals
{
    [Key]
    public short AnimalId { get; set; }
    [ForeignKey("ChildId")]
    public short ChildId { get; set; }

    public DateTime StartDate { get; set; }
    public DateTime EndDate { get; set; }
    public DateTime MAnniversary { get; set; }
    public DateTime PAnniversary { get; set; }

    public virtual Child Child { get; set; }
}

In my repository I'm trying to return a List of Parent. How do I add the filter ..see commented code lines... on the IQueryable()?

public List<Parent> Get(Request request)
{
    var data = _context.Parents
        .Include(r => r.Child)
            .ThenInclude(a => a.Animals)
        .AsQueryable().AsNoTracking();

    data = data.Where(x => x.ProcessedDate == request.ProcessedDate);

    // Here is the filter I'm trying to add but can't because data is an IQueryable() :
    // Animals needs to be filtered based off a query like:
    // data.Child.Animals = data.Child.Animals.Where( d => d.StartDate <= data.ProcessedDate && (
    //                        d.EndDate == null || data.ProcessDate <= d.EndDate
    //                       )
    //                       && d.ChildId == data.ChildId && data.Anniversary >= d.MAnniversary
    //                       ).ToList();

    return data;
}

When returning just Parent I'm able to add the filter query like below without any issues because 'data' is not an IQueryable() :

public Parent Get(int id)
{
    var data = _context.Parents
        .Include(r => r.Child)
            .ThenInclude(a => a.Animals)
        .FirstOrDefault(x => x.ParentId == id);

    data = data.Where(x => x.ProcessedDate == request.ProcessedDate);

    data.Child.Animals = data.Child.Animals.Where(d => d.StartDate <= data.ProcessedDate && (
                            d.EndDate == null || data.ProcessDate <= d.EndDate
                           )
                           && d.ChildId == data.ChildId && data.Anniversary >= d.MAnniversary
                            ).ToList();
    return data;
}

I've tried adding a filter on the .ThenInlude() like below but it doesn't work because I can't access the properties needed.

var data = _context.Parents
            .Include(r => r.Child)
                .ThenInclude(a => a.Animals.Where(x => x.StartDate <= "this doesn't work because can't access Parent or Child properties")
            .AsQueryable().AsNoTracking();

UPDATE

I added navigation Parent navigation property to Child and changed the query to be based on the inner relations. It looks something like this:

var data = _context.Animals
        .Include(r => r.Child)
            .ThenInclude(a => a.Parent)
        .Where(r => r.StartDate <= r.Child.Parent.ProcessedDate && (
                            r.EndDate == null || r.Child.Parent.ProcessDate <= r.EndDate
                           )
                           && r.ChildId == r.Child.Parent.ChildId && 
                 r.Child.Parent.Anniversary >= r.MAnniversary
               ).AsQueryable().AsNoTracking();

Upvotes: 1

Views: 558

Answers (1)

haydnD
haydnD

Reputation: 2293

I added Parent navigation property to Child and changed the query to be based on the inner relations. It looks something like this:

var data = _context.Animals
        .Include(r => r.Child)
            .ThenInclude(a => a.Parent)
        .Where(r => r.StartDate <= r.Child.Parent.ProcessedDate && (
                            r.EndDate == null || r.Child.Parent.ProcessDate <= r.EndDate
                           )
                           && r.ChildId == r.Child.Parent.ChildId && 
                 r.Child.Parent.Anniversary >= r.MAnniversary
               ).AsQueryable().AsNoTracking();

Upvotes: 0

Related Questions