Without using Automapper, I'm trying to map a list of DTO's into each element of a list of DTO's. Specfically in this example, I want the API to return a list of parents where each parent contains it's list of children and list of pets.

I have an EF Core project with the models below (which use fully defined relationships) and exist within a larger database.

There is a Parent object, which has many Pet objects and many Child objects, like so:

EF Core Models

    public class Parent
    {
        public int ParentId { get; set; }
        public string Name { get; set; }

        public List<Child> Children { get; set; }
        public List<Pet> Pets { get; set; }
    }

    public class Child
    {
        public int ChildId { get; set; }
        public string Name { get; set; }

        public int ParentId { get; set; }
        public Parent Parent { get; set; }
    }

    public class Pet
    {
        public int PetId { get; set; }
        public string Name { get; set; }

        public int ParentId { get; set; }
        public Parent Parent { get; set; }
    }

Without using Automapper I'm trying to use DTOs in a controller to send the data out.

I've setup the DTOs as follows:

DTOs

    public class ParentDTO
    {
        public int ParentId { get; set; }
        public string Name { get; set; }

        //Should these be List<> or IEnumerable<>?
        public IEnumerable<ChildDTO> Children { get; set; }
        public IEnumerable<PetDTO> Pets { get; set; }
    }

    public class ChildDTO
    {
        public int ChildId { get; set; }
        public string Name { get; set; }
    }

    public class PetDTO
    {
        public int PetId { get; set; }
        public string Name { get; set; }
    }

Currently my controller looks like this:

Controller Attempt 1

        // GET: api/Parents/
        [HttpGet]
        public IEnumerable<ParentDTO> GetParents()
        {

            var parents = from pa in _context.Parents
                          select new ParentDTO()
                          {
                              Name = pa.Name
                          };

            return parents;
        }

        // GET: api/Parents/GetParentsWith
        [HttpGet("[action]")]
        public IEnumerable<ParentDTO> GetParentsWith()
        {

            var parents = from pa in _context.Parents
                          select new ParentDTO()
                          {
                              Name = pa.Name,
                              //Not sure how to correctly do this below...
                              Children = (from c in _context.Children.Where(c => c.ParentId == pa.ParentId)
                                          select new ChildDTO()
                                          {
                                              Name = c.Name
                                          }),
                              Pets = (from pe in _context.Pets.Where(pet => pet.ParentId == pa.ParentId)
                                      select new PetDTO()
                                      {
                                          Name = pe.Name
                                      })
                          };

            return parents;
        }

My GET: api/Parents/ works just fine (because I'm not trying to make the nested list), but how do I add the related child and pet items to the parents DTO like I'm trying to do in GET: api/Parents/GetParentsWith?

Trying this gives the error:

Microsoft.Data.SqlClient.SqlException (0x80131904): Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
....
...

I've also tried:

Controller Attempt 2

        // GET: api/Parents/GetParentsWith
        [HttpGet("[action]")]
        public IEnumerable<ParentDTO> GetParentsWith()
        {

            var parents = from pa in _context.Parents
                          select new ParentDTO()
                          {
                              Name = pa.Name,
                              Children = (from c in pa.Children
                                          select new ChildDTO()
                                          {
                                              Name = c.Name
                                          }),
                              Pets = (from pe in pa.Pets
                                      select new PetDTO()
                                      {
                                          Name = pe.Name
                                      })
                          };

            return parents;
        }

This also gives the error:

Microsoft.Data.SqlClient.SqlException (0x80131904): Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
....
...

I've also tried:

Controller Attempt 3

        // GET: api/Parents/GetParentsWith
        [HttpGet("[action]")]
        public IEnumerable<ParentDTO> GetParentsWith()
        {

            var parents = from pa in _context.Parents
                          select new ParentDTO()
                          {
                              ParentId = pa.ParentId,
                              Name = pa.Name
                          };

            foreach (ParentDTO parent in parents)
            {
                var children = from child in _context.Children where child.ParentId == parent.ParentId
                               select new ChildDTO()
                               {
                                   ChildId = child.ChildId,
                                   Name = child.Name
                               };

                var pets = from pet in _context.Pets
                           where pet.ParentId == parent.ParentId
                           select new PetDTO()
                           {
                               PetId = pet.PetId,
                               Name = pet.Name
                           };

                parent.Children = children;
                parent.Pets = pets;

            return parents;
        }

Which doesn't throw any errors so maybe I'm on the right track here, unfortunately it just returns null where I would expect an array of children or pets:

[
    {
        "parentId": 1,
        "name": "Ronald",
        "children": null,
        "pets": null
    },
    {
        "parentId": 2,
        "name": "Benjamin",
        "children": null,
        "pets": null
    },
    {
        "parentId": 3,
        "name": "Sally",
        "children": null,
        "pets": null
    },
    {
        "parentId": 4,
        "name": "Foobar",
        "children": null,
        "pets": null
    }
]

Solved! But did I do it right?

I noticed parents was an IQueryable, and so the assignment within the foreach was not working as I expected... I think I've got a bit to learn about IQueryable, IEnumerable etc. etc.

        // GET: api/Parents/GetParentsWith
        [HttpGet("[action]")]
        public ask<ActionResult<IEnumerable<ParentDTO>>> GetParentsWith()
        {

            var parents = (from pa in _context.Parents
                          select new ParentDTO()
                          {
                              ParentId = pa.ParentId,
                              Name = pa.Name
                          }).ToListAsync();

            foreach (ParentDTO parent in await parents)
            {
                var children = from child in _context.Children where child.ParentId == parent.ParentId
                               select new ChildDTO()
                               {
                                   ChildId = child.ChildId,
                                   Name = child.Name
                               };

                var pets = from pet in _context.Pets
                           where pet.ParentId == parent.ParentId
                           select new PetDTO()
                           {
                               PetId = pet.PetId,
                               Name = pet.Name
                           };

                parent.Children = children;
                parent.Pets = pets;
            }

            return parents;
        }