使用DateTime.ParseExact时无法识别的表达式节点:ArrayIndex

Question

我有以下部分查询

var finalResults =
    (from o in outerJoin
    orderby o.timeKey.timeKey.Year_Num, o.timeKey.timeKey.Month, o.Key.Key.PlantId, o.timeKey.timeKey.PhysicalUnitId
    select new
    {
        IndicatorName = IndicatorName,
        LocationName = o.timeKey.timeKey.PlantId,
        GroupingName = o.timeKey.timeKey.PhysicalUnitId,
        Year = o.timeKey.timeKey.Year_Num,
        Month = o.timeKey.timeKey.Month, 
        Numerator = o.timeKey.Key.Derate_Hours != null ? o.timeKey.Key.Derate_Hours ?? 0 : 0,
        Denominator = o.timeKey.timeKey.Hours - o.timeKey.Key.Derate_Hours ?? 0,
        Weight = o.timeKey.timeKey.NetMaximumCapacity,                         
    }).ToList();

除了Month以的格式给我一个月和一年之外，这个查询工作得很好

August 2012

我需要这种格式：

08

我更改了我的代码，如下所示：

var finalResults =
    (from o in outerJoin
    orderby o.timeKey.timeKey.Year_Num, o.timeKey.timeKey.Month, o.Key.Key.PlantId, o.timeKey.timeKey.PhysicalUnitId
    select new
    {
        IndicatorName = IndicatorName,
        LocationName = o.timeKey.timeKey.PlantId,
        GroupingName = o.timeKey.timeKey.PhysicalUnitId,
        Year = o.timeKey.timeKey.Year_Num,
        Month = DateTime.ParseExact(o.timeKey.timeKey.Month.Split(' ')[0], "MMMM", CultureInfo.CurrentCulture).Month,
        Numerator = o.timeKey.Key.Derate_Hours != null ? o.timeKey.Key.Derate_Hours ?? 0 : 0,
        Denominator = o.timeKey.timeKey.Hours - o.timeKey.Key.Derate_Hours ?? 0,
        Weight = o.timeKey.timeKey.NetMaximumCapacity,                         
    }).ToList();

但现在我收到以下错误：

Unrecognized expression node: ArrayIndex

有办法做我想做的事吗？更改数据库中的格式不是一个选项。

以下是整个查询：

protected IList<DataResults> QueryData(HarvestTargetTimeRangeUTC ranges)
{
    using (var context = new DataClassesDataContext(_connectionString))
    {
        context.CommandTimeout = 240;                
        const string IndicatorName = "{DFD88372-FB87-49AC-8576-68DCBE7B00E8}";
        List<string> typeCodes = new List<string>() { "D1", "D2", "D3", "DP", "PD", "DM", "D4" };
        DataResults endItem = new DataResults();
        List<DataResults> ListOfResults = new List<DataResults>();
        var results =
            (from v in context.vDimUnits
             join vf in context.vFactEnergyAllocations on v.UnitKey equals vf.UnitKey
             join vd in context.vDimGadsEvents on vf.GadsEventKey equals vd.GadsEventKey
             join vt in context.vDimTimes on vf.TimeKey equals vt.TimeKey
             where typeCodes.Contains(vd.GadsEventTypeCode) 
                && vt.Year_Num >= ranges.StartTimeUTC.Year 
                && vt.Year_Num <= ranges.EndTimeUTC.Year                        
                && v.PhysicalUnitId != "N/A"
                && v.PhysicalUnitId != "UNK"
                && v.PlantId != "UNK" 
                && v.NercUnitType != "WT"
             group vf by new { v.PlantId, v.PhysicalUnitId, v.NetDependableCapacity, vt.Year_Num, vt.Month } into groupItem
             select new
             {
                 groupItem.Key.Year_Num,
                 groupItem.Key.Month,
                 groupItem.Key.PhysicalUnitId,
                 groupItem.Key.NetDependableCapacity,
                 Derate_Hours = groupItem.Sum(x => (float?)x.AllocatedEnergyMwh / groupItem.Key.NetDependableCapacity),
                 groupItem.Key.PlantId,
                 Unit = groupItem.Count()
             });
        var resultHours =
            (from f in
            (from vt in context.vDimTimes
            from v in context.vDimUnits
            where vt.Year_Num >= ranges.StartTimeUTC.Year
                && vt.Year_Num <= ranges.EndTimeUTC.Year 
                && v.PhysicalUnitId != "N/A" 
                && v.PhysicalUnitId != "UNK" 
                && v.PlantId != "UNK" 
                && v.NercUnitType != "WT"
            select new { v.PlantId, v.PhysicalUnitId, vt.Year_Num, vt.Month, vt.TimeKey, v.NetMaximumCapacity }).Distinct()
             group f by new { f.PhysicalUnitId, f.Year_Num, f.Month, f.PlantId } into groupItem
            select new
            {
                 groupItem.Key.PhysicalUnitId,
                 groupItem.Key.Year_Num,
                 groupItem.Key.Month,
                 groupItem.Key.PlantId,
                 groupItem.First().NetMaximumCapacity,
                 Hours = groupItem.Count()
            });
        var serviceHrsResults =
            (from v in context.vDimUnits
             join vf in context.vFactEnergyAllocations on v.UnitKey equals vf.UnitKey
             join vt in context.vDimTimes on vf.TimeKey equals vt.TimeKey
             join vus in context.vDimUnitStates on vf.UnitStateKey equals vus.UnitStateKey
             where vus.UnitStateType != "Active" 
                && vt.Year_Num >= ranges.StartTimeUTC.Year 
                && vt.Year_Num <= ranges.EndTimeUTC.Year
                && v.NetDependableCapacity != 0 
                && v.PhysicalUnitId != "N/A" 
                && v.PhysicalUnitId != "UNK" 
                && v.PlantId != "UNK" 
                && v.NercUnitType != "WT"
             group vf by new { v.PlantId, vt.Year_Num, vt.Month, v.PhysicalUnitId, v.NetDependableCapacity } into groupItem
             select new
             {
                 groupItem.Key.Year_Num,
                 groupItem.Key.Month,
                 groupItem.Key.PhysicalUnitId,
                 groupItem.Key.NetDependableCapacity,
                 groupItem.Key.PlantId,
                 Unit = groupItem.Count()
             });
        var outerJoin1 =
            (from h in resultHours
             join u in results on new { h.PhysicalUnitId, h.Year_Num, h.Month } equals new { u.PhysicalUnitId, u.Year_Num, u.Month } into outer
             from grouping in outer.DefaultIfEmpty()
             select new { timeKey = h, Key = grouping });
        var outerJoin2 =
            (from h in resultHours
             join s in serviceHrsResults on new { h.PhysicalUnitId, h.Year_Num, h.Month } equals new { s.PhysicalUnitId, s.Year_Num, s.Month } into outer2
             from grouping in outer2.DefaultIfEmpty()
             select new { timeKey = h, Key = grouping });
        var outerJoin =
            (from a in outerJoin1
             join b in outerJoin2 on new { a.timeKey.PhysicalUnitId, a.timeKey.Year_Num, a.timeKey.Month } equals new
             {
                 b.timeKey.PhysicalUnitId,
                 b.timeKey.Year_Num,
                 b.timeKey.Month
             } into outer
             from grouping in outer.DefaultIfEmpty()
             select new { timeKey = a, Key = grouping }).Distinct();

        var finalResults =
            (from o in outerJoin
             orderby o.timeKey.timeKey.Year_Num, o.timeKey.timeKey.Month, o.Key.Key.PlantId, o.timeKey.timeKey.PhysicalUnitId
             select new
             {
                IndicatorName = IndicatorName,
                 LocationName = o.timeKey.timeKey.PlantId,
                 GroupingName = o.timeKey.timeKey.PhysicalUnitId,
                 Year = o.timeKey.timeKey.Year_Num,
                 Month = DateTime.ParseExact(o.timeKey.timeKey.Month.Split(' ')[0], "MMMM", CultureInfo.CurrentCulture).Month,
                 Numerator = o.timeKey.Key.Derate_Hours != null ? o.timeKey.Key.Derate_Hours ?? 0 : 0,
                 Denominator = o.timeKey.timeKey.Hours - o.timeKey.Key.Derate_Hours ?? 0,
                 Weight = o.timeKey.timeKey.NetMaximumCapacity,                         
                 }).ToList();
        for (int counter = 0; counter < finalResults.Count; counter++)
        {
            var item = finalResults[counter];
            endItem = new DataResults();
            ListOfResults.Add(endItem);
            endItem.IndicatorName = IndicatorName;
            endItem.LocationName = item.LocationName;
            endItem.GroupingName = item.GroupingName;
            endItem.Year = item.Year;
            endItem.Month = item.Month.ToString();
            endItem.Numerator = item.Numerator;
            endItem.Denominator = item.Denominator;
            endItem.Weight = item.Weight.Value;                    
        }
        return ListOfResults;
    }
}

Answer 1

我们仍然没有太多上下文，但我还是会猜测一个合适的答案：）

如果你试图在一个被翻译成SQL的查询表达式中做很多工作，你可能会发现最好把查询分成两部分：

• 在SQL中完成的部分，具有尽可能多的筛选，并且只指定您需要的数据，但采用"原始"格式
• 在LINQ to Objects中完成的一部分，它可以使用您需要的任何.NET方法

使用AsEnumerable可以有效地从使用Queryable中的方法切换到使用Enumerable中的方法。所以你可能有：

var sqlQuery = from ...
               orderby ...
               select ...;
var finalQuery = sqlQuery.AsEnumerable().Select(entry => new { 
                     // Call whatever methods you like in here
                 });

这样就避免了要求LINQ提供程序生成SQL来模拟以下内容：

DateTime.ParseExact(o.timeKey.timeKey.Month.Split(' ')[0], "MMMM", 
                    CultureInfo.CurrentCulture).Month,

这也意味着您可以编写助手方法，这些方法可以在查询之外轻松测试：

var finalQuery = sqlQuery.AsEnumerable().Select(entry => new { 
                     Month = ConvertYearMonthToMonthNumber(entry.Month),
                     ...
                 });

请注意，CultureInfo.CurrentCulture很可能是这里的错误的区域性，除非您真的知道存储在数据库中的数据与您的用户处于相同的区域性中，而这可能不是不变的区域性。我认为更有可能你真的想使用不变的文化。。。或者仅使用CCD_ 5。