Grouping in Linq queries with multiple tables

Question

I have the two tables

Person
__________
PersonID
PersonName
DOB
Status


Notes
__________
NoteID
PersonID
NoteText
Comments
LineNo

here is some sample content

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

and

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

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

All the examples I found so far only have one table, so I tried to start with grouping just the Notes table as follows:

var result = (from n in db.Notes
              group n.NoteText by n.PersonID into g
              select new NoteGrp { 
                  PersonID = g.Key, 
                  Notes = g.ToList().ToString() 
              }).AsEnumerable();

in the function it is something like this:

public IEnumerable<NoteGrp> GetNotes()
{
    using (MyContext db = new MyContext())
    {               
        var result = (from n in db.Notes
                      group n.NoteText by n.PersonID into g
                     select new NoteGrp { PersonID = g.Key, Note = g.ToList().ToString() }).AsEnumerable();
        return result;
    }
}

It compiles fine and does error out until it gets to return result. If I view result object in the watch window and expand it to voew the results, I see The entity or complex type 'NoteGrp' cannot be constructed in a LINQ to Entities query inner exception. But if I let it run through then in the browser I get the following: The 'ObjectContent1' type failed to serialize the response body for content type 'text/html;`

P.S. NoteGrp is a class I created that contains only 2 members: PersonID as an int and Notes as a string

public class NoteGrp
    {
        [Key]
        public int PersonID { get; set; }
        public string Notes { get; set; }

    }

I also tried using

 var result = db.Notes.GroupBy(n => new { n.PersonID }).Select(g => new NoteGrp{PersonID= g.Key.personID, Notes = string.Join(",", g.Select(x => x.NoteText)) }).ToList();

and got the same error that the entity of complex type cannot be constructed. I could use anonymous type:

var result = db.Notes.GroupBy(n => new { n.PersonID }).Select(g => new {PersonID= g.Key.personID, Notes = string.Join(",", g.Select(x => x.NoteText)) }).ToList();

but then I am not sure what return type should my function have...

Answer 1

From what I assume (by it failing with this error but working with an anonymous type) the NoteGrp class is mapped and you can't instantiate an EF mapped object in a query. Create a DTO class (or an anonymous type Instead).

Also the use of .ToList().ToString() is probably not what you are looking for but what you want is string.Join:

public IEnumerable<NoteGrp> GetNotes()
{
    using (MyContext db = new MyContext())
    {
        var result = (from n in db.Notes
                      group n.NoteText by n.PersonID into g
                      select new { //Creating anonymous object with only the needed information
                          PersonID = g.Key, 
                          Notes = g.ToList()
                      }).AsEnumerable() //Bringing items to memory so can use string.Join
                     .Select(item => new NoteGroupDTO { //Instantiating new DTO object
                         PersonID = item.PersonID,
                         Notes = string.Join(", ", item.Notes)
                     }).ToList();
        return result;
    }
}

class:

public class NoteGroupDTO
{
    public int PersonId { get; set; }
    public string Notes { get; set; }
}

Answer 2

You can use AsEnumerable to change to Linq to Objects before the projection (Select) to avoid Linq to Entities limitations like String.Join:

var result = db.Notes.GroupBy(n => n.PersonId)
                     .AsEnumerable()
                     .Select(g => new NoteGrp
                      {
                          PersonID = g.Key,
                          Notes = string.Join(", ", g.Select(x => x.NoteText))
                      });

If you can change NoteGrp you can add another property to holds all NoteText and join them at the other property:

var result = db.Notes.GroupBy(n => n.PersonId)
                     .Select(g => new NoteGrp
                      {
                          PersonID = g.Key,
                          Notes = g.Select(x => x.NoteText)
                      });

public class NoteGrp
{
    public int PersonID { get; set; }
    public IEnumerable<string> Notes { get; set; }
    ...

    public string Note
    { get { return string.Join(", ", Notes); } }
}