Returning many-to-many results with LINQ in C# asp.net

Question

I'm struggling to understand LINQ syntax and how to use it with many-to-many relationships in a database. In my example project I'm modeling cars and maintenance you can do to a car. So I have 3 tables:

1. Cars (information about a car that a user adds)
2. Services (reference info only)
3. CarServices (stores what services have been done on a car)

On the website, I'm displaying info from all 3 of these tables so I've created a view model, CarsIndexViewModel, to hold a Car and its associated CarServices.

public class Car
{
    public int Id { get; set; }

    [Required]
    [StringLength(40)]
    public string Name { get; set; }

    [Required]
    [Range(1900, 2018)]
    public int Year { get; set; }
    public string Make { get; set; }
    public string Model { get; set; }
    public string Color { get; set; }
    public int Mileage { get; set; }

    public virtual ICollection<CarService> CarServices { get; set; }
}

public class Service
{
    public int Id { get; set; }
    public string Name { get; set; }

    public virtual ICollection<CarService> CarServices { get; set; }

}

public class CarService
{
    public int Id { get; set; }

    public int CarId { get; set; }

    [Required]
    [Display(Name = "Service")]
    public int ServiceId { get; set; }

    [Required]
    [DataType(DataType.Date)]
    [DisplayFormat(DataFormatString = "{0:MM/dd/yyyy}")]
    [Display(Name = "Date of Service")]
    public DateTime ServiceDate { get; set; }

    [Column(TypeName = "Money")]
    public decimal? Cost { get; set; }

    public bool HasReceipt { get; set; }

    [Required]
    [Display(Name = "Mileage At Time of Service")]
    public int Mileage { get; set; }

    public virtual Car Car { get; set; }
    public virtual Service Service { get; set; }
}

and the view model:

public class CarsIndexViewModel
{
    public Car Car { get; set; }
    public IEnumerable<CarService> Services { get; set; }
}

I've been able to return all of the services and their information by using this code in my controller:

var query = (
            from car in _context.Cars
            select new CarsIndexViewModel()
            {
                Car = car,
                Services = car.CarServices
            }).ToList();

return View(query);

What I cannot figure out is how to only return CarServices that meet certain criteria. For example, what if I only want to show CarServices from the most recent date?

Answer 1

There are many ways of doing this. A couple possibilities to get what you are talking about (show CarServices from the most recent date) is this:

Last n recent services:

var n = 4;
var query = (
        from car in _context.Cars
        select new CarsIndexViewModel()
        {
            Car = car,
            AllServices = car.CarServices,
            RecentServices = car.CarServices.OrderBy(x => ServiceDate).Take(n)
        }).ToList();

Recent services after a given date:

var recentThreshold = new DateTime(2017,01,01);
var query = (
        from car in _context.Cars
        select new CarsIndexViewModel()
        {
            Car = car,
            AllServices = car.CarServices,
            RecentServices = car.CarServices.Where(x => ServiceDate > recentThreshhold)
        }).ToList();

Recent services that all happened on the last day that any service was given:

var latestServiceDates = (
        from car in _context.Cars
        select new KeyValuePair<int, DateTime>()
        {
            Key = car.id,
            Value = car.CarServices.OrderByDescending(x => ServiceDate).First()
        }).ToList();
var query = (
        from car in _context.Cars
        select new CarsIndexViewModel()
        {
            Car = car,
            AllServices = car.CarServices,
            RecentServices = car.CarServices.Where(x => ServiceDate = latestServiceDates.First(y => y.Key == x.id))
        }).ToList();

It just depends what you are trying to do exactly.

Answer 2

If you want to show most recent services:

var query = (from car in _context.Cars
             select new CarsIndexViewModel()
             {
                 Car = car,
                 Services = car.CarServices
                               .OrderByDescending(cs => cs.ServiceDate)
                               .Take(5) // only 5 most recent
                               .ToList()
             }).ToList();