LINQ Group By on two property then filtering result-CodePudding

I have a class1 which have 3 child classes

I like to Create LINQ with

public class ReturnObject
{
    Class2Field1 
    Class2Field2
    Address List<XYZ> Class3Fields 
    Class4Field1
}


Class1
    .Where(ct => ct.Id == input.Id  && ct.Class2 != null && !ct.RemovedDatetime.HasValue)
    .OrderBy(ct => ct.CreatedDatetime)
    //.GroupBy(h=> new {  h.Class2,  h.Class3 })
    .Select(h => new ReturnObject
    {

        Class2Field1 = h.Class2.F1,
        Class2Field2 = h.Class2.F2,
        Class4Field1 = h.Class4 != null ? h.Class4.F1 : null,
        Address = h.Class3 == null ? null : new List<XYZ> { new XYZ
            {
                XYZF1 = h.Class3.F1                      
            } 
        }
    }).ToListAsync()

But Can't able to group by correctly. so at the moment only return the first record of the

Model is like below

  public  class Class1
{
    public int Id { get; set; }       
    public int? Class2Id { get; set; }        
    public int? Class3Id { get; set; }
    public int? Class4Id { get; set; }

    public virtual Class2? Class2 { get; set; }

    public virtual Class3? Class3 { get; set; }

    public virtual Class4? Class4 { get; set; }
}

Table structure and required response like

Table1 PKC1T1, C2T1,C3T1, FkColumnT2, FKColumnT3, FKColumnT4

Table2 PKT2, C2T2,C3T2,

Table3 PKT3, C2T3,C3T3,

Table4 PKT4, C1T4,C2T4,

ResultSet { C2T2, C3T2, List lst = new List C1T4

}

CodePudding user response：

Try the following query:

var result = (await Class1
    .Where(ct => ct.Id == input.Id  && ct.Class2 != null && !ct.RemovedDatetime.HasValue)
    .OrderBy(ct => ct.CreatedDatetime)
    .Select(h => new 
    { 
        Class2Field1 = h.Class2.F1, 
        Class2Field2 = h.Class2.F2, 
        Class4Field1 = h.Class4.F1, 
        Class3Field1 = h.Class3.F1
    })
    .ToListAsync())
    .GroupBy(h => new 
    { 
        h.Class2Field1, 
        h.Class2Field2, 
        h.Class4Field1, 
    })
    .Select(g => new ReturnObject
    {

        Class2Field1 = g.Key.Class2Field1,
        Class2Field2 = g.Key.Class2Field2,
        Class4Field1 = g.Key.Class4Field1,
        Address = g.Select(x => new XYZ
            {
                XYZF1 = x.Class3Field1                   
            }).ToList()
        }
    }).ToList();

Type of field h.Class3.F1 is hidden for me and maybe we need cast.