# Calculating difference in time

In table first value I have value with datatype = Start

I need to calculate difference first value - midnight

Last value is with datatype =Stop

I need to calculate difference midnight - last item

For other I need to calculate datatype.Stop - previous.datatype=Start

Here is ViewModel that I use for repo method

public class HeatMapViewModel
{
public decimal? Latitude2 { get; set; }
public decimal? Longitude2 { get; set; }
public int coeff = 2;
public int Difference;
public DateTime Date { get; set; }
}


I wrote this code in repo method

public List<HeatMapViewModel> GetStops()
{
using (TraxgoDB ctx = new TraxgoDB())
{
List<HeatMapViewModel> items = new List<HeatMapViewModel>();

var firstitem = ctx.Logging.Where(x => x.Datatype == Datatype.Start).AsEnumerable().FirstOrDefault();

var midnight = new DateTime(firstitem.CurrentDateTime.Year, firstitem.CurrentDateTime.Month, firstitem.CurrentDateTime.Day, 00, 00, 00);
TimeSpan difference = (firstitem.CurrentDateTime - midnight);
var difference_after_midnight = (int)difference.TotalMinutes;

{
Latitude2 = firstitem.Latitude,
Longitude2 = firstitem.Longitude,
Difference = difference_after_midnight,
Date = firstitem.CurrentDateTime
});

var lastItem = ctx.Logging.Where(x => x.Datatype == Datatype.Stop).AsEnumerable().LastOrDefault();
var before_midnight = new DateTime(lastItem.CurrentDateTime.Year, lastItem.CurrentDateTime.Month, lastItem.CurrentDateTime.Day, 23, 59, 00);
TimeSpan difference_before = (before_midnight - lastItem.CurrentDateTime);
var difference_before_midnight = (int)difference_before.TotalMinutes;

{
Latitude2 = lastItem.Latitude,
Longitude2 = lastItem.Longitude,
Difference = difference_before_midnight,
Date = firstitem.CurrentDateTime
});

var allitems = ctx.Logging;
var filteredQuery = allitems.Where(x => x.Datatype == Datatype.Start || x.Datatype == Datatype.Stop).OrderByDescending(x => x.LogID).ToList();

for (int i = 1; i < filteredQuery.Count; i++)
{
if (filteredQuery[i].Datatype == Datatype.Stop && filteredQuery[i - 1].Datatype == Datatype.Start)
{
TimeSpan differenceTicks = filteredQuery[i - 1].CurrentDateTime - filteredQuery[i].CurrentDateTime;

{
Latitude2 = filteredQuery[i].Latitude,
Longitude2 = filteredQuery[i].Longitude,
Difference = (int)differenceTicks.TotalMinutes,
Date = firstitem.CurrentDateTime
});
}
}
return items;
}
}


Here is data from db

It works great, but I need to know if I can modify it?

• There are a few changes you can make to both simplify this and improve performance (for example, you making 3 database calls when you only need one) but without seeing a sample of your database records and how this data is being used it a bit hard to give an answer. – Stephen Muecke Nov 15 '17 at 1:02
• And you have not specified an .OrderBy() clause so there is no guarantee that the result will be returned in the order your expecting – Stephen Muecke Nov 15 '17 at 1:05
• I updated my post @StephenMuecke – S.E Nov 20 '17 at 13:50
• What do the numbers 0, 1 and 2 relate to (i.e. which is Start and which is Stop and what is the other value for)? The fact that most records in your table image have 0 and only a few have 1 or 2 and there seems to be no order to them would potentially cause problems with your query - and at the very least its very inefficient. – Stephen Muecke Nov 21 '17 at 0:27
• 1- Start, 2 - Stop, 0 -Driving. For 0 is other method. I working with 1 and 2 @StephenMuecke – S.E Nov 21 '17 at 8:54

The main area where you code can be improved is in your database calls. Currently your making 3 database calls. When you use

var lastItem = ctx.Logging.Where(x => x.Datatype == Datatype.Stop).AsEnumerable().LastOrDefault();


your loading all the records into memory (using .AsEnumerable()) in order to get the last one then throwing all the rest away, and then making another database query using

var filteredQuery = allitems.Where(...)


which fetches those records all over again. You only need the one database call to materialize the records to memory, and then you can get the first and last records from that collection.

Your also not testing for null when you use .FirstOrDefault() and .LastOrDefault() which has the potential to throw an exception in the code following those queries.

You can also simplify some code such as

var midnight = new DateTime(firstitem.CurrentDateTime.Year, firstitem.CurrentDateTime.Month, firstitem.CurrentDateTime.Day, 00, 00, 00);


which can be rewritten as

var midnight = firstitem.CurrentDateTime.Date;


You could re-write your method to have only one database call as

public List<HeatMapViewModel> GetStops()
{
using (TraxgoDB ctx = new TraxgoDB())
{
List<HeatMapViewModel> items = new List<HeatMapViewModel>();
var logs = ctx.Logging.Where(x => x.Datatype == Datatype.Start || x.Datatype == Datatype.Stop).OrderByDescending(x => x.LogID).ToList();
if (logs.Count < 2)
{
return items; // or return null;?
}
var first = logs.First();
var currentDateTime = first.CurrentDateTime;
double minutes = (first.CurrentDateTime - first.CurrentDateTime.Date).TotalMinutes;
{
Latitude2 = first.Latitude,
Longitude2 = first.Longitude,
Difference = (int)minutes,
Date = currentDateTime
});
var last = logs.Last();
{
Latitude2 = last.Latitude,
Longitude2 = last.Longitude,
Difference = (int)minutes,
Date = currentDateTime
});
for (int i = 1; i < logs.Count; i++)
{
var previous = logs[i - 1];
var current = logs[i];
if (current.Datatype == Datatype.Stop && previous.Datatype == Datatype.Start)
{
minutes = (previous.CurrentDateTime - current.CurrentDateTime).TotalMinutes;

{
Latitude2 = current.Latitude,
Longitude2 = current.Longitude,
Difference = (int)minutes,
Date = currentDateTime
});
}
}
return items;
}
}


Note that I have assumed that if there were less than 2 items in the table, then your data would not make sense in a view which is the purpose of if (logs.Count < 2). That check will also prevent exceptions in the code that follows.

I have also assumed (based on the description in the question) that .First() will always return an item with DataType.Start and .Last() will always return an item with DataType.Stop so I have excluded checks for that in the code above