Entity Framework Core, retrieve data from second level related table with aggregation function-CodePudding

I have a situation that I'd like to try that doesn't let me sleep at night. in essence, I would like to display cards (bootstrap type) in a View which, for each person (it's just an example for analogy), show the associated characteristics and the value of the last measurement (for example, imagining that the height was measured at 5 years and then at 10 years ). In a situation similar to that of the image below, it is a best practice to adopt the only logic that I was able to implement (sorry but I am a hobbyist who loves programming, unfortunately I have not studied for that) reported in the code below or Is it better to use Partial View? In the second case, how are they implemented for the second level or better, how is the value of the last date associated with each characteristic?

What I would get:

And this is my approach:

VIEW MODEL

public class ViewModel
{
   public IEnumerable<PersonViewModel>? People { get; set; }
   public IEnumerable<FeaturesViewModel>? Features { get; set; }
}

public class PersonViewModel
{
   public int Id { get; set; }
   public string Name{ get; set; } = string.Empty;
   public string? Surname { get; set; }
   public ICollection<FeaturesViewModel>? FeaturesViewModel{ get; set; }
}

public class FeaturesViewModel
{
   public virtual PersonViewModel? People { get; set; }
   public int Id { get; set; }
   public string? Feature{ get; set; } = string.Empty;
   public DateTime? DateLastValue { get; set; } = default;
   public String? StringLastValue { get; set; } = string.Empty;
}

CONTROLLER

var myPeople = _context.Person!.Select(x => new PersonViewModel
{
   Id = (int)x.Id!,
   Name = x.Name,
   Surname = x.Surname
});

var myFeatures = _context.Features!.Select(x => new FeaturesViewModel
{
   Id = x.Id,
   PersonId = (int)x.PersonId!,
   Feature = x.Feature,
   DateLastValue = x.Features.Feature-Value.Max(x => x.DateTimeMeasure), 
   //StringLastValue = ????                 
 });

ViewModel VM = new();
VM.People = myPeople;
VM.Features = myFeatures;

return View(VM);

VIEW

@model VM

<div >
    <div >
       <h3>PEOPLE: </h3>
     </div>
</div>
<br />

<div >

    @foreach (var item in Model.People!)
    {
       @*<img [email protected]  alt="...">*@
            <div >
                <h5 >@item.Surname.ToUpper()</h5>
                <h6 >@Html.DisplayFor(modelItem => item.Name)</h6>
       <hr />
          <p >
             FEATURES:
          </p>
       <hr/>
          <p >

             @foreach (var feat in Model.Features!.Where(x=>x.PersonId == item.Id))
             {
                 <div >
                    <div >
                       @feat.Feature - @feat.StringFeatureValue ??
                    </div>
                 </div>
             } //end of Features 
         </p>
      } //end of foreach People
</div>

CodePudding user response：

You can try this way:

Your view models

public class ViewModel
{
   //the only one list you need in the view
   public IEnumerable<PersonViewModel> People { get; set; }
}

public class PersonViewModel
{
   public int Id { get; set; }
   public string Name{ get; set; }
   public string Surname { get; set; }
   // All persons features 
   public ICollection<FeaturesViewModel> Features { get; set; }
}

public class FeaturesViewModel
{
   public int Id { get; set; }
   public string Feature{ get; set; }
   // last feature value by datetime
   public FeatureValueViewModel LastFeatureValue { get; set; }
}

public class FeatureValueViewModel
{
   public DateTime DateTimeMeassure { get; set; }
   public string MeasureValue { get; set; }
}

Controller

//If you have the right relations, You can take all the data you need that way
var people = _context.Person.Select(x => new PersonViewModel
{
   Id = x.Id,
   Name = x.Name,
   Surname = x.Surname
   Features = x.Features.Select(y => new FeaturesViewModel
   {
       Id = y.Id,
       Feature = y.Feature,
       LastFeatureValue = y.FeatureValues.OrderByDescending(x => x.DateTimeMeashure).FirstOrDefault(),
   }).ToList();

}).ToList();


ViewModel VM = new();
VM.People = myPeople;

return View(VM);

View

@model VM

<div >
    <div >
       <h3>PEOPLE: </h3>
     </div>
</div>
<br />

<div >

    @foreach (var person in Model.People)
    {
       @*<img [email protected]  alt="...">*@
            <div >
                <h5 >@item.Surname.ToUpper()</h5>
                <h6 >@Html.DisplayFor(modelItem => item.Name)</h6>
       <hr />
          <p >
             FEATURES:
          </p>
       <hr/>
          <p >
             //
             @foreach (var feat in person.Features )
             {
                 <div >
                    <div >
                       @feat.LastFeatureValue.DateTimeMeassure - @feat.LastFeatureValue.MeasureValue
                    </div>
                 </div>
             } //end of Features 
         </p>
      } //end of foreach People
</div>