How to Select All with a One to Many Relationship Using Linq

Question

I have two tables:

CREATE TABLE Thing (
    Id int,
    Name nvarchar(max)
);

CREATE TABLE SubThing (
        Id int,
        Name nvarchar(max),
        ThingId int (foreign key)
    );

I want to select all Things with a listing of SubThings and set them to a ThingViewModel.

The Thing ViewModel is simple:

public class ThingViewModel
{
    public int Id { get; set; }
    public string Name { get; set; }
    public List<SubThingViewModel> SubThings { get; set; }
}

The SubThingViewModel is:

public class SubThingViewModel
    {
        public int Id { get; set; }
        public string Name { get; set; }
    }

I already select the Thing records like this:

List<ThingViewModel> things = null;
things = _context.Things.OrderBy(b => b.Name)
    .Select(b => new ThingViewModel
    {
         Id = b.Id,
         Name = b.Name
    }).ToList();

How would I add the SubThings to the query and ViewModel?

Solution

You could do another projection using SubThings navigation property:

things = _context.Things.OrderBy(b => b.Name)
.Select(b => new ThingViewModel
{
     Id = b.Id,
     Name = b.Name,
     SubThings =b.SubThings.Select(st=>new SubThingViewModel{Id =st.Id,...}).ToList()
}).ToList();

Comments

[crackedcornjimmy]

I'll add the SubThings ViewModel to the post to make it more clear. Would I also need a .ToList() at the end of this line: SubThings =b.SunThings.Select(st=>new SubThingViewModel{Id =st.Id,...})

[ocuenca]

Right, SubThings in the root ViewModel class is a List, thanks ;)

[crackedcornjimmy]

No worries. I just want to make this a useful post 10 years from now.