将另一个表添加到分组的linq查询中 [英] Adding another table to a grouped linq query

问题描述

我有两个桌子

Person
__________
PersonID
PersonName
DOB
Status


Notes
__________
NoteID
PersonID
NoteText
Comments
LineNo

这是一些示例内容

 PersonID   PersonName    DOB          Status
   1    Mark Jacobs   07/07/1961    Active

和

NoteID  PersonID    NoteText    LineNo
 123       1        Line 1      1
 234       1        Line 2      2
 236       1        Line 3      3

因此，作为最终结果，我希望Linq查询显示类似的内容

So as an end result I want a Linq query to display something like that

PersonID    PersonName    DOB           Note
   1    Mark Jacobs   07/07/1961        Line 1, Line 2, Line 3

我对Notes表有一个有效的linq查询，但也想包括Persons表中的一些字段:

I have a working linq query for the Notes table, but would like to include some fields from Persons table as well:

 var result = (from n in db.Notes
                  group n.NoteText by n.PersonID into g
                  select new { 
                      PersonID = g.Key, 
                      Notes = g.ToList()
                  }).AsEnumerable() 
                 .Select(item => new NoteGroupDTO { 
                     PersonID = item.PersonID,
                     Notes = string.Join(", ", item.Notes)
                 }).ToList();

我想在选择列表中添加人名"，"DOB"和状态".

I'd like to add Person name, DOB, and Status to the select list.

我创建了一个课程

public class PersonNoteDTO
{
    public int PersonID { get; set; }
    public string PersonName { get; set; }
    public DateTime DOB { get; set; }
    public string Status { get; set; }
    public string Notes { get; set; }
}

对于我的查询，我添加了一个join和order by子句以按行号排序.但是我不确定如何在我的匿名对象的选择列表中添加字段:

To my query I added a join and order by clause to order by line numbers. But I am not sure how to add fields to the select list in my anonymous object:

 var result = (from n in db.Notes
                  join p in db.Persons on n.PersonID=p.PersonID
                  orderby n.LineNo
                  group n.NoteText by n.PersonID into g
                  select new { 
                      PersonID = g.Key, 
                      Notes = g.ToList()
                  }).AsEnumerable() 
                 .Select(item => new PersonNoteDTO { 
                     PersonID = item.PersonID,
                     Notes = string.Join(", ", item.Notes)
                 }).ToList();

推荐答案

您可以按多个字段分组，然后通过.Key属性访问这些属性:

You can group by multiple fields and then access these properties through the .Key property:

var result = (from n in db.Notes
              join p in db.Persons on n.PersonID equals p.PersonID
              group new { n.NoteText, n.LineNo } by new { n.PersonID, n.PersonName, p.DOB, p.Status } into g
              select new { 
                  PersonID = g.Key.PersonID, 
                  PersonName = g.Key.PersonName, 
                  DOB = g.Key.DOB, 
                  Status = g.Key.Status, 
                  Notes = g.OrderBy(i => i.LineNo).Select(i=> i.NoteText)
              }).AsEnumerable()

             .Select(item => new PersonNoteDTO { 
                 PersonID = item.PersonID,
                 PersonName = item.PersonName,
                 DOB = item.DOB,
                 Status = item.Status,
                 Notes = string.Join(", ", item.Notes)
             }).ToList();

如果所有这些属性确实是Person类的一部分，那么您也可以只是GroupJoin:

If all these properties are indeed part of the Person class then you can also just GroupJoin:

var result = (from p in db.Persons
              join n in db.Notes on p.PersonID equals n.PersonID into personNotes
              select new
              {
                  Person = p,
                  Notes = personNotes.OrderBy(pn => pn.LineNo).Select(pn => pn.NoteText)
              }).AsEnumerable()
             .Select(item => new PersonNoteDTO { 
                 PersonID = item.Person.PersonID,
                 PersonName = item.Person.PersonName,
                 DOB = item.Person.DOB,
                 Status = item.Person.Status,
                 Notes = string.Join(", ", item.Notes)
             }).ToList();

但是我建议您研究导航属性.然后，您甚至都不需要加入.只需一个.Include

But I suggest that you look into Navigation Properties. Then you won't even need the join. Just have an .Include

这篇关于将另一个表添加到分组的linq查询中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！