Linq查询获取每月每天的状态

Question

下面是用户不可用表

Id |  UserId | StartDate   | EndDate  
1  |   100   | 01-02-2015  | 01-03-2015  
2  |   101   | 10-02-2015  | 11-03-2015  
3  |   102   | 09-04-2015  | 11-05-2015  
3  |   100   | 01-05-2015  | 01-09-2015

我需要在日历中显示每天有多少用户不可用。日历一次显示一个月的数据，每天显示不可用用户的数量。

我如何从linq获得每月每一天的数据?

Answer 1

尝试以下代码(用于本地用户集合，而不是linq到sql):

class User
{
    public int Id;
    public int UserId;
    public DateTime StartDate;
    public DateTime EndDate;
}
class Program
{
    static void Main(string[] args)
    {
        var users = new User[]{ 
            new User { Id = 1, UserId = 100, StartDate = new DateTime(2015,2,1), EndDate = new DateTime(2015,3,1) },
            new User { Id = 2, UserId = 101, StartDate = new DateTime(2015,2,10), EndDate = new DateTime(2015,3,11) },
            new User { Id = 3, UserId = 102, StartDate = new DateTime(2015,4,9), EndDate = new DateTime(2015,5,11) },
            new User { Id = 3, UserId = 100, StartDate = new DateTime(2015,5,1), EndDate = new DateTime(2015,9,11) }
        };
        var minDate = users.Select(x => x.StartDate).Min();
        var maxDate = users.Select(x => x.EndDate).Max();
        var counts = Enumerable
            .Range(0, Convert.ToInt32((maxDate - minDate).TotalDays))
            .Select(x => {
                var Date = minDate.AddDays(x);
                return new
                {
                    Date,
                    Count = users.Where(u => Date >= u.StartDate && Date <= u.EndDate).Count() 
                };
            }).Where(x => x.Count > 0)
            .ToArray();
    }
}

Answer 2

我想这就行了

int TotalDayCount = 365;
DateTime startDay = DateTime.Now;
var NoOneAvailable = (from x in Enumerable.Range(0, TotalDayCount)
let myday = startDay.AddDays(x)
where !datapoints.Any(f => f.start > myday && f.end < myday)
select myday).ToList();