How can I linq object grouping

Question

How can I group these objects? I can get via LINQ and bind that. But this is not useful because I need reverse grouping. Like That :

MainGroup > Group List > Subgroup List

Maybe I can do that with LINQ query first getting values. I don't know.

I'm starter user for LINQ. I don't have much information. Thank you in advance for your help. I get and bind mongodb data like that :

var subGroupCollection = Database.GetCollection<SubGroup>(typeof(SubGroup).Name);
var groupCollection = Database.GetCollection<Group>(typeof(Group).Name);
var mainGroupCollection = Database.GetCollection<MainGroup>(typeof(MainGroup).Name);

var query = from sg in subGroupCollection.AsQueryable()
            join mg in mainGroupCollection on sg.MainGroupId equals mg.Id into mainGroups
            join z in groupCollection on sg.GroupId equals z.Id into groups
            select new SoccerOddType
            {
                Id = sg.Id,
                IsActive = sg.IsActive,
                GroupId = sg.GroupId,
                Name = sg.Name,
                LastUpdateDate = sg.LastUpdateDate,
                CreatedDate = sg.CreatedDate,
                Order = sg.Order,
                Discount = sg.Discount,
                DiscountType = sg.DiscountType,
                MainGroupId = sg.MainGroupId,
                MainGroup = mainGroups.First(),
                Group = groups.First()
            };

From :

public class MainGroup
{
    public string Id { get; set; }
    public string Name { get; set; }
    public bool IsActive { get; set; }
}

public class Group
{
    public string Id { get; set; }
    public string MainGroupId { get; set; }
    [BsonIgnore] public Group MainGroup { get; set; }
    public string Name { get; set; }
    public bool IsActive { get; set; }
}

public class SubGroup
{
    public string Id { get; set; }
    public string Name { get; set; }
    public string MainGroupId { get; set; }
    public string GroupId { get; set; }
    [BsonIgnore] public Group MainGroup { get; set; }
    [BsonIgnore] public Group Group { get; set; }
    public bool IsActive { get; set; }
    public decimal Discount { get; set; }
    public EnmDiscountType DiscountType { get; set; }
}

To :

public class MainGroupViewModel
{
    public string Id { get; set; }
    public string Name { get; set; }
    public bool IsActive { get; set; }
    public List<GroupViewModel> Groups { get; set; }
}

public class GroupViewModel
{
    public string Id { get; set; }
    public string MainGroupId { get; set; }
    public string Name { get; set; }
    public bool IsActive { get; set; }
    public List<SubGroupViewModel> SubGroups { get; set; }
}

public class SubGroupViewModel
{
    public string Id { get; set; }
    public string Name { get; set; }
    public string MainGroupId { get; set; }
    public string GroupId { get; set; }
    public bool IsActive { get; set; }
    public decimal Discount { get; set; }
    public EnmDiscountType DiscountType { get; set; }
}

Answer 1

If you have a one-to-many relation, using a foreign key, and you want items-with-their-subitems, like Schools with their Students, Customers with their Orders, MainGroups with their SubGroups, then you can use a simple Select to get your result, or use GroupJoin

Select

var result = mainGroupCollection.Select(mainGroup => new MainGroupViewModel
{
    Id = mainGroup.Id,
    Name = mainGroup.Name,
    ...

    // Keep only the Groups of this MainGroup, using foreign key
    Groups = groupCollection
        .Where(group => group.MainGroupId == mainGroup.Id)
        .Select(group => new GroupViewModel
        {
            Id = group.Id,
            Name = group.Name,
            ...

            // Keep only the subGroups of this Group, using foreign key
            SubGroups = subGroupCollection
                .Where(subGroup => subGroup.GroupId == group.Id)
                .Select(subGroup => new SubGroupViewModel
                {
                    Id = group.Id,
                    Name = group.Name,
                    ...
                })
                .ToList(),
        })
        .ToList(),
});

Although this method works, it is not very efficient, because for every element in the mainGroupCollection it has to enumerate over the entire GroupCollection, and for every element it has to enumerate over the entire SubGroupCollection.

Depending on the DBMS that you use if you are querying from a database this is not a big issue. However, I would go for a GroupJoin

GroupJoin

Enumerable.GroupJoin is way more efficient (or the IQueryable equivalent). The Enumerable version uses a Dictionary to see if it already has found an item with this Id, so it does not need to enumerate more than once over each collection.

// GroupJoin the mainGroupCollection with the groupCollection:
var result = mainGroupCollection.GroupJoin(groupCollection,
    mainGroup = mainGroup.Id,         // from every mainGroup take the primary key
    group => group.MainGroupId,       // from every group take the foreign key

    // ResultSelector: for every mainGroup and its groups make one MainGroupViewModel
    (mainGroup, groupsOfThisMainGroup) => new MainGroupViewModel
    {
        Id = mainGroup.Id,
        Name = mainGroup.Name,
        ...

        // for the Groups: GroupJoin the groups of this main group with the subGroups
        Groups = groupsOfThisMainGroup.GroupJoin(subGroupCollection,
            groupOfThisMainGroup => groupOfThisMainGroup.Id,
            subGroup => subGroup.GroupId,

            // result selector
            (group, subGroupsOfThisGroup) => new GroupViewModel
            {
                Id = group.Id,
                Name = group.Name,

               SubGroups = subGroupsOfThisGroup.Select(subGroup => new SubGroupViewModel
               {
                    Id = subGroup.Id,
                    Name = subGroup.Name,
                    ...
               })
               .ToList(),
        })
        .ToList(),
    });

Answer 2

I'd probably go with a .Select().

var subGroups = subGroupCollection.Select(sg => new SubGroupViewModel
{
    Id = sg.Id,
    Name = sg.Name,
    MainGroupId = sg.MainGroupId,
    GroupId = sg.GroupId,
    IsActive = sg.IsActive,
    Discount = sg.Discount,
    DiscountType = sg.DiscountType
});

var groups = groupCollection.Select(g => new GroupViewModel 
{
    Id = g.Id,
    MainGroupId = g.MainGroupId,
    Name = g.Name,
    IsActive = g.IsActive,
    SubGroups = subGroups.Where(sg => sg.GroupId == g.Id).ToList() 
});

var mainGroups = mainGroupCollection.Select(mg => new MainGroupViewModel
{
    Id = mg.Id,
    Name = mg.Name,
    IsActive = mg.IsActive,
    // .Any() or .All() here?
    Groups = groups.Where(g => g.SubGroups.Any(sg => sg.MainGroupId == mg.Id))
});