如何使这个LINQ查询更干净？

I have recently written a LINQ query to get a Dictionary containing the last 6 month's placement amounts.

我最近编写了一个LINQ查询来获取包含最近6个月的展示位置数量的词典。

It is returning a Dictionary of Month string - Decimal Amount pairs.

它返回一个月份字典字符串 - 十进制数量对。

It seems kind of cludgey. Any of you LINQ masters out there able to help me refactor this to make a bit cleaner?

这似乎是一种喧嚣。你们中的任何一位LINQ大师都能帮助我重构这个以使它更干净一点吗?

/// <summary>
/// Gets the last 6 months of Placement History totalled by Month 
/// for all Agencies
/// </summary>
/// <returns></returns>
public Dictionary<string, decimal> getRecentPlacementHistory()
{
    var placementHistoryByMonth = new Dictionary<string, decimal>();

    using (DemoLinqDataContext db = new DemoLinqDataContext())
    {
        for (int i = 0; i < 6; i++)
        {
            Decimal monthTotal = 
              (from a in db.Accounts
               where 
                 (a.Date_Assigned.Value.Month == DateTime.Now.AddMonths(-i).Month &&
                  a.Date_Assigned.Value.Year == DateTime.Now.AddMonths(-i).Month)
               select a.Amount_Assigned).Sum();
            String currentMonth = DateTime.Now.AddMonths(-i).ToString("MMM");

            placementHistoryByMonth.Add(currentMonth, monthTotal);
        }
        return placementHistoryByMonth;
    }
}

3 个解决方案

#1

First problem:

where (a.Date_Assigned.Value.Month == DateTime.Now.AddMonths(-i).Month &&
       a.Date_Assigned.Value.Year == DateTime.Now.AddMonths(-i).Month)

Shouldn't the latter expression end with .Year rather than .Month? Surely you'll rarely get a year with a value of 1-12...

后面的表达不应该以.Year而不是.Month结束吗?当然你很少会得到一年的价值1-12 ......

I would extract the idea of the "current month" as you're using it a lot. Note that you're also taking the current time multiple times, which could give odd results if it runs at midnight at the end of a month...

当你正在使用它时,我会提取“当前月份”的想法。请注意,您还要多次使用当前时间,如果它在月底的午夜运行,可能会产生奇怪的结果......

public Dictionary<string, decimal> getRecentPlacementHistory()
{
    var placementHistoryByMonth = new Dictionary<string, decimal>();
    using (DemoLinqDataContext db = new DemoLinqDataContext())
    {
        DateTime now = DateTime.Now;

        for (int i = 0; i < 6; i++)
        {
            DateTime selectedDate = now.AddMonths(-i);

            Decimal monthTotal = 
               (from a in db.Accounts
                where (a.Date_Assigned.Value.Month == selectedDate.Month &&
                       a.Date_Assigned.Value.Year == selectedDate.Year)
                select a.Amount_Assigned).Sum();

            placementHistoryByMonth.Add(selectedDate.ToString("MMM"),
                                        monthTotal);
        }
        return placementHistoryByMonth;
    }
}

I realise it's probably the loop that you were trying to get rid of. You could try working out the upper and lower bounds of the dates for the whole lot, then grouping by the year/month of a.Date_Assigned within the relevant bounds. It won't be much prettier though, to be honest. Mind you, that would only be one query to the database, if you could pull it off.

我意识到这可能是你试图摆脱的循环。您可以尝试计算整个批次的日期的上限和下限,然后按相关边界内的a.Date_Assigned的年/月进行分组。说实话,它并不会更漂亮。请注意,如果您可以将其关闭,那只会对数据库进行一次查询。

#2

Use Group By

使用分组依据

DateTime now = DateTime.Now;
DateTime thisMonth = new DateTime(now.Year, now.Month, 1);

Dictionary<string, decimal> dict;
using (DemoLinqDataContext db = new DemoLinqDataContext())
{
    var monthlyTotal = from a in db.Accounts
        where a.Date_Assigned > thisMonth.AddMonths(-6)
        group a by new {a.Date_Assigned.Year, a.Date_Assigned.Month} into g
        select new {Month = new DateTime(g.Key.Year, g.Key.Month, 1),
                    Total = g.Sum(a=>a.Amount_Assigned)};

    dict = monthlyTotal.OrderBy(p => p.Month).ToDictionary(n => n.Month.ToString("MMM"), n => n.Total);
}

No loop needed!

不需要循环!

#3

If you are not worried about missing months with no data,then I had a similar problem where I did the following : (translated to your variables)

如果你不担心缺少没有数据的月份,那么我遇到了类似的问题,我做了以下几点:(翻译成你的变量)

  DateTime startPeriod = 
     new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1).AddMonths(-6);

  var query1 = from a in db.Accounts where a.Date_Assigned >= startPeriod
 group a by new { a.Date_Assigned.Year  ,a.Date_Assigned.Month  } into result
 select new
 {
     dt = new DateTime( result.Key.Year, result.Key.Month , 1),
     MonthTotal = result.Sum(i => i.Amount_Assigned)
 } ;             

  var dict = query1.OrderBy(p=> p.dt).ToDictionary(n => n.Dt.ToString("MMM") , n => n.MonthTotal );

#1