Is it possible to select mapped entity into another mapped entity in a single query?-CodePudding

Is there any elegant way to perform a nested LINQ selection into objects that are being selected themselves? In other words, let's assume there are three DB tables all with one-to-many relation: Schedule, Day (One schedule may have many days) and Activity (One day may have many activities. I would like to try to build a query which selects data into related mapped objects without the necessity of creating additional helper objects. Is that even possible? Please see below objects which map DB tables:

public Class Schedule{
   public int ScheduleId { get; set; }
   ...
   public byte[] RowVersion { get; set; }

   [NotMapped]
   public List<Day> days; //Supposed to store Day objects
}

public Class Day{
   public int DayId { get; set; }
   ...
   public byte[] RowVersion { get; set; }

   [NotMapped]
   public List<Activity> activities; //Supposed to store Activity objects
}

public Class Activity{
   public int ActivityId { get; set; }
   ...
   public byte[] RowVersion { get; set; }
}

At this point I use additional classes to fetch data: SchedulePrim, DaysPrim and ActivitiesPrim. After the query is executed I put Prims into [NotMapped] attributes of proper objects (see above) and then get rid of Prims. To me this seem like using unnecessary resources. The query looks somewhat like this:

from schedules in context.Schedules.Where(...)
select new SchedulePrim
{
    Schedule = schedules
    DaysPrim = from days in context.Days.Where(...)
               select new DaysPrim
               {
                    Day = days
                    ActivitiesPrim = from activities in context.Activities.Where(...)
                                     select new DaysPrim
                                     {
                                          Activity = activities
                                     }
               }
}

Here comes the logic of reprocessing fetched data into proper entities. Is there a faster way to do this? The way that lets selecting data into [NotMapped] attributes on the fly, without the need of introducing additional processing?

CodePudding user response：

Just eliminate the NotMapped attributes and the "additional classes", make sure you have proper foreign keys, and load the data using Include.

db.Schedules.Include(s => s.Days).ThenInclude(d => d.Activities).Where(...)