I have three tables that are related to each other. I searched for groupBy methods in entityframwork but did not get the correct answer. this is tables:

public class DayProgramOffice
{
    public long Id { get; set; }
    public DateTime Day { get; set; }
    public virtual ICollection<ScheduleOffice> ScheduleOffices { get; set; }

}
public class ScheduleOffice
{
    public long Id { get; set; }
    public DateTime Time { get; set; }

    public virtual DayProgramOffice DayProgramOffice { get; set; }
    public long DayProgramOfficeId { get; set; }

    public virtual TypeConsultaion TypeConsultaion { get; set; }
    public long TypeConsultaionId { get; set; }

}

public class TypeConsultaion
{
    public byte Id { get; set; }
    public string TypeName { get; set; }
    public virtual ICollection<ScheduleOffice> ScheduleOffices { get; set; }
}

and have three viewmodel that want send data to it:

public class Day
{
    public DateTime day { get; set; }
    public IList<TypeConViewModel> Types{ get; set; }
}

public class TypeConViewModel
{
    public string TypeName { get; set; }
    public IList<HoverViewModel> Hovers { get; set; }

}

public class HoverViewModel
{
    public DateTime Hover { get; set; }
}

in view I want show data like this:

@foreach (var item1 in Model.Days)
{

    <p>@item1.Day</p>
    foreach (var item2 in item1.Types)
    {

        <p>@item2.TypeName</p>

        foreach (var item3 in item2.Hover)
        {
            <p>@item3.hover</p>
        }
    }

}

I use this code but cannot group data:

_db.DayProgramOffice
    .Include(c=>c.ScheduleOffice)
    .ThenInclude(d=>d.TypeConsultaion)
    .ToList():

CodePudding user response：

group by this way, but you can improve the query performance with change the tables structures!

Controller or Action :

    public IActionResult Index()
    { 
        var daysModel = _db.ScheduleOffices
                             .Include(a => a.DayProgramOffice)
                             .Include(b => b.TypeConsultaion)
                             .ToList()
                             .GroupBy(d => d.DayProgramOffice)
                             .Select(d => new Day
                             { 
                                day = d.Key.Day,
                                Types = d.GroupBy(x => x.TypeConsultaion).Select(f => new TypeConViewModel { 
                                    TypeName = f.Key.TypeName,
                                    Hovers = f.Select(g => new HoverViewModel { 
                                        Hover = g.Time
                                    }).ToList()
                                }).ToList()
                             })
                             .ToList();
        
        return View(daysModel);
    }

And in your views you have some bug, change it to this :

@foreach (var item1 in Model){
   <p class="day">@item1.day</p>
   foreach (var item2 in item1.Types){
    <p class="type">@item2.TypeName</p>
    foreach (var item3 in item2.Hovers)
    {

        <p class="hover">@item3.Hover</p>
    }
}

}

CodePudding user response：

So you have a table with DayProgramOffices, and a table with ScheduleOffices. There is a one-to-many relation between DayProgramOffices and ScheduleOffices: every DayProgramOffice has zero or more ScheduleOffices, every ScheduleOffice belongs to exactly one DayProgramOffice, namely the DayProgramOffice that the foreign key refers to.

Similarly there is a one-tomany relation between TypeConsultations and ScheduleOffices: every TypeConsultation has zero or more ScheduleOfficess, every ScheduleOffice belongs to exactly one TypeConsultation, namely the one that the foreign key refers to.

Alas, you gave us some code that doesn't do what you want, and you forgot te precisely specify what you do want. However, from your code I think that you want the following:

Requirement: From every DayProgramOffice give me all its zero or more ScheduledOffices, together with the TypeConsultation of this ScheduledOffice.

Easy method: use the virtual ICollection

When joining tables using entity framework, the most easy solution is usually using the virtual ICollections. Entity Framework knows your relations, and can translate this into the proper (Group-)Joins.

using (var dbContext = new OfficeDbContext(...))
{
    var dayProgramOffices = dbContext.DayProgramOffices.Select(dayProgramOffice => new
    {
        // Select the DayProgramOffice properties that you plan to use:
        Id = dayProgramOffice.Id,
        Day = dayProgramOffice.Day,
        ...

        // Get all ScheduledOffices of this dayProgramOffice
        ScheduledOffices = dayProgramOffice.ScheduledOffices
            .Select(scheduleOffice => new
            {
                // Select only the properties that you plan to use:
                Id = scheduledOffice.Id,
                ...

                // Don't select the foreign key, you already know the value
                // DayProgramOfficeId = scheduledOffice.DayProgramOfficeId,

                // This ScheduledOffice has exactly one TypeConsultation:
                TypeConsultation = new
                {
                    Id = scheduledOffice.TypeConsultation.Id,
                    TypeName = scheduledOffice.TypeConsultation.TypeName,
                    ...
                }
             })
             .ToList(),
        });
}

I chose to create anonymous types (new {...}), to prevent transferring values that are not used from the database management system to your local process. This will also give you the freedom to add properties that are not in the tables, for instance the earliest time that the office has been scheduled:

DayProgramOffices.Select(dayProgramOffice => new
{
    ...
    NewestScheduledOfficeTime = dayProgramOffice.ScheduledOffices
        .Select(scheduledOffice => scheduledOffice.Time)
        .Min(),

Of course, if you want, you can specify the types in which you want to put your fetched data in.

Alternative: use the foreign keys

var dayProgramOffices = dbContext.DayProgramOffices.Select(dayProgramOffice => new
{
    Id = dayProgramOffice.Id,
    Day = dayProgramOffice.Day,
    ...

    // Get all ScheduledOffices of this dayProgramOffice using the foreign key
    ScheduledOffices = dbContext.ScheduledOffices
        .Where(scheduledOffice.DayProgramOfficeId == dayProgramOffice.Id)
        .Select(scheduleOffice => new
        {
            Id = scheduledOffice.Id,
            ...

            // fetch the TypeConsultation of this ScheduledOffice using the foreign key
            TypeConsultation = dbContext.TypeConsultations
                .Where(typeConsultation => typeConsulation.Id == scheduledOffice.TypeConfulstionId)
                .Select(typeConsulation => new
                {
                    Id = typeConsultation.Id,
                    TypeName = typeConsultation.TypeName,
                    ...
                })
                // expect exactly one suche TypeConsultation:
                .SingleOrDefault(),
         })
         .ToList(),
    });

It might be that your version of entity framework doesn't allow SingleOrDefault. Of course you can use FirstOrDefault as well. This is probably even be more efficient, because there is no check of uniqueness.

The hard way: do the (Group-)Joins yourself

Entity framework will translate the Expressions in the previous method into (Group-)Joins. Of course you can do these GroupJoins yourself:

var dayProgramOffices = dbContext.DayProgramOffices.GroupJoin(
    dbContext.ScheduledOffices,

    dayProgramOffice => dayProgramOffice.Id,               // take the primary key
    scheduledOffice => scheduledOffice.DayProgramOfficeId, // take the foreign key

    // parameter resultSelector: for every dayProgramOffice and all zero or more
    // scheduledOffices that have a foreign key that refers to this dayProgramOffice
    // make one new:
    (dayProgramOffice, scheduledOffices) => new
    {
        Id = dayProgramOffice.Id,
        Day = dayProgramOffice.Day,

        // To get the one and only TypeConsultaition of each ScheduledOffice do a Join
        ScheduledOffices = scheduledOffices.Join(
            dbContext.TypeConsultations,

            scheduledOffice => scheduledOffice.TypeConsultationId, // foreign key
            typeConsultation => typeConsultation.Id,               // primary key

            // parameter resultSelector: for every scheduledOffice, with its one
            // and only typeConsultation, make one new:
            (scheduledOffice, typeConsultation) => new
            {
                Id = scheduledOffice.Id,
                ...

                TypeConsultation = new
                {
                    Id = typeConsultation.Id,
                    TypeName = typeConsultation.TypeName,
                    ...
                }),
        })
        .ToList(),
   });

If you have a one-to-many relation, and you want "items with their sub-items", start at the "one" side, and use a GroupJoin. If you want the sub-item with its one and only parent item that the foreign key refers to, use a Join.

Why you should not use Include

Your Dbcontext has a ChangeTracker. If you query an object without using Select, then the complete row of the table is transferred.

Apart from that this might transfer properties that you didn't plan to use, the object is also put in the ChangeTracker, together with a copy of the object. You get the reference to the copy (or maybe the original, doesn't matter, theory is the same). Whenever you make changes to the fetched object, then in fact you are changing the copy in the ChangeTracker.

If you call DbContext.SaveChanges, then every property of the original is compared value-wise with the copy. If they are not equal, then the changed values are updated in the database.

If you don't plan to change the fetched object, this will be quite a waste of processing power.

When fetching data from the database using entity framework, always use Select, and select only the properties that you actually plan to use. Only fetch the complete object and only use Include / ThenInclude if you plan to change the fetched data.