Problem understanding Linq query structure when involves filtering, flattening and grouping

Question

I'm facing a lot of problems trying to fetch data from this Data Model using LinqToEntities.

I'm working with EF Core 2.1 and Linq on a SQL Server database.

Basically I need to show events on a weekly calendar, but the data structure is quite complex. Events could have multiple EventDate (every date is a single day with a starting and ending hour) and on each day many different Contacts partecipate sometimes on a different time than the generic one on the EventDate.

I need to filter them by a week period (Startdate, EndDate) and occasionally also by a list of contacts involved.

The main problem is that I need to extract a list of distinct objects (filtered as requested) one for each EventDate but with all the overrides grouped within it and with the list of contacts for each override group, like this sample structure:

EventID,
EventName, 
//...Other Data from Events
FromDateTime, //Taken from the EventDate Table
ToDateTime, //Taken from the EventDate Table
//...Other Data from EventDates
Overrides:[
    //Taken from the EventDateContact Table
    {
        OverrideFromHour, 
        OverrideToHour 
        Contacts [contactid, contactid, ...] 
    },
    {
        OverrideFromHour,
        OverrideToHour
        Contacts [contactid, contactid, ...]
    }
]

I'm still having problems understanding LinqToEntities on very complex queries.

In a non EF/Linq application I would have done a Join between tables then iterated on the results grouping manually as needed, but I'm wondering if is there a way to do this directly using LinqToEntities or mixing it with LinqToObjects woking in memory after one or diffenrent an initial queries.

I've tried different join and grouping but I can't figure out how make it works!

Here is the data model:

public class Event {

    public Event()
    {
        EventDates = new HashSet<EventDate>();
        EventDatesContacts = new HashSet<EventDatesContact>();
    }

    public int ID { get; set; }

    public string Name { get; set; }

    ...other data

    public virtual ICollection<EventDate> EventDates { get; set; }

    public virtual ICollection<EventDatesContact> EventDatesContacts { get; set; }
}


public class EventDate {

    public EventDate()
    {
        EventDatesContacts = new HashSet<EventDatesContact>();
    }

    public int ID { get; set; }

    public int EventID { get; set; }

    //The Date part remain the same between From and To. 
    //Only the Hour can change.
    public DateTime FromDateTime { get; set; }

    public DateTime ToDateTime { get; set; }

    //...other data

    public virtual Event Event { get; set; }

    public virtual ICollection<EventDatesContact> EventDatesContacts { get; set; }

}

public class EventDatesContact {

    public int ID { get; set; }

    public int EventID { get; set; }

    public int ContactID { get; set; }

    public int EventDateID { get; set; }

    //If not null these overrides the corresponding DateTime in the EventDate
    //The Date part remain the same between From and To and also the same as the linked EventDate. 
    //Only the Hour can change.
    public DateTime? OverrideFromTime { get; set; }

    public DateTime? OverrideToTime { get; set; }

    //...other data

    public virtual Contact Contact { get; set; }

    public virtual EventDate EventDate { get; set; }

    public virtual Event Event { get; set; }

}

Thank you in advance for any support!

Answer 1

I don't use EF much, but you shouldn't need to join with EF. You should be able to just use the Navigation properties, but you will need to group since you are consolidating the EventDate.EventDatesContacts in your result. Here is my attempt:

var results = db.Events
                .Include(e => e.EventDates).ThenInclude(ed => ed.EventDatesContacts)
                .SelectMany(e => e.EventDates.Select(ed => new Result {
                    EventID = e.ID,
                    EventName = e.Name,
                    FromDateTime = ed.FromDateTime,
                    ToDateTime = ed.ToDateTime,
                    Overrides = ed.EventDatesContacts.GroupBy(edc => new {
                            FromTime = edc.OverrideFromTime == null ? ed.FromDateTime : edc.OverrideFromTime.Value,
                            ToTime = edc.OverrideToTime == null ? ed.ToDateTime : edc.OverrideToTime.Value
                        },
                        edc => edc.ContactID
                        )
                        .Select(edcg => new EventOverride {
                            OverrideFromHour = edcg.Key.FromTime,
                            OverrideToHour = edcg.Key.ToTime,
                            ContactIDs = edcg.ToList()
                        })
                        .ToList()
                }));

My understanding is you may not need the Include if you have eager loading enabled.