I am writing a small feature in an existing ASP.NET 4.6 MVC app that will allow for search of records in a table and then show a series of records from the matching temporal table to highlight who changed what and when.
So for example, if a client record has a name field that has been changed, I want to show a list from the temporal table showing each time that entity was changed, and I want a way to compare each entry to the entry directly before it (ordered by date) to show exactly what had changed in that entry and how it differs from the previous by highlighting it in red in the table or similar. Is there a way to do this on the FE in the foreach loop? Or is it something I would handle in the controller.
My existing table razor-page code:
<table >
<tr>
<th>DB ID</th>
<th>Reference</th>
<th>@Html.ActionLink("Last Modified", "ServiceDeliveryHistoryCompare", new { sortOrder = ViewBag.DateSortParm, currentFilter = ViewBag.CurrentFilter })</th>
<th>Service Support Reference</th>
<th>Claim Date</th>
<th>Created By</th>
<th>Service Provider ID</th>
<th>Provider Invoice Number</th>
</tr>
@foreach (var sd in Model.ServiceDeliveryHistories)
{
<tr>
<td>@sd.dbId</td>
<td>@sd.Reference</td>
<th>@sd.DateLastModified</th>
<td>@sd.ServiceSupportReference</td>
<td>@sd.ClaimDate</td>
<td>@sd.CreatedByName</td>
<td>@sd.ServiceProviderId</td>
<td>@sd.ProviderInvoiceNumber</td>
</tr>
}
</table>
Page @(Model.ServiceDeliveryHistories.PageCount < Model.ServiceDeliveryHistories.PageNumber ? 0 : Model.ServiceDeliveryHistories.PageNumber) of @Model.ServiceDeliveryHistories.PageCount
I am not sure if this helps, but here is my controller code
public async Task<ActionResult> ClientHistoryCompare(string currentFilter, string sortOrder, string searchString, DateTime? start, DateTime? end, int? pageHistory, int? pageClient)
{
ViewBag.CurrentSort = sortOrder;
ViewBag.NameSortParm = String.IsNullOrEmpty(sortOrder) ? "name_desc" : "";
ViewBag.DateSortParm = sortOrder == "Date" ? "date_desc" : "Date";
if (searchString != null)
{
pageHistory = 1;
pageClient = 1;
}
else
{
searchString = currentFilter;
}
ViewBag.CurrentFilter = searchString;
ClientViewModel clientViewModel = new ClientViewModel();
IEnumerable<Client> clients = await db.Clients.OrderBy(s => s.LastName).ToListAsync();
IEnumerable<ClientHistory> clientHistories = await db.ClientHistories.ToListAsync();
Guid clientGuid;
if (!String.IsNullOrEmpty(searchString))
{
clients = clients.Where(s => s.Identifier.ToString() == searchString);
var client = clients.FirstOrDefault();
if (client != null)
{
clientGuid = client.Identifier;
clientHistories = clientHistories.Where(i => i.Identifier == clientGuid);
}
else return new HttpNotFoundResult("Client ID not found");
}
switch (sortOrder)
{
case "name_desc":
clients = clients.OrderByDescending(s => s.LastName);
break;
case "Date":
clientHistories = clientHistories.OrderBy(s => s.DateLastModified);
break;
case "date_desc":
clientHistories = clientHistories.OrderByDescending(s => s.DateLastModified);
break;
default: // Name ascending
clientHistories = clientHistories.OrderBy(s => s.LastName);
break;
}
int pageSize = 10;
int pageNumberClient = (pageClient ?? 1);
int pageNumberHistory = (pageHistory ?? 1);
clientViewModel.ClientHistories = clientHistories.ToPagedList(pageNumberHistory, pageSize);
clientViewModel.Clients = clients.ToPagedList(pageNumberClient, pageSize);
return View(clientViewModel);
}
Is there an easy way to do this in Entity Framework? I thought it might be a little easier than I am finding it and there might have been a function I could use to do this easily, but I have not had any luck. Any help would be appreciated.
CodePudding user response：
Honestly, there is actually quite a bit wrong with the controller code. One key issue you will run into is the fact that you are passing Entities to the view logic instead of POCO view models. To see what has changed you will need some variant of post-processing to go through the values to look for changes. Then you will need some means of storing an indicator to use in your view to toggle a CSS class for whether that field was modified or not.
The first would be if you are using ASP.Net 4.6, what version of Entity Framework are you using? I would highly recommend that you upgrade EF to the highest version supported by the .Net Framework dependencies you have that absolutely cannot be upgraded. For instance if EF 6.4.4 requires updating C# to 4.7 and that causes problems then you should be able to upgrade to EF 6.2.
There is absolutely no argument to keep projects on outdated libraries that are falling out of official support. You stand to gain capabilities and significant performance improvements as well as a wider support network as information out there on older versions is unreliable at best and people won't be running these versions to help debug issues.
A few things you should consider to optimize your code.
IEnumerable<Client> clients = await db.Clients.OrderBy(s => s.LastName).ToListAsync();
IEnumerable<ClientHistory> clientHistories = await db.ClientHistories.ToListAsync();
This is loading ALL clients and client history into memory. You definitely do not want to do this when you're interested in just one client, or a page of results. Instead you should be building up queries so that they are ultimately only executed once to fetch the single applicable page of results.
The view model seems a bit whack in terms of what it is displaying as it seems to handle situations where you expect one client's data, or potentially many clients' data & history. An attempt not to break the current behaviour, but ensure filtering is done by the Database rather than in-memory copies:
public async Task<ActionResult> ClientHistoryCompare(string currentFilter, string sortOrder, string searchString, DateTime? start, DateTime? end, int? pageHistory, int? pageClient)
{
ClientViewModel viewModel = null;
if (!string.IsNullOrEmpty(searchString))
{
Guid clientGuid;
if (!Guid.TryParse(searchString, out clientGuid))
return new HttpNotFoundResult("Client ID not found");
viewModel = BuildViewModelForSingleClient(clientGuid, sortOrder, pageSize);
if (viewModel == null)
return new HttpNotFoundResult("Client ID not found");
}
else
{
viewModel = BuildViewModelForClients(sortOrder, clientPageNumber ?? 1, historyPageNumber ?? 1, pageSize);
}
return View(viewModel);
}
private ClientViewModel BuildViewModelForSingleClient(Guid clientGuid, string sortOrder, int pageSize)
{
var viewModel = new ClientViewModel();
var client = db.Clients
.AsNoTracking()
.SingleOrDefault(c => c.Identifier == clientGuid);
if (client == null)
return null;
var historyQuery = db.ClientHistories
.AsNoTracking()
.Where(ch => ch.Identifier == clientGuid);
switch(sortOrder.ToLower())
{
case "date":
historyQuery = historyQuery.OrderBy(ch => ch.DateLastModified);
break;
case "date_desc":
historyQuery = historyQuery.OrderByDescending(ch => ch.DateLastModified);
break;
}
viewModel.Clients = new [] {client}.ToList();
viewModel.ClientHistories = historyQuery.ToPagedList(1, pageSize);
return viewModel;
}
private ClientViewModel BuildViewModelForClients(string sortOrder, int clientPageNumber, int historyPageNumber int pageSize)
{
var viewModel = new ClientViewModel();
var clientQuery = db.Clients
.AsNoTracking()
.AsQueryable();
var historyQuery = db.ClientHistories
.AsNoTracking()
.AsQueryable();
switch(sortOrder.ToLower())
{
case "date":
historyQuery = historyQuery.OrderBy(ch => ch.DateLastModified);
break;
case "date_desc":
historyQuery = historyQuery.OrderByDescending(ch => ch.DateLastModified);
break;
case "name_desc":
clientQuery = clientQuery.OrderByDescending(c => c.LastName);
historyQuery = historyQuery.OrderByDescending(c => c.LastName);
break;
case "name":
default:
clientQuery = clientQuery.OrderBy(c => c.LastName);
historyQuery = historyQuery.OrderBy(c => c.LastName);
break;
}
viewModel.Clients = clientQuery.ToPagedList(clientPageNumber, pageSize);
viewModel.ClientHistories = historyQuery.ToPagedList(historyPageNumber, pageSize);
return viewModel;
}
From the original code I didn't see if/where the Start and End dates were being used, but I would suspect those should be applied to the History query as boundaries. Realistically I would recommend further splitting up the loading of client and histories so that these can be loaded individually such as with AJAX calls via pagination controls so that you can request just a page of Histories and refresh that UI element rather than refreshing an entire page each time the user navigates to another page of results.
Now to the crux of the question. To highlight when something changes, we would want the razor view to be able to do something like:
<td>@sd.dbId</td>
<td>@sd.Reference</td>
<th>@sd.DateLastModified</th>
<td>@sd.ServiceSupportReference</td>
<td @(sd.ClaimDateChanged ? "class=\"highlight\"" : "")>@sd.ClaimDate</td>
<td>@sd.CreatedByName</td>
<td @(sd.ServiceProviderIdChanged ? "class=\"highlight\"" : "")>@sd.ServiceProviderId</td>
<td @(sd.ProviderInvoiceNumberChanged ? "class=\"highlight\"" : "")>@sd.ProviderInvoiceNumber</td>
Where the "highlight" class is a CSS class style with the desired color/decoration.
Normally the view model used by the view would be a simple class populated with the values from the Entity. However, that will probably be a pretty big change to make, so the alternative would be to add these fields to the entity to be treated as non-mapped fields. This means that EF will treat them like they don't exist, but that does mean you need to be careful not to try and reference them in a Linq expression that EF would be parsing to build an SQL Query.
So if we have an entity class like:
public class ClientHistory
{
public Guid Identifier { get; set; }
// ...
public DateTime ClaimDate { get; set; }
[NotMapped]
public bool ClaimDateChanged { get; set; } = false;
// ...
}
For each property you want to be able to flag has changed, we add a [NotMapped] bool flag indicator.
The next step will be to go through the page of history results to determine if the fields have changed. The caveat here is we have to account for whether the items in the page are sorted ascending or descending. This also does not highlight how the first item in the page might differ from a previous item on the earlier page.
private void MarkChanges(IList<ClientHistory> history, bool isDescending = false)
{
if(isDescending)
{
for(int count = history.Count - 2; count >= 0; count--)
{
var current = history[count];
var previous = history[count 1];
MarkChanges(current, previous);
}
}
else
{
for(int count = 1; count < history.Count - 1; count )
{
var current = history[count];
var previous = history[count-1];
MarkChanges(current, previous);
}
}
}
private void MarkChanges(ClientHistory current, ClientHistory previous)
{
current.ClaimDateChanged = (current.ClaimDate != previous.ClaimDate) ;
// repeat for each "Changed" flag and field comparison.
}
For each field you add a "Changed" flag, add a comparison. The logic needs to take into account that the items may be sorted ascending or descending so when you call it after taking a page of items, you need to tell it which way they were sorted. So for instance we change:
viewModel.ClientHistories = historyQuery.ToPagedList(historyPageNumber, pageSize);
to something like:
var clientHistories = historyQuery.ToPagedList(historyPageNumber, pageSize);
MarkChanges(clientHistories, sortOrder.ToLower() == "date_desc");
viewModel.ClientHistories = clientHistories;