ElenaDBA ElenaDBA - 1 month ago 6
C# Question

Grouping in Linq queries with multiple tables

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 'ObjectContent
1' 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

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