Flatten a query of multiple records to one object

Question

I have a linq query that gets some records from a database using Entity Framework Core, like this:

var result = (from users in _context.Users
              on ratings in _context.Ratings on ratings.UserId = users.Id
    select new
    {
        FirstName = users.FirstName,
        LastName = users.LastName,
        Rating = ratings.Rating
    }
    ).ToList();

This give me something like:

John   Doe   7
John   Doe   4
John   Doe   8
John   Doe   9
John   Doe   7

But I would like it to be in one record, like this:

John   Doe   7   4   8   9   7

The result can be an anonymous type that is returned in Json through a web api.

Is this possible using the above query or do I have to do some processing afterwards using a foreach loop?

You need to use Group By to combine records. I would replace the ToList() with .GroupBy(x => new {FirstName = x.FirstName, LastName = x.LastName}).Select(x => new { FirstName = x.Key.FirstName, LastName = x.Key.LastName, Ratings = x.Select(y => y.Rating).ToList()}).ToList(); — jdweng, Sep 02 '19 at 14:02

score 0 · Answer 1 · edited Sep 02 '19 at 14:33

0

You can use the function syntax of LINQ to achieve this as the User object should have a navigational property to an ICollection<Rating>.

var users = _context.Users
    .Include(x => x.Ratings)
    .Select(x => new { 
        FirstName = x.FirstName,
        LastName = x.LastName,
        Ratings = x.Ratings
    }
);

edited Sep 02 '19 at 14:33

Andrei Dragotoniu

6,155
3
18
32

answered Sep 02 '19 at 14:03

Kieran Devlin

1,373
1
12
28

Do not forget to add this namespace: ```using System.Data.Entity``` – harmath Sep 02 '19 at 14:38

Flatten a query of multiple records to one object

1 Answers1