Lambda expression to use outer group for tree nodes

Question

The following linq code works correctly to provide record groups to children of the topmost specialitynodes. How is this done using lambda's?

The top most query obtains the view_consulting records from the database and correctly groups them by speciality_name:

// get the records from the database.
            view_consulting[] v = MyNetwork.Medical.Client.GetConsultingStaff();

            // All records will first be grouped by the speciality name. The specialityGroup will hold all the records for the specific speciality.
            var s = v.GroupBy(p => p.speciality_name)
                .Select((IGrouping<string, view_consulting> specialityGroup) =>
                      new SpecialityNode(specialityGroup.Key, specialityGroup, new speciality { speciality_name = specialityGroup.Key }));
            var ss = s.ToList();

Each SpecialityNode then has a constructor as follows:

// The specialityGroup holds all the records for the speciality.
        public SpecialityNode(string name, IGrouping<string, view_consulting> specialityGroup, speciality data) : base(name, null, null, data)
        {
            // Create a subgroup for each office under each SpecialityNode.           
            var offices = new List<OfficeNode>();

            var officeGroups = from t in specialityGroup
                               group t by t.office_name;

            foreach (var officegroup in officeGroups)
            {
                foreach (var _office in officegroup)
                {
                    var _officenode = new OfficeNode(_office.office_name, this, this, officegroup, new office { city = _office.city, office_name = _office.office_name, phone = _office.phone, fax = _office.fax, state = _office.state, street = _office.street });
                    offices.Add(_officenode);
                }
            }
            Children = offices.ToList<ITreeNode>();
        }

This all works correctly for my needs. Note that the last foreach() is using a reference to the outer foreach() in the SpecialityNode constructor.

Can all these "foreach's" be combined into a a single lambda expression?

Thanks in advance.

If it helps to clarify, here is the OfficeNode constructor. Note: It uses the group to which this office is part of to generate the consultants in each office.

class OfficeNode : TreeViewBase
    {
        private office data;

        public class MyDoctor
        {
            public string lastname { get; internal set; }
            public string firstname { get; internal set; }
            public string speciality { get; internal set; }
            public consultant data { get; internal set; }

            public string nodename
            {
                get { return string.Format("{0}  {1}  {2}", lastname, firstname, speciality); }
            }
        }

        // The name for the office node is the office name. The office node parent and root are the same and is the speciality node.
        public OfficeNode(string officename, ITreeNode _parent, ITreeNode _root, IGrouping<string, view_consulting> officeGroup, office _data) : base(officename, _parent, _root, _data)
        {
            var _doctors = officeGroup.Where(q => !string.IsNullOrEmpty(q.lastname))
                 .GroupBy(q => new MyDoctor {
                     lastname = q.lastname,
                     firstname = q.firstname,
                     speciality = q.speciality_name,
                     data = new consultant { lastname = q.lastname, firstname = q.firstname }
                 })
                 .Select(doctorGroup => new DoctorNode(doctorGroup.Key.nodename, this, _root, doctorGroup, doctorGroup.Key.data));

            Children = _doctors.ToList<ITreeNode>();

            City = _data.city;
            Phone = _data.phone;
            Fax = _data.fax; 
          //  Order = order ?? throw new ArgumentNullException(nameof(order));

}

Answer 1

Yes, you can using the SelectMany. SelectMany allows you to flatten multiple collections into a single collection. Since it appears your officeGroups is an enumerable of enumerables, SelectMany will return you back a single enumerable of all of the items.

var list = officeGroups.SelectMany(o => o)
                        .Select(o => new OfficeNode(constructorParam1,
                                                    constructorParam2,
                                                    etc.))
                        .ToList();

However, as clean as you may think it looks right now. Once you dump all of your parameters in and then your Office initializer, it is going to be harder to read and track than your foreach. It will also be harder to debug.

EDIT: Since I missed the part of still needing the officeGroup, we can pivot a little and build the collection off of the groups first and harness anonymous objects.

var list = officeGroups.Select(og => new 
                                     {
                                        Offices = og.Select(o => new OfficeNode(o.office_name,
                                                                                this,
                                                                                this,
                                                                                og,
                                                                                etc.))
                                     }
                        .SelectMany(x => x.Offices)
                        .ToList();