错误：在调用方法以在linq语句中设置属性时，linq查询中不支持异常 [英] Error: not supported exception in linq query while calling a method to set properties in linq statement

问题描述

我在调用将值设置为匿名类型的方法时得不到支持异常。我能理解为什么会这样，但有没有其他办法可以实现我想做的事情。请看下面的代码。



我尝试过：

 private static double CalculateAvgDayFromDates（DateTime date，DateTime productionStartDate）
 {
 return（Convert.ToDateTime（date.ToShortDateString（）） -  Convert.ToDateTime（productionStartDate.ToShortDateString（）） ）.TotalDays + GetPercentageOfDay（date.TimeOfDay）; 
 
} 
 public static void GetDirectorDetails（）
 {
 try 
 {
 var sdt = new DateTime（2018,2,3） ; 
 var edt = new DateTime（2019,2,1）; 
 
使用（var db = new MyDbContext（））
 {
 var ManagerDetails =来自db.Projects中的p 
让projHOurs = db.ProjectHours.Where（ph => ph.ProjectID == p.ID）.Sum（h => h.DayHours）
 // let lDate = CalculateAvgDayFromDates（p.ReadyToLaunchDate.Value，p.ProductionStartDate.Value）
 //让sLDate = CalculateAvgDayFromDates（p.ProductionStartDate.Value.AddDays（6），p.ProductionStartDate.Value）
 where（p.ProductionStartDate> sdt&& p.ProductionStartDate< = edt）
 where（p.ProductionCompleteDate！= null&& p.ProductType == 1&& p.Active）
 
 select new 
 {
 Manager = p.Manager，
 SalesEmployee = p.SalesEmployee，
 StoreNumber = p.StoreNumber，
 LaunchDate = CalculateAvgDayFromDates（p.ReadyToLaunchDate.Value，p.ProductionStartDate.Value ），
 SoftLaunchDate = CalculateAvgDayFromDates（p.ProductionStartDate.Value.AddDays（6），p.ProductionStartDate.Value），
 totalWorkedHours = projHOurs 
 
}; 
 
 var查询=来自导演详细信息中的d 
组d由d.director导入g 
选择新
 {
 manager = g.Key，
 storeCounts = g.Count（），
 avgWorkedHours = g.Average（x => x.totalWorkedHours），
 avgLaunchDays = g.Average（x => x.LaunchDate），
 avgSoftLaunchDays = g.Average（x => x.SoftLaunchDate）
}; 
 
 foreach（查询中的var d）
 {
 System.Diagnostics.Debug.WriteLine（$dir：{d.manager}，storeCounts：{d.storeCounts}， avgLaunchDays：{d.avgLaunchDays}，avgSoftLaunchDays：{d.avgSoftLaunchDays}，avgWorkedHours：{d.avgWorkedHours}）; 
 
} 
} 
} 
 catch（例外情况）
 {
 
 string s = ex.Message; 
 System.Diagnostics.Debug.WriteLine（s）; 
} 
} 

解决方案

dir：{d.manager}，storeCounts：{d.storeCounts} ，avgLaunchDays：{d.avgLaunchDays}，avgSoftLaunchDays：{d.avgSoftLaunchDays}，avgWorkedHours：{d.avgWorkedHours}）;

}
}
}
catch（例外情况）
{

string s = ex.Message;
System.Diagnostics.Debug.WriteLine（s）;
}
}

实体框架不知道如何转换 CalculateAvgDayFromDates SQL查询的方法。



在执行计算之前，您需要将记录拉入内存。

  var  managerQuery = 来自 p   db.Projects 
 让 projHOurs = db.ProjectHours.Where（ph = >  ph.ProjectID == p.ID）.Sum（h = >  h.DayHours）
 其中（p.ProductionStartDate >  sdt&& p.ProductionStartDate <  = edt ）
 其中（p.ProductionCompleteDate！=  null && p.ProductType ==  1 && p.Active）
 选择  new  
 {
 p.Manager ，
 p.SalesEmployee，
 p.StoreNumber，
 p.ReadyToLaunchDate，
 p.ProductionStartDate，
 projHOurs，
}; 
 
  var  ManagerDetails = 来自 r  in  managerQuery.AsEnumerable（）
  select   new  
 { 
 r.Manager，
 r.SalesEmployee，
 r.StoreNumber，
 LaunchDate = CalculateAvgDayFromDates（r.ReadyToLaunchDate.Value，r.ProductionStartDate.Value），
 SoftLaunchDate = CalculateAvgDayFromDates（r.ProductionStartDate.Value.AddDays（ 6 ），r.ProductionStartDate.Value），
 totalWorkedHours = r.projHOurs，
} ; 

AsEnumerable（）调用强制在内存中执行以下LINQ方法，而不是尝试转换他们到SQL。

Hi,

I am getting not supported exception while calling methods to set values to anonymous type. I can understand why is it but is there any other way to achieve what I am trying to do. please look at the code below.

What I have tried:

private static double CalculateAvgDayFromDates(DateTime date, DateTime productionStartDate)
        {
            return (Convert.ToDateTime(date.ToShortDateString()) - Convert.ToDateTime(productionStartDate.ToShortDateString())).TotalDays + GetPercentageOfDay(date.TimeOfDay);
           
        }
        public static void GetDirectorDetails()
        {
            try
            {
                var sdt = new DateTime(2018, 2, 3);
                var edt = new DateTime(2019, 2, 1);

                using (var db = new MyDbContext())
                {
                    var ManagerDetails = from p in db.Projects
                                          let projHOurs = db.ProjectHours.Where(ph => ph.ProjectID == p.ID).Sum(h => h.DayHours)
                                          //let lDate = CalculateAvgDayFromDates(p.ReadyToLaunchDate.Value, p.ProductionStartDate.Value)
                                          //let sLDate = CalculateAvgDayFromDates(p.ProductionStartDate.Value.AddDays(6), p.ProductionStartDate.Value)
                                          where (p.ProductionStartDate > sdt && p.ProductionStartDate <= edt)
                                          where (p.ProductionCompleteDate != null && p.ProductType == 1 && p.Active)
                                         
                                          select new
                                          {
                                              Manager = p.Manager,
                                              SalesEmployee = p.SalesEmployee,
                                              StoreNumber = p.StoreNumber,
                                              LaunchDate = CalculateAvgDayFromDates(p.ReadyToLaunchDate.Value, p.ProductionStartDate.Value),
                                              SoftLaunchDate = CalculateAvgDayFromDates(p.ProductionStartDate.Value.AddDays(6), p.ProductionStartDate.Value),
                                              totalWorkedHours = projHOurs

                                          };

                    var Query = from d in directorDetails
                                  group d by d.director into g
                                  select new
                                  {
                                      manager = g.Key,
                                      storeCounts = g.Count(),
                                      avgWorkedHours = g.Average(x => x.totalWorkedHours),
                                      avgLaunchDays = g.Average(x => x.LaunchDate),
                                      avgSoftLaunchDays = g.Average(x => x.SoftLaunchDate)
                                  };
                   
                    foreach (var d in Query)
                    {
                        System.Diagnostics.Debug.WriteLine($"dir:{d.manager}, storeCounts: {d.storeCounts}, avgLaunchDays: {d.avgLaunchDays}, avgSoftLaunchDays:{d.avgSoftLaunchDays}, avgWorkedHours: {d.avgWorkedHours}");
                      
                    }
                }
            }
            catch (Exception ex)
            {

                string s = ex.Message;
                System.Diagnostics.Debug.WriteLine(s);
            }
        }

解决方案

"dir:{d.manager}, storeCounts: {d.storeCounts}, avgLaunchDays: {d.avgLaunchDays}, avgSoftLaunchDays:{d.avgSoftLaunchDays}, avgWorkedHours: {d.avgWorkedHours}"); } } } catch (Exception ex) { string s = ex.Message; System.Diagnostics.Debug.WriteLine(s); } }

Entity Framework doesn't know how to convert your CalculateAvgDayFromDates method to a SQL query.

You'll need to pull the records into memory before performing that calculation.

var managerQuery = from p in db.Projects
                   let projHOurs = db.ProjectHours.Where(ph => ph.ProjectID == p.ID).Sum(h => h.DayHours)
                   where (p.ProductionStartDate > sdt && p.ProductionStartDate <= edt)
                   where (p.ProductionCompleteDate != null && p.ProductType == 1 && p.Active)
                   select new
                   {
                       p.Manager,
                       p.SalesEmployee,
                       p.StoreNumber,
                       p.ReadyToLaunchDate,
                       p.ProductionStartDate,
                       projHOurs,
                   };

var ManagerDetails = from r in managerQuery.AsEnumerable()
                     select new
                     {
                         r.Manager,
                         r.SalesEmployee,
                         r.StoreNumber,
                         LaunchDate = CalculateAvgDayFromDates(r.ReadyToLaunchDate.Value, r.ProductionStartDate.Value),
                         SoftLaunchDate = CalculateAvgDayFromDates(r.ProductionStartDate.Value.AddDays(6), r.ProductionStartDate.Value),
                         totalWorkedHours = r.projHOurs,
                     };

The AsEnumerable() call forces the following LINQ methods to be executed in memory, rather than trying to convert them to SQL.

这篇关于错误：在调用方法以在linq语句中设置属性时，linq查询中不支持异常的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！