如何使用实体框架在大型查询中使用外键提高性能

我使用实体框架。下面是我的SQL表的例子:

RPM_Data

RPM_ID  Year    Month   Budget_Code Code_IAM    Actual_Days Direction_ID    Cost
1       2014    1       03-G317     S00140802   0,03125     7               5,5
2       2014    2       01-G001     S00220105   0,01525     6               2,5
3       2014    3       01-G001     S00140802   0,05015     5               10

方向

Id  Label
5   Direction_n1
6   Direction_n2
7   Direction_n3

我需要得到每个"Actual_Days"每Budget_Line, Code_IAM和方向的总和为所有的一年。所以它看起来像这样的一个对象列表:

BudgetCode : "03-G317" 
Code_IAM   : "S00140802" 
Direction  : "Direction_n3"
ActualJan  : 0.325
ActualFeb  : 1.25
ActualMar  : 1.325
ActualApr  : 0.75
ActualMay  : 3.25
ActualJun  : 2.325
ActualJul  : 4.25
ActualAug  : 1.125
ActualSep  : 0.22
ActualOct  : 0.325
ActualNov  : 2.325
ActualDec  : 4.325
Cost       : 12554.25

所以我提出了以下查询:

var directions = db.Direction.ToList();
List<Actual> query = (from r in db.RPM_Data
                    where r.Year == DateTime.UtcNow.Year && lines.Contains(r.Budget_Code)
                    group r by new { r.Budget_Code, r.Code_IAM, r.Direction_ID } into grp
                    select
                        new Actual
                            {
                                BudgetLine = grp.Key.Budget_Code,
                                CodeIam = grp.Key.Code_IAM,
                                Direction = db.Direction.Where(d => d.Id == grp.Key.Direction_ID)
                                                        .Select(d => d.Label)
                                                        .FirstOrDefault(),
                                ActualJan = grp.Sum(x => x.Month == 1 ? x.Actual_Days : 0),
                                ActualFeb = grp.Sum(x => x.Month == 2 ? x.Actual_Days : 0),
                                ActualMar = grp.Sum(x => x.Month == 3 ? x.Actual_Days : 0),
                                ActualApr = grp.Sum(x => x.Month == 4 ? x.Actual_Days : 0),
                                ActualMay = grp.Sum(x => x.Month == 5 ? x.Actual_Days : 0),
                                ActualJun = grp.Sum(x => x.Month == 6 ? x.Actual_Days : 0),
                                ActualJul = grp.Sum(x => x.Month == 7 ? x.Actual_Days : 0),
                                ActualAug = grp.Sum(x => x.Month == 8 ? x.Actual_Days : 0),
                                ActualSep = grp.Sum(x => x.Month == 9 ? x.Actual_Days : 0),
                                ActualOct = grp.Sum(x => x.Month == 10 ? x.Actual_Days : 0),
                                ActualNov = grp.Sum(x => x.Month == 11 ? x.Actual_Days : 0),
                                ActualDec = grp.Sum(x => x.Month == 12 ? x.Actual_Days : 0),
                                Cost = grp.Sum(x => x.Cost)
                            }
                    ).ToList();

但是db.Direction的子查询破坏了我的性能…

所以我尝试先"存储"所有的db.Direction，然后从中获取Id。

var directions = db.Direction.ToList();
// And then in my query 
// ...
Direction = directions.Where(d => d.Id == grp.Key.Direction_ID)
                      .Select(d => d.Label)
                       .FirstOrDefault(),
// ...

但是我得到以下错误:

无法创建类型为"OPManager.DataAccess.Direction"的常量值。在此上下文中只支持基本类型或枚举类型。

我也尝试了没有.ToList()，并得到一个错误。

你能告诉我在我的查询中如何正确处理这个外键吗?

我怎样才能有最好的表现来得到我想要的?

public class Action
{
   [NotMapped]
   public int Direction_ID { get; set; }
}

List<Actual> query = (from r in db.RPM_Data
                    where r.Year == DateTime.UtcNow.Year && lines.Contains(r.Budget_Code)
                    group r by new { r.Budget_Code, r.Code_IAM, r.Direction_ID } into grp
                    select
                        new Actual
                        {
                           BudgetLine = grp.Key.Budget_Code,
                           CodeIam = grp.Key.Code_IAM,
                           //Direction = ... -> leave this property unset.                       
                           Direction_ID = grp.Key.Direction_ID, // but set the id.        
                        }
                    ).ToList();

foreach(var a in query)
{
    a.Direction = directions.Where(d => d.Id == a.Direction_ID)
                     .Select(d => d.Label)
                     .FirstOrDefault();
    // Hides the direction id if necessary.
    a.Direction_ID = 0;
}