Fritz Fritz - 1 year ago 102
MySQL Question

How to do Multiple Left Join, Group By, and Concatenate in LINQ

I am trying to display the 'names' of the dialects (from 'lu_dialect_t') of the Parents of a specific Child. I am doing multiple left joins with the LINQ query and now I am hoping to find a way on how to GROUP the query by the 'parent_id' and concatenate the 'name' (of dialects spoken by the parent) to one column and store it in a variable for my ViewModel.

This is my ViewModel:

public class ParentViewModel
public int parent_id { get; set; }
public string last_name { get; set; }
public string first_name { get; set; }
public string middle_name { get; set; }
public string ext_name { get; set; }
public Nullable<System.DateTime> birthdate { get; set; }
public string civil_status { get; set; }
public string email_address { get; set; }
public string cell_num { get; set; }
public string tel_num { get; set; }
public string fax_num { get; set; }
public string room_num_or_building { get; set; }
public string street { get; set; }
public string purok { get; set; }
public string subdivision { get; set; }
public Nullable<int> brgy_id { get; set; }
public string city_code { get; set; }
public string province_code { get; set; }
public string mother_tongue { get; set; }
public string educational_attainment { get; set; }
public string occupational_status { get; set; }
public string parent_type { get; set; }
public string deceased { get; set; }
public Nullable<System.DateTime> survey_date_conducted { get; set; }
public string person_who_conducted { get; set; }
public int child_id { get; set; }
public string parent_dialects { get; set; }

This is my Controller:

public ActionResult Parents(int id)
{ var query = (from p in db.parent_t

join cp in db.tn_child_parent_t on p.parent_id equals cp.parent_id into tcpGroup
from x in tcpGroup.DefaultIfEmpty()

join c in db.child_t on x.child_id equals c.child_id into cGroup
from y in cGroup.DefaultIfEmpty()

join pd in db.tn_parent_dialect_t on p.parent_id equals pd.parent_id into tpdGroup
from a in tpdGroup.DefaultIfEmpty()

join d in db.lu_dialect_t on a.dialect_id equals d.dialect_id into dGroup
from b in dGroup.DefaultIfEmpty()

where (y.child_id == id)

select new ViewModels.ParentViewModel
parent_id = p.parent_id,
last_name = p.last_name,
first_name = p.first_name,
middle_name = p.middle_name,
ext_name = p.ext_name,
birthdate = p.birthdate,
civil_status = p.civil_status,
email_address = p.email_address,
cell_num = p.cell_num,
tel_num = p.tel_num,
fax_num = p.fax_num,
room_num_or_building = p.room_num_or_building,
street = p.street,
purok = p.purok,
subdivision = p.subdivision,
brgy_id = p.brgy_id,
city_code = p.city_code,
province_code = p.province_code,
mother_tongue = p.mother_tongue,
educational_attainment = p.educational_attainment,
occupational_status = p.occupational_status,
parent_type = p.parent_type,
deceased = p.deceased,
survey_date_conducted = p.survey_date_conducted,
person_who_conducted = p.person_who_conducted,
parent_dialects =,
return View(query);

Right now, the query just displays shows my table like this:

My current progress

But what I want is like this:

The desired result

Please help, I have been trying to find a way to do this for hours. Thank you.

Answer Source

Here is something similar

 static void Main(string[] args)
            var items = Enumerable.Range(0, 10).Select(p => new { Name = "Name" + p%2, LasetName = "LN"+p%2, Dialect = "D"+p });

            var data = from item in items
                       group item by item.Name into g
                       select new
                           Name = g.Key,
                           LastName = g.First().LasetName,
                           Dialect = string.Join(",", g.Select(d=>d.Dialect))
            foreach (var item in data)
                Console.WriteLine($"Name:{item.Name}, Dialect:{item.Dialect}");

Post process the var query with your group by and youse first for all the single properties you need. If you are using EF you will need to do a ToList first to get the data to memory for the concatenation. Also if there is a lot of data pulling all the rows in memory is not the best.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download