使用EF Core的LINQ查询抛出翻译异常

Question

我有一个查询工作，如果我使用实体框架的。net框架:

var results = (from cp in ctx.CardPlacement
.SelectWhere(x => x.Created <= request.Request.EndDate && x.Created >= request.Request.StartDate)
group cp by cp.Created.Date into batch 
let firstCp = batch.First()
select new SummaryResult
{
Id = firstCp.Id,
AltId = firstCp.AltId,
Created = firstCp.Created,                                       
PlacedValue = batch.Sum(x => x.Amount).ToString(),
Placement = (int)firstCp.AltId,
DatePlaced = firstCp.Created.DateTime                                       
}).ToList();

但是当我在。net Core上使用它时，我得到一个异常:

LINQ表达式GroupByShaperExpression的处理:键选择器:转换(日期，c.创建)，ElementSelector: EntityShaperExpression:EntityType: CardPlacementValueBufferExpression:ProjectionBindingExpression: EmptyProjectionMemberIsNullable:假'通过'RelationalProjectionBindingExpressionVisitor'失败。这可能表明实体框架存在错误或限制。

有人知道解决这个问题的方法吗?

Answer 1

试试这个:

var palcements = cp.CardPlacement
.Where(x => x.Created <= request.Request.EndDate && x.Created >= request.Request.StartDate);
var grouped = 
from p in palcements
group p by p.Created.Date into g
select new 
{
CreatedDate = g.Key,
Amount = g.Sum(x => x.Amount)
};
var query =
from d in grouped
from firstCp in palcements
.Where(p => p.Created.Date == d.CreatedDate)
.OrderBy(p => p.Created)
.Take(1)
select new SummaryResult
{
Id = firstCp.Id,
AltId = firstCp.AltId,
Created = firstCp.Created,                                       
PlacedValue = d.Amount.ToString(),
Placement = (int)firstCp.AltId,
DatePlaced = firstCp.Created.DateTime                                       
};

请注意，在EF Core 6中，您的查询应该可以工作。