I have a query that works if I'm using Entity Framework for .NET Framework:

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();

But when I use it in Entity Framework Core on .NET Core I get an exception:

Processing of the LINQ expression 'GroupByShaperExpression: KeySelector: CONVERT(date, c.Created), ElementSelector:EntityShaperExpression: EntityType: CardPlacement ValueBufferExpression: ProjectionBindingExpression: EmptyProjectionMember IsNullable: False ' by 'RelationalProjectionBindingExpressionVisitor' failed. This may indicate either a bug or a limitation in Entity Framework.

Does anybody know a workaround for this?

CodePudding user response：

Try this one:

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                                       
    };

Note that in EF Core 6 your query should work.