使用LINQ将多个日期转换为范围

Question

我需要帮助应用跨日历年的设置。我的用户正在输入数字目标到数据库根据季节在日历。想想看，苹果酒在秋季(9月到11月)的销量会更高，而在一年中的其他时间(12月到8月)销量会更低。然而，苹果汁全年销售均匀。

为了简化用户的数据输入，他们为每个产品输入目标销售数字以及该数字开始的月份。

例如，数据库中的数据看起来像这样(任何产品最多有两个目标):

Apple Cider - September - 5,000 gallons
Apple Cider - December - 1,000 gallons
Apple Juice - September - 3,000 gallons

我还在一个数据库表中保存了我们的财务日历，这样我就知道哪个财政年度、哪个月、哪个月的周数等与任何一天相关联。

我需要将这些数据应用到报告中，但是我需要一年中每个月的数据，而不仅仅是用户输入的数据。

我在使用LINQ加入我的财务日历以创建数据的爆炸视图时遇到麻烦?我如何从一年中所有月份的清单开始，为每个产品，把目标放在日历上，然后填补空白。例如Apple Cider使用上面的例子

January
February
March
April 
May 
June 
July 
August 
September - 5,000 gallonws
October
November 
December - 1,000 gallons

Apple Cider - September - 5,000 gallons
Apple Cider - October - 5,000 gallons
Apple Cider - November - 5,000 gallons
Apple Cider - December - 1,000 gallons
Apple Cider - January - 1,000 gallons
Apple Cider - February- 1,000 gallons
Apple Cider - March - 1,000 gallons
Apple Cider - April- 1,000 gallons
Apple Cider - May - 1,000 gallons
Apple Cider - June - 1,000 gallons
Apple Cider - July - 1,000 gallons
Apple Cider - August - 1,000 gallons
Apple Juice - September - 3,000 gallons
Apple Juice - October - 3,000 gallons
Apple Juice - November - 3,000 gallons
Apple Juice - December - 3,000 gallons
Apple Juice - January - 3,000 gallons
Apple Juice - February - 3,000 gallons
Apple Juice - March - 3,000 gallons
Apple Juice - April - 3,000 gallons
Apple Juice - May - 3,000 gallons
Apple Juice - June - 3,000 gallons
Apple Juice - July - 3,000 gallons
Apple Juice - August - 3,000 gallons

Answer 1

我不确定LINQ在这种情况下会有多大帮助。

您可以使用for循环相对简单地实现所需的转换:

' Sample data using anonymous types
Dim entries() =
    {New With {.Name = "Apple Cider", .Year = 2012, .Month = 9, .Quantity = 5000},
     New With {.Name = "Apple Cider", .Year = 2012, .Month = 12, .Quantity = 1000},
     New With {.Name = "Apple Juice", .Year = 2013, .Month = 9, .Quantity = 3000}}
' Dictionary keyed by date
Dim dict = entries.ToDictionary( _
    Function(entry) New DateTime(entry.Year, entry.Month, 1))
' Current is first entry
Dim currentEntry = entries(0)
Dim epoch = New DateTime(currentEntry.Year, currentEntry.Month, 1)
' Iterate over 24 months
For i = 0 To 23            
    Dim currentMonth = epoch.AddMonths(i)
    ' Update current based on current month
    If dict.ContainsKey(currentMonth) Then
        currentEntry = dict(currentMonth)
    End If
    ' Write data
    Console.WriteLine("{0} - {1} - {2} gallons",
                      currentEntry.Name,
                      MonthName(currentMonth.Month),
                      currentEntry.Quantity)
Next

Answer 2

要在Linq中做到这一点，请使用Range()(在可以在循环中完成的情况下，通常可以替换range)

下面是一个使用Enumerable的例子。Range和@PhillipTrelford的示例数据:

Sub Main
  Dim entries() =  {New With {.Name = "Apple Cider", .Year = 2012, .Month = 9, .Quantity = 5000}, _
                    New With {.Name = "Apple Cider", .Year = 2012, .Month = 12, .Quantity = 1000}, _
                    New With {.Name = "Apple Juice", .Year = 2013, .Month = 9, .Quantity = 3000}}
  Dim dict = entries.ToDictionary( Function(entry) New DateTime(entry.Year, entry.Month, 1))
  Dim startDate as DateTime = New DateTime(2012,9,1)
  Dim curDefault = New With {.Name = "", .Quantity = 0}
  Dim result = Enumerable.Range(0, 24).Select(Function(x)
    Dim thisdate = startDate.AddMonths(x)
    If dict.ContainsKey(thisdate) Then
      curDefault.Name = dict(thisdate).Name
      curDefault.Quantity = dict(thisdate).Quantity
    End If
    Return String.Format("{0} - {1} - {2} gallons",curDefault.Name,thisdate.ToString("MMMM"),curDefault.Quantity)
  End Function)
  result.Dump
End Sub

这段代码运行得很好，使用linqpad(参见linqpad.com)可以得到预期的结果

Apple Cider - September - 5000 gallons 
Apple Cider - October - 5000 gallons 
Apple Cider - November - 5000 gallons 
Apple Cider - December - 1000 gallons 
Apple Cider - January - 1000 gallons 
Apple Cider - February - 1000 gallons 
Apple Cider - March - 1000 gallons 
Apple Cider - April - 1000 gallons 
Apple Cider - May - 1000 gallons 
Apple Cider - June - 1000 gallons 
Apple Cider - July - 1000 gallons 
Apple Cider - August - 1000 gallons 
Apple Juice - September - 3000 gallons 
Apple Juice - October - 3000 gallons 
Apple Juice - November - 3000 gallons 
Apple Juice - December - 3000 gallons 
Apple Juice - January - 3000 gallons 
Apple Juice - February - 3000 gallons 
Apple Juice - March - 3000 gallons 
Apple Juice - April - 3000 gallons 
Apple Juice - May - 3000 gallons 
Apple Juice - June - 3000 gallons 
Apple Juice - July - 3000 gallons 
Apple Juice - August - 3000 gallons