Rapscallion Rapscallion - 1 month ago 20
C# Question

ASP MVC: ViewModel index using master detail linq statement

I'm new to ASP.NET and MVC so please bear with me. I have two models:

public class Change
{
public int ID { get; set; }
public string Title { get; set; }
// more fields
public virtual ICollection<ChangeHistory> ChangeHistory { get; set; }
}


and

public class ChangeHistory
{
public int ID { get; set; }
public int ChangeID { get; set; }
public DateTime Date {get; set; }
public Status Status {get; set; }
}


Currently, my Changes view simply lists all changes in a table with their details (ID, Title etc.). What I would like is to add several columns from the details table such as Inititiaton Date (Date of the first ChangeHistory item for a given Change) and Current Status & Date (both retrieved from the last ChangeHistory item for a given Change.)

I started by creating a new ViewModel:

public class ChangeViewModel
{
// Take these details from the change record
public int ID { get; set; }
public string Title { get; set; }
public string Description { get; set; }

// Take these details from the first change history record
public DateTime InitiationDate { get; set; }

// Take these details from the latest change history record
public DateTime StatusDate { get; set; }
public Status CurrentStatus { get; set; }
}


In my ChangesController I got as far as retrieving the Initiation Date before my lack of experience stopped me in my tracks:

var ChangeWithStatus = from c in db.Changes
from h in db.ChangeHistories
where c.ID == h.ChangeID && h.Date ==
(
from h2 in db.ChangeHistories
where c.ID == h2.ChangeID
select h2.Date
).Min()

select new ChangeViewModel
{
// Change details
ID = c.ID,
Title = c.Title,
Description = c.Description,

// Initiation details
InitiationDate = h.Date,
Initiator = h.User,

// Status details
StatusDate = h.Date,
CurrentStatus = h.Status,
User = h.User
};


I'm wondering if the solution is to retrieve each ChangeHistory record with its own linq statement and then combine them with a third. Can anyone help me with this problem? Thanks in advance.

EDIT: Solution - thanks to SomeUser

var changeViewModels = (from change in db.Changes
let lastChangeHistory = change.ChangeHistory.OrderByDescending(changeHistory => changeHistory.Date).FirstOrDefault()
let firstChangeHistory = change.ChangeHistory.OrderBy(changeHistory => changeHistory.Date).FirstOrDefault()
select new ChangeViewModel
{
ID = change.ID,
Title = change.Title,
Description = change.Description,
InitiationDate = firstChangeHistory.Date,
StatusDate = lastChangeHistory.Date,
CurrentStatus = lastChangeHistory.Status,
}).ToList();

Answer

This should work:

var changeViewModels = (from change in db.Changes
                        let lastChangeHistory = change.ChangeHistory.OrderByDescending(changeHistory => changeHistory.Date).FirstOrDefault()
                        let firstChangeHistory = change.ChangeHistory.OrderBy(changeHistory => changeHistory.Date).FirstOrDefault()
                        select new ChangeViewModel
                        {
                            ID = change.ID,
                            Title = change.Title,
                            Description = change.Description,
                            InitiationDate = firstChangeHistory.Date,
                            StatusDate = lastChangeHistory.Date,
                            CurrentStatus = lastChangeHistory.Status,
                        }).ToList();

Using let clause this line:

let lastChangeHistory = change.ChangeHistory.OrderByDescending(changeHistory => changeHistory.Date).First()

will give you the last change history for every change. while this line:

let firstChangeHistory = change.ChangeHistory.OrderBy(changeHistory => changeHistory.Date).First()

will give you the first.

Now you have all the data you need and you can select it into ChangeViewModel.