c# - EF5 : LINQ to Entities does not recognize the method System. 可空系统。十进制中位数

Question

我首先使用 EF5 代码。当我执行查询时:

var priceIndexQuery = from r in unitOfWork.Repository<StockIndexPriceResult>()
                                      where request.CalculateModel.StockCompanies.Contains(r.StockCompanyId)
                                            && r.StockIndexId == request.CalculateModel.StockIndexId
                                            && r.ResultDate >= dateFiveYearsAgo
                                      orderby r.ResultDate descending
                                      group r by r.ResultDate
                                          into g
                                          select g;

然后:

var query = priceIndexQuery.Select(g => new CalculateAggregateModel
                                                    {
                                                        ResultDate = g.Key,
                                                        Value = g.Median(p => p.Result ?? 0),
                                                        PeriodType = "Day"
                                                    });

                                    resultData = query.ToList();

我在query.ToList(); 上得到了异常(exception)> 目前我所知道的是MSSQL 不能执行Median 函数。有什么办法可以解决吗？

Answer 1

Entity Framework 无法将您的 Median 方法转换为 SQL，因此您需要在执行 Median 方法之前查询数据库。尝试在您的第一个查询之后立即执行 .ToList() 或 .AsEnumerable():

var priceIndexQuery = (from r in unitOfWork.Repository<StockIndexPriceResult>()
                                      where request.CalculateModel.StockCompanies.Contains(r.StockCompanyId)
                                            && r.StockIndexId == request.CalculateModel.StockIndexId
                                            && r.ResultDate >= dateFiveYearsAgo
                                      orderby r.ResultDate descending
                                      group r by r.ResultDate
                                          into g
                                          select g).ToList();