I am writing a C# .NET 5 application that will act as a backend for an Angular frontend, providing CRUD APIs. The purpose of the app is managing a flight school.

I am writing the API methods that will return the list of pilots and the single pilot, that is https://myapp/api/pilots and https://myapp/api/pilots/{id}.

I have three methods here:

1. GetPilots() for the complete list
2. GetPilot(long idPilot) for the detail
3. an auxiliary method GetFlightTime(long idPilot) to return the total flight time of each pilot doing the sum of the flight durations.

My problem: the detail method works because I first call the auxiliary function and then I use the result in the returned viewmodel. But GetPilots() doesn't work and returns the error System.InvalidOperationException: The client projection contains a reference to a constant expression of 'QTBWeb.Models.PilotsRepository' through the instance method 'GetFlightTime'. This could potentially cause a memory leak; consider making the method static so that it does not capture constant in the instance.

Is this because I am calling the GetFlightTime method inside the LINQ expression? I don't understand the make the method static suggestion. How can I reformat the code to make it work?

Thanks!

   public IEnumerable<PilotViewModel> GetPilots()  // THIS METHOD RETURNS ERROR
    {
        return _context.Pilots
            .Select(pilot => new PilotViewModel
            {
                Id = pilot.Id,
                Name = pilot.Name,
                FlightMinutes = GetFlightTime(pilot.Id)  // THE PROBLEM IS HERE
            })
            .ToList();
    }

    public PilotViewModel GetPilot(long idPilot)  // THIS METHOD WORKS
    {
        var _flightMinutes = GetFlightTime(idPilot);

        return _context.Pilots
            .Select(pilot => new PilotViewModel
            {
                Id = pilot.Id,
                Name = pilot.Name,
                FlightMinutes = _flightMinutes
            })
            .Where(pilot => pilot.Id == idPilot)
            .FirstOrDefault();
    }

    public int GetFlightTime(long idPilot)
    {
        return _context.Flights
            .Where(flight => flight.pilot == idPilot)
            .Select(flight => flight.Duration).Sum();  
    }

CodePudding user response：

A good way to solve this would be to make sure that your Pilot class has a collection of Flights, serving as the other side of the one-to-many map you have as Flight.Pilot. You can then use this collection to calculate the sum, without having to query the database for every looped instance of Pilot.

Your code would look something like this:

public IEnumerable<PilotViewModel> GetPilots()
    {
        return _context.Pilots
            .Include(pilot => pilot.Flights) // Include Flights to join data
            .Select(pilot => new PilotViewModel
            {
                Id = pilot.Id,
                Name = pilot.Name,
                FlightMinutes = pilot.Flights.Sum(flight => flight.Duration)
            });
    }

    public PilotViewModel GetPilot(long idPilot)
    {
        return _context.Pilots
            .Include(pilot => pilot.Flights) // Include Flights to join data
            .Where(pilot => pilot.Id == idPilot) // Notice how we filter first
            .Select(pilot => new PilotViewModel
            {
                Id = pilot.Id,
                Name = pilot.Name,
                FlightMinutes = pilot.Flights.Sum(flight => flight.Duration)
            })
            .FirstOrDefault();
    }