Get a specific info from an other table with Linq

Question

My database is created using code first. Here is the model for my client.

    public int Id { get; set; }

    [Required]
    public string FirstName { get; set; }

    [Display(Name = "Phone number")]
    public int PhoneNumber { get; set; }

    [Display(Name = "Email adress")]
    [EmailAddress]
    public string EmailAdress { get; set; }

    [Display(Name = "Date of the wedding")]
    public DateTime DateOfTheWedding { get; set; }

    [Display(Name = "Type of client")]
    public TypeOfClient TypeOfClient { get; set; }

    [NotMapped]
    public int DaySinceLastVisit { get; set; }

I also have a model Visit :

    public int Id { get; set; }

    public Client Client { get; set; }

    [Required]
    [ForeignKey("Client")]
    public int ClientId { get; set; }
    [Display(Name = "Date of the visit")]
    public DateTime DateOfTheVisit { get; set; }
    public String Comment { get; set; }

Each client have a multiple visit. How can I get the last visit and add it to my model ?

Here is my resquest linq that I want to improve.

        var clientsQueryable = _context.Clients
            .Include(c => c.TypeOfClient);

        if (!String.IsNullOrWhiteSpace(query))
            clientsQueryable.Where(client => client.FirstName.Contains(query));

        var listClients = clientsQueryable
                .ToList()
                .Select(Mapper.Map<Client, ClientDto>);

      return Ok(listClients);

I want to have in Client the info DaySinceLastVisit. I presume that we need to get the last visit and make a calculation to get the days that have passed since this visit ?

Answer 1

First of all:

If a Client has multiple Visits, it would be good to add a

public virtual ICollection<Visit> Visits { get; set; }

To your Client entity, so you can easily access all the Visits linked to a Client.

Then it's easy to calculate the "DaySinceLastVisit" using similar code like this:

public int DaySinceLastVisit => (DateTime.Now - Visits.OrderByDescending(v => v.DateOfTheVisit).FirstOrDefault().DateOfTheVisit).Days;