DBContext对象LINQ Sum(time)列到小时 [英] DBContext object LINQ Sum(time) column to hours

问题描述

这是一个使用EntityFrameWorkCore的ASP.Net Core Web API项目.我需要将TotalLoggedInTime(格式:139:05:40)转换为Hours并将小时数加总，并将其分配给logonHours，它在该行上失败.callOffered的第一行工作正常.

This is an ASP.Net Core Web API Project, using EntityFrameWorkCore. I need to convert TotalLoggedInTime (format: 139:05:40) to Hours and Sum the hours and assign it to logonHours, it fails on that line. The first line for callsOffered works fine.

C#代码:

internal void Hydrate(DbSet<MyObj> myObj)
{
    string callsOffered = myObj.Sum(a => a.CallsPresented).ToString();
    string logonHours = myObj.Sum(a => TimeSpan.Parse(a.TotalLoggedInTime).TotalHours).ToString();
}

SQL表

SELECT total_logged_in_time FROM mytable

total_logged_in_time
139:05:40
157:22:46
157:26:51
148:17:42
157:23:14
138:04:28
175:27:41
162:35:44
163:13:13
144:36:12
9:01:56

错误

LINQ表达式'TimeSpan.Parse((EntityShaperExpression:实体类型:MyObjValueBufferExpression:(ProjectionBindingExpression:空投影成员)IsNullable:False).TotalLoggedInTime).TotalHours'无法翻译.以一种可以翻译的形式重写查询，或通过插入调用显式切换到客户评估AsEnumerable()，AsAsyncEnumerable()，ToList()或ToListAsync().请参阅 https://go.microsoft.com/fwlink/?linkid=2101038 为了更多信息.

解决方案:

使用@Arcord提供的解决方案.我不得不添加一种扩展方法来转换小时数，但这超出了原始问题的范围.

Using the solution provided by @Arcord. I had to add an extension method for converting the hours, but that's beyond the scope of this original issue.

string logonHours = myObj.ToList().Sum(a => a.TotalLoggedInTime.ToHours()).ToString();

public static double ToHours(this string valueInTimeFormat)
{
    double hours = 0;

    //hours = TimeSpan.Parse(valueInTimeFormat).TotalHours;

    int[] ssmmhh = { 0, 0, 0 };
    var hhmmss = valueInTimeFormat.Split(':');
    var reversed = hhmmss.Reverse();
    int i = 0;
    reversed.ToList().ForEach(x => ssmmhh[i++] = int.Parse(x));
    hours = (int)(new TimeSpan(ssmmhh[2], ssmmhh[1], ssmmhh[0])).TotalHours;

    return hours;
}

推荐答案

这是因为它试图将您的代码转换为SQL.

It's because it tries to convert your code to SQL.

string logonHours = myObj.Sum(a => TimeSpan.Parse(a.TotalLoggedInTime).TotalHours).ToString();

当然，所有内容都不能转换为SQL，因此在这种情况下会失败.

Of course everything is not convertible to SQL so in that case it fail.

您可以使用此:

string logonHours = myObj.ToList().Sum(a => TimeSpan.Parse(a.TotalLoggedInTime).TotalHours).ToString();

它应该工作，但是，.ToList()"将强制EF从数据库中获取所有数据，然后(由于所有内容都在内存中)可以应用代码.

It should works but the ".ToList()" will force EF to fetch all data from the database and then (since everything is in memory) the code can be applied.

但是仅当您的数据量很少时才这样做！如果您不知道预期的负载，最好不要将这种东西投入生产.

But only do this if you have a small amount of data! It's better not to put that kind of stuff in production if you don't know the expected load.

修改

您还可以考虑存储滴答"而不是字符串(如所述

You could also also considers to store "ticks" instead of a string (like described here). In that case you will the able to do.

string logonHours = TimeSpan.FromTicks(myObj.Sum(a => a.TotalLoggedInTime)

将在SQL Server上完成更多工作，而您只会收集总和.

More work will be done on the SQL server and you will only gather the sum.

您还应该考虑使用方法的异步"版本(ToListAsync，SumAsync等)

You should also considers to use "async" version of methods (ToListAsync, SumAsync, ...)

这篇关于DBContext对象LINQ Sum(time)列到小时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！