How to filter a database item through a List field?

Question

I have the entity MessageEntity which contains several sessions (SectionsEntity) which in turn contains several calls (TreatmentEntity).

I need to bring the messages, filtered by your Id, filtered by active sessions and active calls.

Follows the entities:

public class MessageEntity: AggEntity
{
....
     public MessageEntity()
     {

     }

     public virtual ICollection<SectionsEntity> Sections { get; private set; }
...
}

public class SectionsEntity : AggEntity
{
    ...
    public SectionsEntity() { }
    ..
    public virtual ICollection<TreatmentEntity> Treatments { get; private set; } = new List<TreatmentEntity>();
    public DateTime? FinishAt { get; private set; } 
}

public class TreatmentEntity : AggEntity
{
...
    public AttendanceEntity()
    {

    }
    public DateTime? FinishAt { get; private set; }
....    
}

I have an extension class that returns to Messageentity:

public static IQueryable<MessageEntity> ActiveSectionsByMessageId(this IQueryable<MessageEntity> source, Guid id)
{
    return source.Where(x => x.Id == id)
        

}

How to bring only the Message that has the fields FinishAt == null ?

Thank you very much

Answer 1

Opa, I made a little console design and changed some things in its classes as the encapsulation to be able to demonstrate how would be a query in LINQ to do the filtering follows the code:

public class MessageEntity
    {

        public MessageEntity()
        {
            Sections = new List<SectionsEntity> {
                new SectionsEntity {Id = 1, FinishAt = null},
                new SectionsEntity {Id = 2,FinishAt = DateTime.Now.AddDays(1)},
                new SectionsEntity {Id = 3,FinishAt = null},
                new SectionsEntity {Id = 4,FinishAt = DateTime.Now.AddDays(-2)},
                new SectionsEntity {Id = 5,FinishAt = null},
                new SectionsEntity {Id = 6,FinishAt = DateTime.Now.AddDays(-1)},
                new SectionsEntity {Id = 7,FinishAt = DateTime.Now.AddDays(-10)},
            };
        }

        public virtual ICollection<SectionsEntity> Sections { get; private set; }

    }

    public class SectionsEntity
    {

        public SectionsEntity() { 
            Treatments = new List<TreatmentEntity> {
                new TreatmentEntity {Id = 1, FinishAt = null},
                new TreatmentEntity {Id = 2,FinishAt = DateTime.Now.AddDays(1)},
                new TreatmentEntity {Id = 3,FinishAt = null},
                new TreatmentEntity {Id = 4,FinishAt = DateTime.Now.AddDays(-2)},
                new TreatmentEntity {Id = 5,FinishAt = null},
                new TreatmentEntity {Id = 6,FinishAt = null},
                new TreatmentEntity {Id = 7,FinishAt = null},
            };
        }

        public virtual ICollection<TreatmentEntity> Treatments { get; private set; } = new List<TreatmentEntity>();
        public int Id { get; set; }
        public DateTime? FinishAt { get; set; }
    }

    public class TreatmentEntity
    {

        public TreatmentEntity()
        {

        }
        public int Id { get; set; }
        public DateTime? FinishAt { get; set; }

    }

And my program class was like this:

class Program
    {
        static void Main(string[] args)
        {
            var message = new MessageEntity();

            var somenteNull = message.Sections.Where(w => w.FinishAt == null && w.Treatments.Any(w => w.FinishAt == null)).ToList();

            foreach (var item in somenteNull)
            {
                Console.WriteLine($"Id: {item.Id} -> Finalizado: {item.FinishAt}");
            }
        }

    }

The output were the 3 instances that have null in both Sectionsentity and Treatmententity follows:

$ dotnet run
Id: 1 -> Finalizado: 
Id: 3 -> Finalizado: 
Id: 5 -> Finalizado: