GroupBy Expression无法翻译 [英] GroupBy Expression failed to translate

问题描述

//Model
public class Application
{
    [Key]
    public int ApplicationId { get; set; }
    public DateTime CreatedAt { get; set; }
    public DateTime ConfirmedDate { get; set; }
    public DateTime IssuedDate { get; set; }
    public int? AddedByUserId { get; set; }
    public virtual User AddedByUser { get; set; }
    public int? UpdatedByUserId { get; set; }
    public virtual User UpdatedByuser { get; set; }
    public string FirstName { get; set; }
    public string MiddleName { get; set; }
    public string LastName { get; set; }
    public string TRN { get; set; }
    public string EmailAddress { get; set; }
    public string Address { get; set; }
    public int ParishId { get; set; }
    public Parish Parish { get; set; }
    public int? BranchIssuedId { get; set; }
    public BranchLocation BranchIssued { get; set; }
    public int? BranchReceivedId { get; set; }
    public BranchLocation BranchReceived {get; set; }
}

public async Task<List<Application>> GetApplicationsByNameAsync(string name)
{
    if (string.IsNullOrEmpty(name))
        return null;
    return await _context.Application
        .AsNoTracking()
        .Include(app => app.BranchIssued)
        .Include(app => app.BranchReceived)
        .Include(app => app.Parish)
        .Where(app => app.LastName.ToLower().Contains(name.ToLower()) || app.FirstName.ToLower()
        .Contains(name.ToLower()))
        .GroupBy(app => new { app.TRN, app })
        .Select(x => x.Key.app)
        .ToListAsync()
        .ConfigureAwait(false);
}

上面的 GroupBy 表达式在VS Studio中无法编译.我的目标是运行一个查询，该查询按名称过滤结果，该名称包含用户给定的字符串，然后应按相似的 TRN 数字对结果进行分组，以返回要返回到视图的那些应用程序的列表.我想我真的很接近，但似乎无法弄清楚查询的最后一点.任何指导表示赞赏.

The above GroupBy expression fails to compile in VS Studio. My objective is to run a query filtering results by name containing a user given string and then it should group the results by similar TRN numbers returning a list of those applications to return to the view. I think I am really close but just cant seem to figure out this last bit of the query. Any guidance is appreciated.

出现错误

InvalidOperationException: The LINQ expression 'DbSet<Application>
.Where(a => a.LastName.ToLower().Contains(__ToLower_0) || a.FirstName.ToLower().Contains(__ToLower_0))
.GroupBy(
source: a => new {
TRN = a.TRN,
app = a
},
keySelector: a => a)' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync()

更新似乎这肯定是由于自从最近的更新以来.net core 3.x和EF core一起玩的方式发生了变化.我必须通过使用 AsEnumerable()而不是 ToListAsync()将其更改为客户端评估.Steve py给出的其余查询都使用此方法.即使在阅读了文档后，我仍然不知道groupby在LINQ中是如何工作的，因此这对我有很大帮助.但是，将查询带到客户端评估可能会出现性能问题.

UPDATE Seems it is definitely due to a change in how .net core 3.x and EF core play together since recent updates. I had to change it to client evaluation by using AsEnumerable() instead of ToListAsync(). The rest of the query given by Steve py works with this method. I was unaware even after reading docs how the groupby really worked in LINQ, so that has helped me a lot. Taking the query to client side eval may have performance issues however.

推荐答案

基于此:

我想按TRN分组，TRN是一组重复的数字，例如12345，在应用程序"表中可能有许多具有相同序列的记录，而我只希望每组TRN序列中的最新行.

I want to group by TRN which is a repeating set of numbers eg.12345, in the Application table there may be many records with that same sequence and I only want the very latest row within each set of TRN sequences.

我相信这应该可以满足您的需求:

I believe this should satisfy what you are looking for:

return await _context.Application
    .AsNoTracking()
    .Include(app => app.BranchIssued)
    .Include(app => app.BranchReceived)
    .Include(app => app.Parish)
    .Where(app => app.LastName.ToLower().Contains(name.ToLower()) || app.FirstName.ToLower()
    .Contains(name.ToLower()))
    .GroupBy(app => app.TRN)
    .Select(x => x.OrderByDescending(y => y.CreatedAt).First())
    .ToListAsync()
    .ConfigureAwait(false);

GroupBy 表达式应代表您要分组的内容.在您的情况下，TRN.从那里开始选择时， x 代表每个组"，其中包含属于每个TRN的应枚举的应用程序集.因此，我们按降序的CreatedAt日期排序，以使用 First 选择最新的日期.

The GroupBy expression should represent what you want to group by. In your case, the TRN. From there when we do the select, x represents each "group" which contains the Enumarable set of Applications that fall under each TRN. So we order those by the descending CreatedAt date to select the newest one using First.

给个机会.如果不是您想要的，请考虑为您的问题添加一个示例集，并提供所需的输出以及此处产生的输出/错误.

Give that a shot. If it's not quite what you're after, consider adding an example set to your question and the desired output vs. what output / error this here produces.

这篇关于GroupBy Expression无法翻译的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！