public static string SelectAllPlanetsAndTheirJourneyCount(ColonialJourneyContext context) { StringBuilder sb = new StringBuilder(); var planets = context .Planets .Select(p => new { p.Name, JourneysCount = p.Spaceports.Select(s => s.Journeys.Count).Count() }) .OrderByDescending(p => p.JourneysCount) .ThenBy(p => p.Name) .ToList(); foreach (var planet in planets) { sb.Append($"Planet Name: {planet.Name}, Journeys Count: {planet.JourneysCount}" + Environment.NewLine); } return(sb.ToString().TrimEnd()); }
public static string SelectAllMililitaryJourneys(ColonialJourneyContext context) { StringBuilder sb = new StringBuilder(); var militaryJourneys = context .Journeys .Where(j => j.Purpose == "Military") .OrderBy(j => j.JourneyStart) .Select(j => new { Id = j.Id, JourneyStart = j.JourneyStart.ToString("dd-MM-yyyy", CultureInfo.InvariantCulture), JourneyEnd = j.JourneyEnd.ToString("dd-MM-yyyy", CultureInfo.InvariantCulture) }) .ToList(); foreach (var j in militaryJourneys) { sb.Append($"Id: {j.Id}, JourneyStart: {j.JourneyStart}, JourneyEnd: {j.JourneyEnd}" + Environment.NewLine); } return(sb.ToString().TrimEnd()); }
public static string SelectAllPilots(ColonialJourneyContext context) { StringBuilder sb = new StringBuilder(); var pilots = context .TravelCards .Where(tc => tc.JobDuringJourney == "Pilot") .OrderBy(tc => tc.Colonist.Id) .Select(tc => new { Id = tc.Colonist.Id, JobTitle = tc.JobDuringJourney, FullName = tc.Colonist.FirstName + " " + tc.Colonist.LastName }) .ToList(); foreach (var pilot in pilots) { sb.Append($"Id: {pilot.Id}, Job: {pilot.JobTitle}, Full Name: {pilot.FullName}" + Environment.NewLine); } return(sb.ToString().TrimEnd()); }
public static string SelectSpaceshipsWithPilotsYoungerThan30Years(ColonialJourneyContext context) { StringBuilder sb = new StringBuilder(); var spaceships = context .Spaceships .Where(s => s.Journeys .Any(j => j.TravelCards.Any(tc => tc.JobDuringJourney == "Pilot") && j.TravelCards .Any(tc => DateTime.Parse("01/01/2019", CultureInfo.InvariantCulture).Year - tc.Colonist.BirthDate.Year < 30))) .Select(s => new { s.Name, s.Manufacturer }) .ToList(); foreach (var spaceship in spaceships) { sb.Append($"Name: {spaceship.Name}, Manufacturer: {spaceship.Manufacturer}" + Environment.NewLine); } return(sb.ToString().TrimEnd()); }
static void Main(string[] args) { var db = new ColonialJourneyContext(); Console.WriteLine(SelectAllPlanetsAndTheirJourneyCount(db)); }