c# - Try to get total price with id from 2 tables with Linq

Question

I am currently struggling to create a linq statement in order to calculate the total price.

I have 2 tables.

BookingSet Table :

BookingSet Table

PassengerSet Table

PassengerSet Table

The goal is indeed to create a linq statement which will get the sum of ticket price for one PersonID according to one FlightNo

So basically, for the flight number 1, I would like to retrieve 19500

I hope someone can help me !

What I have tried :

(I'm terribly sorry If you think that's bad...)

[HttpGet("{id}/sales")]
public async Task<ActionResult<double>> GetFlightTotalSales(int id)
{

double price = 0.0;
var bookingset = _context.BookingSet;
var passengerset = await _context.PassengerSet.ToListAsync();

           
var passengerIdFlight = bookingset
                       .Where(x => x.FlightNo == id)
                       .Select(x => x.PassengerID);
                
price = passengerset.Where(x => ( x.PersonID == passengerIdFlight).Select(x=>x.TicketPrice);

return price;
}

EDIT : After the answers I got, I came up this :

[HttpGet("{id}/sales")]
        public ActionResult<double> GetFlightTotalSales(int id)
        {
            var price2 = 0.0;
            var results = (from b in _context.BookingSet
                           join p in _context.PassengerSet on b.PassengerID equals p.PersonID
                           select new { b = b, p = p }
                             ).GroupBy(x => new { passengerID = x.b.PassengerID, flight = id })
                             .Select(x => new
                             {
                                 flight = x.Key.flight,
                                 firstName = x.First().p.Firstname,
                                 lastName = x.First().p.Lastname,
                                 totalPrice =(double)x.Sum(y => y.p.TicketPrice)
                             });
            

            foreach(var price in results)
            {
                price2 = price2 + price.totalPrice;
                
            }


            return price2;

My remaining problem is that I still need a way to calculate a sum according to the FlightNo

EDIT 2 : I finally found a solution ! I was really close to the solution. Thanks to all of you guys :)

Here is the solution (if it can help someone in the future) :

[HttpGet("{id}/TotalSalePrice")]
        public ActionResult<double> GetFlightTotalSales(int id)
        {
            var price2 = 0.0;
            var results = (from b in _context.BookingSet
                           join p in _context.PassengerSet on b.PassengerID equals p.PersonID
                           select new { b = b, p = p }
                             ).GroupBy(x => new { passengerID = x.b.PassengerID, flight = x.b.FlightNo })
                            .Where(z=>z.Key.flight == id)
                             .Select(x =>(double)x.Sum(y => y.p.TicketPrice)
                             );
            

            foreach(var price in results)
            {
                price2 = price2 + price;   
            }

            return price2;
        }

Answer 1

Try following :

using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            Context _context = new Context()
            {
                BookingSet = new List<BookingSet>() {
                    new BookingSet() { FlightNo = 1, PassengerID = 32},
                    new BookingSet() { FlightNo = 1, PassengerID = 33},
                    new BookingSet() { FlightNo = 1, PassengerID = 34},
                    new BookingSet() { FlightNo = 1, PassengerID = 35},
                    new BookingSet() { FlightNo = 1, PassengerID = 36},
                    new BookingSet() { FlightNo = 1, PassengerID = 37},
                    new BookingSet() { FlightNo = 1, PassengerID = 38},
                    new BookingSet() { FlightNo = 1, PassengerID = 39},
                    new BookingSet() { FlightNo = 1, PassengerID = 40},
                    new BookingSet() { FlightNo = 1, PassengerID = 41},
                    new BookingSet() { FlightNo = 2, PassengerID = 42},
                    new BookingSet() { FlightNo = 2, PassengerID = 43},
                    new BookingSet() { FlightNo = 6, PassengerID = 44},
                },
                PassengerSet = new List<PassengerSet>() {
                    new PassengerSet() { PersonID = 32, Firstname = "Tony", Lastname = "Montana", IsBusiness = 1, TicketPrice = 1750.00M},
                    new PassengerSet() { PersonID = 33, Firstname = "Al", Lastname = "Capone", IsBusiness = 1, TicketPrice = 1750.00M},
                    new PassengerSet() { PersonID = 34, Firstname = "Nucky", Lastname = "Thompson", IsBusiness = 1, TicketPrice = 1750.00M},
                    new PassengerSet() { PersonID = 35, Firstname = "Sam", Lastname = "Giancana", IsBusiness = 1, TicketPrice = 1750.00M},
                    new PassengerSet() { PersonID = 36, Firstname = "Bugsy", Lastname = "Siegel", IsBusiness = 1, TicketPrice = 1750.00M},
                    new PassengerSet() { PersonID = 37, Firstname = "Vito", Lastname = "Genovese", IsBusiness = 1, TicketPrice = 1750.00M},
                    new PassengerSet() { PersonID = 38, Firstname = "Meyer", Lastname = "Lansky", IsBusiness = 1, TicketPrice = 1750.00M},
                    new PassengerSet() { PersonID = 39, Firstname = "John", Lastname = "Dillinger", IsBusiness = 1, TicketPrice = 1750.00M},
                    new PassengerSet() { PersonID = 30, Firstname = "Luciano", Lastname = "Lucky", IsBusiness = 1, TicketPrice = 1750.00M},
                    new PassengerSet() { PersonID = 41, Firstname = "Mickey", Lastname = "Cohen", IsBusiness = 1, TicketPrice = 1750.00M},
                    new PassengerSet() { PersonID = 42, Firstname = "Anakin", Lastname = "Skywalker", IsBusiness = 1, TicketPrice = 315.00M},
                    new PassengerSet() { PersonID = 43, Firstname = "Obi-Wan", Lastname = "Kenobi", IsBusiness = 1, TicketPrice = 315.00M},
                    new PassengerSet() { PersonID = 44, Firstname = "Ben", Lastname = "Solo", IsBusiness = 0, TicketPrice = 840.00M}
                }
            };

            var results = (from b in _context.BookingSet
                           join p in _context.PassengerSet on b.PassengerID equals p.PersonID
                           select new { b = b, p = p }
                           ).GroupBy(x => new { passengerID = x.b.PassengerID, flight = x.b.FlightNo })
                           .Select(x => new
                           {
                               flight = x.Key.flight,
                               firstName = x.First().p.Firstname,
                               lastName = x.First().p.Lastname,
                               totalPrice = x.Sum(y => y.p.TicketPrice)
                           }).ToList();

        }
    }
    public class Context
    {
        public List<BookingSet> BookingSet { get; set; }
        public List<PassengerSet> PassengerSet { get; set; }
    }
    public class BookingSet
    {
        public int FlightNo { get; set; }
        public int PassengerID { get; set; }
    }
    public class PassengerSet
    {
        public int PersonID { get; set; }
        public string Firstname { get; set; }
        public string Lastname { get; set; }
        public int IsBusiness { get; set; }
        public decimal TicketPrice { get; set; }
    }
}