Calculating difference in time

Question

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

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

Answer 1

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;
        items.Add(new HeatMapViewModel
        {
            Latitude2 = first.Latitude,
            Longitude2 = first.Longitude,
            Difference = (int)minutes,
            Date = currentDateTime
        });
        var last = logs.Last();
        minutes = (last.CurrentDateTime.Date.AddDays(1) - last.CurrentDateTime).TotalMinutes;
        items.Add(new HeatMapViewModel
        {
            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;

                items.Add(new HeatMapViewModel
                {
                    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