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;
items.Add(new HeatMapViewModel
{
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;
items.Add(new HeatMapViewModel
{
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;
items.Add(new HeatMapViewModel
{
Latitude2 = filteredQuery[i].Latitude,
Longitude2 = filteredQuery[i].Longitude,
Difference = (int)differenceTicks.TotalMinutes,
Date = firstitem.CurrentDateTime
});
}
}
return items;
}
}
Here is data from db
.OrderBy()
clause so there is no guarantee that the result will be returned in the order your expecting \$\endgroup\$0
,1
and2
relate to (i.e. which isStart
and which isStop
and what is the other value for)? The fact that most records in your table image have0
and only a few have1
or2
and there seems to be no order to them would potentially cause problems with your query - and at the very least its very inefficient. \$\endgroup\$