两个日期之间的总小时数与 IQueryable 从数据库

本文关键字：小时 IQueryable 数据库 两个 之间 日期 | 更新日期: 2023-09-27 17:57:16

我有包含这些列的表格：

id int, name string, startDate DateTime, endDate DateTime

我想从所有记录的这些日期之间的数据库小时总和中获取。

我使用IQueryable，但我不知道如何正确形成查询。

public int GetSumOfHours(int? assignedProjectId)
{
    var query = GetAll(); //GetAll() returns IQueryable<T>, 
    if (assignedProjectId.HasValue)
    {
        query = query.Where(solution => solution.AssignedProject.Id == assignedProjectId.Value);
    }
    // Get sum of hours  ----> query = query...??
}

感谢您的帮助！

两个日期之间的总小时数与 IQueryable 从数据库

尝试如下操作。 获取秒数，然后对秒求和。

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            DataTable query = new DataTable();
            query.Columns.Add("id", typeof(int));
            query.Columns.Add("name", typeof(string));
            query.Columns.Add("startDate", typeof(DateTime));
            query.Columns.Add("endDate", typeof(DateTime));

            query.Rows.Add(new object[] { 1, "John", DateTime.Parse("1/1/1 0:0:12"), DateTime.Parse("1/1/1 0:1:12") });
            query.Rows.Add(new object[] { 1, "John", DateTime.Parse("1/1/1 0:3:12"), DateTime.Parse("1/1/1 0:5:12") });
            query.Rows.Add(new object[] { 2, "Bob", DateTime.Parse("1/1/1 0:0:12"), DateTime.Parse("1/1/1 0:1:12") });
            query.Rows.Add(new object[] { 2, "Bob", DateTime.Parse("1/1/1 0:0:12"), DateTime.Parse("1/1/1 0:1:12") });
            var totalSeconds = query.AsEnumerable()
                .Where(x => x.Field<int>("id") == 1)
                .Select(x => (x.Field<DateTime>("endDate") - x.Field<DateTime>("startDate")).TotalSeconds).Sum();
        }
    }
}
​

您可以使用一些数学和Sum()方法：

public class hours
{
    public DateTime Start { get; set; }
    public DateTime End { get; set; }
}

。

List<hours> allHrs = new List<hours>{
    new hours{Start = DateTime.Now.AddHours(-3.2), End = DateTime.Now.AddHours(-2)},
    new hours{Start = DateTime.Now.AddHours(-3.9), End = DateTime.Now.AddHours(-2.03)},
    new hours{Start = DateTime.Now.AddHours(-3.8), End = DateTime.Now.AddHours(-2.9)}
};
//Project a new collection with the math done for number of minutes in each row
var mins = from h in allHrs
    select new {nbrMinutes = (h.End - h.Start).Minutes};
//Sum all rows, divide by 60 to get hours
Double total = mins.Sum (s => s.nbrMinutes / 60.0 );
Console.WriteLine(total);

您可以修改 CrowCoder 的示例，直接使用 TimeSpan 和方法对小时数求和 Subtract 和 TotalHours ：

{
    DateTime d = DateTime.Today;    
    List<hours> exampleHours = new List<hours>{
        new hours{Start = d.AddHours(-3.2), End = d.AddHours(-2)},
        new hours{Start = d.AddHours(-3.9), End = d.AddHours(-2.03)},
        new hours{Start = d.AddHours(-3.8), End = d.AddHours(-2.9)}
    };
    double totalHours = 
        (from h in exampleHours
        select new {allHours = (h.End.Subtract(h.Start).TotalHours)})
        .Sum(t => t.allHours);
    Console.WriteLine(totalHours.ToString());
}
相关文章：