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 'AsEnumerable', 'AsAsyncEnumerable'

 

错误信息

System.InvalidOperationException : The LINQ expression 'DbSet<Entity>()
    .GroupJoin(
        inner: DbSet<Entity>(), 
        outerKeySelector: b => b.Id, 
        innerKeySelector: c => c.ForeignId, 
        resultSelector: (b, g) => new { 
            Id = b.Id, 
            Name = b.DisplayName, 
            TypesCount = b.navigation
                .AsQueryable()
                .Count() 
        }
    )' 
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 
'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. 
See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

 引用微软官方文档

由于数据库（特别是关系数据库）无法表示一组客户端对象，因此在许多情况下，GroupJoin 不会转换为服务器。 它需要从服务器获取所有数据来进行 GroupJoin，无需使用特殊选择器（下面的第一个查询）。 但如果选择器限制选定的数据，则从服务器提取所有数据可能会导致出现性能问题（下面的第二个查询）。 这就是 EF Core 不转换 GroupJoin 的原因。

一

var query = from b in context.Set<Blog>()
        join p in context.Set<Post>()
            on b.BlogId equals p.PostId into grouping
        select new { b, grouping };

二

var query = from b in context.Set<Blog>()
            join p in context.Set<Post>()
                on b.BlogId equals p.PostId into grouping
            select new { b, Posts = grouping.Where(p => p.Content.Contains("EF")).ToList() };

如有外键关联可以考虑用导航属性完成

没有的话可以先取出来所有数据 ToList

原

var query = from b in dbContext.Company
            join c in dbContext.Types
            on b.Id equals c.ForeignId into g
            select new
            {
                Id = b.Id,
                Name = b.Name,
                Count = g.Count()
            };

使用导航属性

var query = from b in dbContext.Company.Include(x => x.Types)
            select new
            {
                Id = b.Id,
                Name = b.Name,
                Count = b.Types.Count()
            };

 

全部取出来

var query = from b in (await dbContext.Company.ToListAsync())
            join c in dbContext.Types
            on b.Id equals c.ForeignId into g
            select new
            {
                Id = b.Id,
                Name = b.Name,
                Count = g.Count()
            };