linq查询中的聚合对

Question

我是在框架4.0。这是一个WPF应用程序，在sql server CE上，有一点限制。我有一个像这样的实体:

    public class TimeEvent
    {
       public int Id
       {get; set;}
       public DateTime EventDate
       {get; set;}
       public bool CheckIn
       {get; set;}
    }

我有两种类型的事件，一种用于签入(CheckIn为真)，另一种用于签入(CheckIn为假)。每个事件每天发生一次。我想用linq来处理实体，最终得到一组像这样的对象:

public class Diff
{
   public DateTime Date //The date of both events
   {get; set;}
   public DateTime CheckInTime //Time of first event
   {get; set;}
   public DateTime CheckOutTime //Time of second event
   {get; set;}
   public int Hours //Difference in hours.
   { get { return (CheckOutTime - CheckInTime).Hours;} 
}

有适当的验证规则，因此每种类型的事件在一天内不能发生超过一个。我尝试使用聚合函数，但我真的没有得到任何地方。谢谢你！

Answer 1

按EventDate日期部分分组时间事件。要做到这一点，你需要使用EntityFunctions.TruncateTime方法(简单的EventDate.Date将不与实体框架一起工作)。

var query = from t in db.TimeEvents
            group t by EntityFunctions.TruncateTime(t.EventDate) into g
            let checkIn = g.Min(x => x.EventDate)
            let checkOut = g.Max(x => x.EventDate)           
            select new Diff {
                Date = g.Key,
                CheckInTime = checkIn,
                CheckOutTime = checkOut                    
            };

还可以使用EntityFunctions.DiffHours(checkOut, checkIn)计算服务器端的时差

Answer 2

我假设您有一个TimeEvent s(List<TimeEvent>)的列表，我将其视为timeEvents。

下面给出了我所有的代码。

    List<TimeEvent> timeEvents = new List<TimeEvent>();
                    //dummy values.
                    DateTime now = DateTime.Now;
                    for (int i = 0; i < 8; i++)
                    {
                        timeEvents.Add(new TimeEvent() { Id = i, EventDate = now, CheckIn = false });
                        now = now.Add(TimeSpan.Parse("3:1"));
                        timeEvents.Add(new TimeEvent() { Id = i, EventDate = now, CheckIn = true });
                        now = now + TimeSpan.Parse("1");
                    }
                    //group by 
                    var dataSet = timeEvents.GroupBy(a => a.EventDate.Date);
                    List<Diff> diffs = new List<Diff>();
                    foreach (var x in dataSet) 
                    {
                        if (!(x.Count() == 1))
                        {
                            diffs.Add(new Diff() { Date = x.ElementAt(0).EventDate.Date, CheckInTime = (x.ElementAt(0).CheckIn == true) ? x.ElementAt(0).EventDate : x.ElementAt(1).EventDate, CheckOutTime = (x.ElementAt(0).CheckIn == false) ? x.ElementAt(0).EventDate : x.ElementAt(1).EventDate });
                        }
                    }
                    //diffs list is full. now you can use the diffs as you like :)

Answer 3

无论创建什么聚合委托，都需要EF将其转换为SQL。即使您的数据是完全干净和有序的，这将是一个挑战。为什么不直接用这个

static IEnumerable<Diff> GetDiffs(YourContext context)
{
    bool? checkedIn;
    DateTime lastDate;
    foreach (var event in context.TimeEvents.OrderBy(te => te.EventDate))
    {
        if ((checkedIn.HasValue && checkedIn.Value = !event.CheckIn) ||
                (!checkedIn.HasValue && event.CheckIn))
        {
            if (checkedIn.HasValue && !event.CheckIn)
            {
                yield return new Diff
                    {
                        CheckInTime = lastDate;
                        CheckOutTime = event.EventDate;
                    };
            }                    
            checkedIn = event.CheckIn;
            lastDate = event.EventDate;
        }
    }
}

这样，不管你的数据从哪里开始，或者它是否正确地翻转。我也忽略了你虚假的Diff.Date属性。