c# - 如何在 LINQ 中执行多个左连接、分组依据和连接

Question

我正在尝试显示特定 child 的 parent 的方言(来自“lu_dialect_t”)的“名称”。我正在对 LINQ 查询进行多次左连接，现在我希望找到一种方法来通过“parent_id”对查询进行分组并将“名称”( parent 所说的方言)连接到一列并将其存储在我的 ViewModel 的变量中。

这是我的 View 模型:

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; }
}

这是我的 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 = b.name,
         });
    return View(query);
}

现在，查询只显示我的表，如下所示:

My current progress

但是我想要的是这样的:

The desired result

请帮忙，几个小时以来我一直在努力寻找一种方法来做到这一点。谢谢。

Answer 1

这里有类似的东西

 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}");
            }
            Console.WriteLine("Done");
            Console.ReadLine();
        }

使用您的分组依据对 var 查询 进行后处理，然后您首先使用您需要的所有单个属性。如果您使用的是 EF，则需要先执行 ToList 以将数据存入内存以进行串联。此外，如果有大量数据，将所有行都拉入内存也不是最好的方法。