Hierarchical data and merging two data sets with LINQ

Question

I am trying to merge data from two different services calls. Data is similar in structure to this:

DTOs In Api Layer:

public static class GetData
{
    public record Country
    {
        public Guid Id { get; set; }
        public List<State> States { get; set; }
    }

    public record State
    {
        public Guid Id { get; set; }
        public string Name { get; set; }
        public List<City> Cities { get; set; }
    }

    public record City
    {
        // this Id is stored in Service B's DB as ItemId
        public Guid Id { get; set; }
        public string Name { get; set; }
        public Location Location { get; set; }
    }

    // From service B
    public record Location
    {
        public Guid Id { get; set; }
        public Guid ItemId { get; set; }
        public string Name { get; set; }
        public string Latitude { get; set; }
        public string Longitude { get; set; }
    }
}

I need to merge the Locations into the correct city and return the entire structure above in one shot. eg.

// this gets data including city (but no location) :
var country = await _serviceRequestA.GetCountryData();

// get list of Ids to make second call:
var ids = country.States.SelectMany(x => x.Cities).Select(x => x.Id).ToList();

// get list of location objects based on these ids:
var locations = await _serviceRequestB.GetLocations(ids); 

Trying to: match each City with each Location via city.Id and location.ItemId, and when found, append the location's Id, Name, Latitude, Longitude as a Location object to the City.

// do something like this on each city:
// ...but all previous data,
cities.ForEach(c =>
 {
  c.Location = locations.Where(x => x.ItemId == c.Id)
  .FirstOrDefault();
 });

But how do I take each parent object and children as-is up to and including city?

Again, my data is: country.states[n].cities[n].

I'm not that experienced with C# or LINQ other than simple wheres and selects. Do I have to do multiple loops and grab each level's props and then apply the merge logic? Is there a more succinct way in LINQ?

PS- here is a json representation of what is currently returned from the first service call, and another with the result I want after merge. I need to append the Location object to each matching city:

{
  "country": {
    "id": "3fa85f64-5717-4562-b3fc-2c963f66afa6",
    "name": "US",
    "states": [
      {
        "id": "3fa85f64-5717-4562-b3fc-2c963f66afa6",
        "name": "California",
        "cities": [
          {
            "id": "3fa85f64-5717-4562-b3fc-2c963f66afa6",
            "name": "LA"
          }
        ]
      },
      {
        "id": "3fa85f64-5717-4562-b3fc-2c963f66afa6",
        "name": "Maine",
        "cities": [
          {
            "id": "3fa85f64-5717-4562-b3fc-2c963f66afa6",
            "name": "Bangor"
          }
        ]
      }
    ]
  }
}

So the cities will then look like this when I return data to frontend:

{
  "country": {
    "id": "3fa85f64-5717-4562-b3fc-2c963f66afa6",
    "name": "US",
    "states": [
      {
        "id": "3fa85f64-5717-4562-b3fc-2c963f66afa6",
        "name": "California",
        "cities": [
          {
            "id": "3fa85f64-5717-4562-b3fc-2c963f66afa6",
            "name": "LA",
            "location": {
               "id": "3fa85f64-5717-4562-b3fc-2c963f66afa6",
               "name": "Location X",
               "latitude": "xxx",
               "longitude": "xx-xx"
              }
          }
        ]
      },
      {
        "id": "3fa85f64-5717-4562-b3fc-2c963f66afa6",
        "name": "Maine",
        "cities": [
          {
            "id": "3fa85f64-5717-4562-b3fc-2c963f66afa6",
            "name": "Bangor",
            "location": {
               "id": "3fa85f64-5717-4562-b3fc-2c963f66afa6",
               "name": "Location Y",
               "latitude": "yy",
               "longitude": "yy-yy"
              }
          }
        ]
      }
    ]
  }
}

Answer 1

From your question and comment, I think this is what you want.

cities.ForEach(c =>
            {
                c.Location = locations.Where(x => x.ItemId == c.Id).FirstOrDefault();
            });

Or if you do not want to include Itemid's value , you can also use this code:

cities.ForEach(c =>
            {
                c.Location = locations.Where(x => x.ItemId == c.Id).Select(x => new LocationA(){Id= x.Id,Name= x.Name,Latitude=x.Latitude,Longitude=x.Longitude}).FirstOrDefault();
            });

Update:

If you wanna access cities from country, just need to use Foreach(xxx) twice. Please note that if you want to output json without itemid property instead of the value of itemid is null, you need to change your code like:

public class City
    {
        // this Id is stored in Service B's DB as ItemId
        public Guid Id { get; set; }
        public string Name { get; set; }
        public Object Location { get; set; }
    }

Then:

country.States.ForEach(x =>
            {
                x.Cities.ForEach(y =>
                {
                    y.Location = locationAs.Where(z => z.ItemId == y.Id).Select(d=> new {Id=d.Id,Name = d.Name, Latitude = d.Latitude, Longitude = d.Longitude }).FirstOrDefault();
                });
            });

Output:

You can see even the property not map a data, It will just show null.