Unable to get lastest answers of a survey grouped by session id with Linq C#

Question

I have a survey where the answers are grouped by a Session GUID and a timestamp of the answer:

public class Answer
{
    [Key]
    public int Id { get; set; }
    public DateTime DateCreated { get; set; }
    public string Session { get; set; }
    public int Numeric { get; set; }
    public string Text { get; set; }
}

If the answer is type Numeric (from 1 to 5 stars) the numeric is filled, if the answer is type Text (like a comment), the text is filled.

Example of records:

    48  2016-07-15 11:20:14.823 12f68234-fee0-4a3a-88ef-f3977824ed51    5   NULL
    49  2016-07-15 11:20:19.550 12f68234-fee0-4a3a-88ef-f3977824ed51    2   NULL
    50  2016-07-15 11:20:19.553 12f68234-fee0-4a3a-88ef-f3977824ed51    4   NULL
    51  2016-07-15 11:20:19.557 12f68234-fee0-4a3a-88ef-f3977824ed51    3   NULL
    52  2016-07-15 11:20:19.560 12f68234-fee0-4a3a-88ef-f3977824ed51    0   gostei bastante!
    53  2016-07-15 11:59:59.000 a143125e-0463-13f9-fc83-48d660c96156    4   NULL
    54  2016-07-15 12:00:26.277 a143125e-0463-13f9-fc83-48d660c96156    4   NULL
    55  2016-07-15 12:00:26.277 a143125e-0463-13f9-fc83-48d660c96156    3   NULL
    56  2016-07-15 12:00:26.277 a143125e-0463-13f9-fc83-48d660c96156    4   NULL
    57  2016-07-15 12:00:26.297 a143125e-0463-13f9-fc83-48d660c96156    0   Acho que há algumas coisas para melhorar
    58  2016-07-15 17:56:00.503 610821d4-5c48-4222-8c49-c19f0dd9182c    5   NULL
    59  2016-07-15 17:56:16.617 610821d4-5c48-4222-8c49-c19f0dd9182c    5   NULL
    60  2016-07-15 17:56:16.620 610821d4-5c48-4222-8c49-c19f0dd9182c    5   NULL
    61  2016-07-15 17:56:16.617 610821d4-5c48-4222-8c49-c19f0dd9182c    4   NULL
    62  2016-07-15 17:56:16.637 610821d4-5c48-4222-8c49-c19f0dd9182c    0   Gostei bastante de todo o serviço

The problem is that I can't group by session and ordered by datecreated because they are both distinct records.

The code I have that is not working is:

var sessions = _dbContext.Answers
    .Where(p => p.Location.Company.Id == id)
    .Where(p => p.Question.Type == QuestionType.Text)
    .Where(p => p.Text != "")
    .OrderByDescending(p => p.DateCreated)
    .Select(p => p.Session)
    .Distinct()
    .Take(count);

    var dto = new List<GetLastCommentsByCountByCompanyIdDTO>();

    foreach (var session in sessions)
    {
        dto.Add(new GetLastCommentsByCountByCompanyIdDTO
        {
            LocationName = _dbContext.Answers.Where(s => s.Session == session).Select(s => s.Location.Name).FirstOrDefault(),
            DateCreated = _dbContext.Answers.Where(s => s.Session == session).Select(s => s.DateCreated).FirstOrDefault(),
            Comment = _dbContext.Answers.Where(s => s.Session == session && s.Question.Type == QuestionType.Text).Select(s => s.Text).FirstOrDefault()
        });
    }

    return dto.OrderByDescending(p => p.DateCreated);                

Answer 1

Try this one:

var baseQuery = _dbContext.Answers.AsNoTracking()
                .Where(p => p.Location.Company.Id == id
                            && p.Question.Type == QuestionType.Text
                            && p.Text != null
                            && p.Text != "")
                .GroupBy(g => new { Session = g.Session, Location = g.Location.Name })
                .Select(x =>
                            new
                            {
                                Session = x.Key.Session,
                                LocationName = x.Key.Location,
                                LastAnswer = x.OrderByDescending(f => f.DateCreated).FirstOrDefault()
                            })
                .Select(x => new GetLastCommentsByCountByCompanyIdDTO
                {
                    LocationName = x.LocationName,
                    DateCreated = x.LastAnswer.DateCreated,
                    Comment = x.LastAnswer.Text
                })
                .OrderByDescending(x => x.DateCreated)
                .Take(count);

            var res = baseQuery.ToList();