Select Min time value from DateTime column for each day

user_3120129 0
Viewed 139

There is a table (column RecordDateTime):

 20.2.2021 12:54:34
 20.2.2021 13:54:34
 20.2.2021 14:54:34
 20.2.2021 15:54:34
 20.2.2021 16:54:34
 21.2.2021 11:50:00
 21.2.2021 13:54:34
 21.2.2021 14:54:34
 22.2.2021 10:00:00
 22.2.2021 13:54:34
 22.2.2021 14:54:34

I need to pick up

20.2.2021 12:54:34 
21.2.2021 11:50:00 
22.2.2021 10:00:00

and calculate average time.

Here is my current code:

    public TimeSpan BeginTime(DateTime startDate, DateTime endDate)
    {
            var mainGridInfo = from w in db.LogModel
                               where w.RecordDateTime.Date >= startDate.Date && w.RecordDateTime.Date <= endDate.Date
                               group w by w.RecordDateTime.Date into test
                               select test.OrderByDescending(cs => cs.RecordDateTime.TimeOfDay).FirstOrDefault();

            var minDatesList = mainGridInfo.Select(s => s.RecordDateTime.TimeOfDay).ToList();

            double doubleAverageTicks = minDatesList.Average(timeSpan => timeSpan.Ticks);
            long longAverageTicks = Convert.ToInt64(doubleAverageTicks);

            return new TimeSpan(longAverageTicks);
    }

So as I understand I am grouping by Date into test, then ordering by time portion of DateTime column, then taking First or default (for getting away of possible exceptions). Then I am selecting all available values into new list and calculating average of those? Why my code is producing exceptions? What is wrong?

System.InvalidOperationException: 'The LINQ expression 'GroupByShaperExpression: KeySelector: rtrim(rtrim(strftime('%Y-%m-%d %H:%M:%f', a.RecordDateTime, 'start of day'), '0'), '.'), ElementSelector:EntityShaperExpression: EntityType: LogModel ValueBufferExpression: ProjectionBindingExpression: EmptyProjectionMember IsNullable: False

.OrderByDescending(cs => cs.RecordDateTime.TimeOfDay)' could not be translated. Either rewrite the query in a form that can be

translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.'

entity-framework-core linq c#
3 Answers

Try following :

            DataTable dt = new DataTable();
            dt.Columns.Add("Date", typeof(DateTime));

            dt.Rows.Add(new object[] {DateTime.ParseExact("20.2.2021 12:54:34", "dd.M.yyyy HH:mm:ss", System.Globalization.CultureInfo.InvariantCulture)});
            dt.Rows.Add(new object[] {DateTime.ParseExact("20.2.2021 13:54:34", "dd.M.yyyy HH:mm:ss", System.Globalization.CultureInfo.InvariantCulture)});
            dt.Rows.Add(new object[] {DateTime.ParseExact("20.2.2021 14:54:34", "dd.M.yyyy HH:mm:ss", System.Globalization.CultureInfo.InvariantCulture)});
            dt.Rows.Add(new object[] {DateTime.ParseExact("20.2.2021 15:54:34", "dd.M.yyyy HH:mm:ss", System.Globalization.CultureInfo.InvariantCulture)});
            dt.Rows.Add(new object[] {DateTime.ParseExact("20.2.2021 16:54:34", "dd.M.yyyy HH:mm:ss", System.Globalization.CultureInfo.InvariantCulture)});
            dt.Rows.Add(new object[] {DateTime.ParseExact("21.2.2021 11:50:00", "dd.M.yyyy HH:mm:ss", System.Globalization.CultureInfo.InvariantCulture)});
            dt.Rows.Add(new object[] {DateTime.ParseExact("21.2.2021 13:54:34", "dd.M.yyyy HH:mm:ss", System.Globalization.CultureInfo.InvariantCulture)});
            dt.Rows.Add(new object[] {DateTime.ParseExact("21.2.2021 14:54:34", "dd.M.yyyy HH:mm:ss", System.Globalization.CultureInfo.InvariantCulture)});
            dt.Rows.Add(new object[] {DateTime.ParseExact("22.2.2021 10:00:00", "dd.M.yyyy HH:mm:ss", System.Globalization.CultureInfo.InvariantCulture)});
            dt.Rows.Add(new object[] {DateTime.ParseExact("22.2.2021 13:54:34", "dd.M.yyyy HH:mm:ss", System.Globalization.CultureInfo.InvariantCulture)});
            dt.Rows.Add(new object[] { DateTime.ParseExact("22.2.2021 14:54:34", "dd.M.yyyy HH:mm:ss", System.Globalization.CultureInfo.InvariantCulture)});

            var days = dt.AsEnumerable().GroupBy(x => x.Field<DateTime>("Date").Date).ToList();
            DateTime[] minDate = days.Select(x => x.Min(y => y.Field<DateTime>("Date"))).ToArray();
user_50152380

Lets assume we have some List<DateTime> dates, we should group them by date and extract min value from each group like that:

var groupedDates = dates.GroupBy(d => d.Date)
               .Select(
                   d => d.OrderByAscending(x => x.Millisecond).FirstOrDefault());
user_31793890

Take a look at my variant, too:

using System;
using System.Collections.Generic;
using System.Linq;

namespace Game
{
    class LogModel
    {
        public DateTime RecordDateTime { get; set; }
    }

    class Program
    {   
        static List<LogModel> _log = new List<LogModel>
        {
            new LogModel { RecordDateTime = DateTime.ParseExact("20.2.2021 12:54:34", "dd.M.yyyy HH:mm:ss", System.Globalization.CultureInfo.InvariantCulture) },
            new LogModel { RecordDateTime = DateTime.ParseExact("20.2.2021 13:54:34", "dd.M.yyyy HH:mm:ss", System.Globalization.CultureInfo.InvariantCulture) },
            new LogModel { RecordDateTime = DateTime.ParseExact("20.2.2021 14:54:34", "dd.M.yyyy HH:mm:ss", System.Globalization.CultureInfo.InvariantCulture) },
            new LogModel { RecordDateTime = DateTime.ParseExact("20.2.2021 15:54:34", "dd.M.yyyy HH:mm:ss", System.Globalization.CultureInfo.InvariantCulture) },
            new LogModel { RecordDateTime = DateTime.ParseExact("20.2.2021 16:54:34", "dd.M.yyyy HH:mm:ss", System.Globalization.CultureInfo.InvariantCulture) },
            new LogModel { RecordDateTime = DateTime.ParseExact("21.2.2021 11:50:00", "dd.M.yyyy HH:mm:ss", System.Globalization.CultureInfo.InvariantCulture) },
            new LogModel { RecordDateTime = DateTime.ParseExact("21.2.2021 13:54:34", "dd.M.yyyy HH:mm:ss", System.Globalization.CultureInfo.InvariantCulture) },
            new LogModel { RecordDateTime = DateTime.ParseExact("21.2.2021 14:54:34", "dd.M.yyyy HH:mm:ss", System.Globalization.CultureInfo.InvariantCulture) },
            new LogModel { RecordDateTime = DateTime.ParseExact("22.2.2021 10:00:00", "dd.M.yyyy HH:mm:ss", System.Globalization.CultureInfo.InvariantCulture) },
            new LogModel { RecordDateTime = DateTime.ParseExact("22.2.2021 13:54:34", "dd.M.yyyy HH:mm:ss", System.Globalization.CultureInfo.InvariantCulture) },
            new LogModel { RecordDateTime = DateTime.ParseExact("22.2.2021 14:54:34", "dd.M.yyyy HH:mm:ss", System.Globalization.CultureInfo.InvariantCulture) },
        };

        static void Main(string[] args)
        {
            var result = _log.GroupBy(x => x.RecordDateTime.Date)
                .Select(x => 
                {
                    var minTime = x.Min(y => y.RecordDateTime.TimeOfDay);

                    return new DateTime(x.Key.Year, x.Key.Month, x.Key.Day, minTime.Hours, minTime.Minutes, minTime.Seconds);
                })
                .ToList();

            result.ForEach(x => Console.WriteLine(x));
        }
    }
}

Output:

20.02.2021 12:54:34
21.02.2021 11:50:00
22.02.2021 10:00:00
user_120792070
Related