关于我想做什么的虚构故事
我正在使用C＃，NHibernate，Fluent NHibernate和LINQ（存储库模式）假设我有一个停车位，所以我有一辆汽车从租赁日期到租赁日期。这成为进入数据库的一个入口。
我想了解在过去30天内每天有多少辆汽车。所以可以在趋势图中使用的数据。 $ p
我有数据库中的条目是这样的： 条目： -------- ID StartDate EndDate
我需要了解在一段时间内（例如过去30天），每天有很多条目 。
如果我在数据库中说这个数据： $ $ p $ ID |开始| End --- | ------------ | ---------- 1 | 2013-01-01 | 2013-01-01 2 | 2013-01-02 | 2013-01-02 3 | 2013-01-02 | < - 通知 4 | | 2013-01-03 5 | | < - 通知 6 | | 2013-01-05
我有日期范围 2013-01-01 直到 2013-01-05 。
我除了结果：
Date |值|条目ID（不是结果的一部分，只是为了清晰起见） ----------- | ------- | ----------- 2013-01-01 | 1 | 1 2013-01-02 | 2 | 2 3 2013-01-03 | 3 | 3 4 5 2013-01-04 | 2 | 5 6 2013-01-05 | 2 | 5 6我目前只能按 startdate 然后得到数字，但现在是无效的，因为这个新的要求已经被添加了 enddate 。
我可以在这个范围内的每一天做一个尝试找到匹配的条目，但我认为这将是太昂贵的数据库或Web服务。
建议？
解决方案可能是这样的：
var start = new DateTime（2013，01，01）; var end = DateTime（2013，01，05）; var q = d.Query（） .Where（x => x.Date> =开始&& end> = x.Date）//在那里你筛选范围 .GroupBy（x => x.Date）;开始时间和结束时间都包括在内。编辑
我发现这个（其实我在我的项目）：
var start = new DateTime（2013，1，1）; var end = new DateTime（2013，1，5）; var current = start; var ranges = new List< DateTime>（）; while（current <= end） { ranges.Add（current）; current = current.AddDays（1）; var res = ranges.Select（x => new { date = x， entries = entries.Where（e = > e.Start< = x&& x< = e.End）.ToList（）}）。ToList（）;请尝试测试它，也许您可以对您的案例进行调整（请注意，过滤是从日期范围开始的） 。
I'm using C#, NHibernate, Fluent NHibernate and LINQ (repository pattern).
Fictional story on what I want to do
Say I have a car parking place, so I have a car that is renting a space from date until date. This becomes one entry into the database.
I would like to find out how many cars there were for each day over the period of last 30 days. So data that can be used in a trend chart.
Description
What I have is entries in the database that are like so:
Entry: -------- ID StartDate EndDateI need to find out how many entries there were each day for a time period (say last 30 days).
If I have say this data in the database:
ID | Start | End ---|------------|---------- 1 | 2013-01-01 | 2013-01-01 2 | 2013-01-02 | 2013-01-02 3 | 2013-01-02 | 2013-01-03 <-- NOTICE 4 | 2013-01-03 | 2013-01-03 5 | 2013-01-03 | 2013-01-05 <-- NOTICE 6 | 2013-01-04 | 2013-01-05I have date range 2013-01-01 until 2013-01-05.
I except the result:
Date | Value | Entry IDs (not part of the result, only here for clarity) -----------|-------|----------- 2013-01-01 | 1 | 1 2013-01-02 | 2 | 2 3 2013-01-03 | 3 | 3 4 5 2013-01-04 | 2 | 5 6 2013-01-05 | 2 | 5 6I currently only group by startdate and then get the numbers, but this is invalid now because of this new requirement that was added with enddate.
I could do a for each day in that range, try to find entries that match in that, but I'm thinking that would be too expensive on the database or web service.
Advices?
解决方案Maybe something like this:
var start = new DateTime(2013, 01, 01); var end = DateTime(2013, 01, 05); var q = d.Query() .Where(x => x.Date >= start && end >= x.Date) // there you filter for range .GroupBy(x => x.Date); // use .Day to group by dayStart time and end time are inclusive.
EDIT
I've found this (actually i've a similar case in my project):
var start = new DateTime(2013, 1, 1); var end = new DateTime(2013, 1, 5); var current = start; var ranges = new List<DateTime>(); while (current <= end) { ranges.Add(current); current = current.AddDays(1); } var res = ranges.Select(x => new { date = x, entries = entries.Where(e => e.Start <= x && x <= e.End).ToList() }).ToList();Try test it and maybe you can acomodate to your case (note that filtering starts by the date range).
更多推荐
从具有开始日期和结束日期的数据创建趋势数据
发布评论