Get data from nested tables-CodePudding

I'm trying to get a nested table using a LINQ command but I cannot manage to return the desired output.

Given the IdTrip I need to get the trip data and as arrays, the clients/countries data too.

I have tried to utilize the below command and I managed to get the countries ( even if I should only show the name ) but I cannot return the client objects as expected, only the ids from the table Client_Trips.

IEnumerable<object> countries = await _db.Trips
    .Include(t => t.IdCountries)
    .Include(d => d.ClientTrips)
    .Select(c => new
    {
        Name = c.Name,
        Description = c.Description,
        DateFrom = c.DateFrom,
        DatTo = c.DateTo,
        MaxPeople = c.MaxPeople,
        Countries = c.IdCountries,
        Clients = c.ClientTrips
    }).ToListAsync();

This is what I would like to receive:

[
    {
        "name": "XXXX",
        "description": "XXXXXX",
        "dateFrom": "2023-01-12T00:00:00",
        "datTo": "2023-01-17T00:00:00",
        "maxPeople": 10,
        "countries": [
            {
                "name": "XXXX"
            },
            {
                "name": "XXXX"
            }
        ],
        "clients": [
            {
                FirstName: "name",
                LastName: "lastname"
            },
                {
                FirstName: "name",
                LastName: "lastname"
            }
        ]
    }   
]

CodePudding user response：

If you have proper navigation properties, query should looks like this:

var countries = await _db.Trips
    .Select(c => new
    {
        Name = c.Name,
        Description = c.Description,
        DateFrom = c.DateFrom,
        DatTo = c.DateTo,
        MaxPeople = c.MaxPeople,
        Countries = c.CountryTrips.Select(x => new { x.Country.Name }).ToArray(),
        Clients = c.ClientTrips.Select(x => new { x.Client.FirstName, x.Client.Lastname }).ToArray()
    }).ToListAsync();

CodePudding user response：

Just for completeness, I add below the two solutions:

Efficient way using only one LINQ query

var countries = await _db.Trips

  .Select(c => new
     {
         Name = c.Name,
         Description = c.Description,
         DateFrom = c.DateFrom,
         DatTo = c.DateTo,
         MaxPeople = c.MaxPeople,
         Countries = c.IdCountries.Select(x => new { x.Name 
      }).ToArray(),
             Clients = c.ClientTrips.Select(x => new { x.IdClientNavigation.FirstName, x.IdClientNavigation.LastName }).ToArray()
         }).ToListAsync();

Not efficient way but it did work - Run two separated queries and assigned the output to an object of a custom class 'Response'

public class TripResponse { public string Name { get; set; }

    public string? Description { get; set; }
    public DateTime DateFrom { get; set; }
    public DateTime DateTo { get; set; }
    public int MaxPeople { get; set; }
    public IEnumerable<object> Countries { get; set; }

    public IEnumerable<object> Clients { get; set; }}




            var clients = from client in _db.Clients
                      join clientTrip in _db.ClientTrips on client.IdClient equals clientTrip.IdClient
                      join trip in _db.Trips.Where(c => c.IdTrip == t.IdTrip) on clientTrip.IdTrip equals trip.IdTrip
                      select new { FirstName = client.FirstName, LastName = client.LastName };

        var countries = await _db.Trips.Where(c => c.IdTrip == t.IdTrip).Select(c => c.IdCountries.Select(c => new { Name = c.Name })).ToListAsync();
        TripResponse response = new TripResponse
        {
            Name = t.Name,
            Description = t.Description,
            DateFrom = t.DateFrom,
            DateTo = t.DateTo,
            MaxPeople = t.MaxPeople,
            Countries = countries,
            Clients = clients

        };