"Processing of the LINQ expression 'GroupByShaperExpression" Got error after upgrade to ef core . 5

Question

My project was .net core 2.2 and upgraded to .net 5 and EF 5.0 My last query was

 public IList> GetAllGroupActive()
 {
     return (from b in _db.UsefullLinks
             group b by b.UseFullLinkType.Name into g
             select new Univer.Business.Model.Group 
             { 
                 Key = g.Key, 
                 Values = g 
             }).ToList();        
 }

My Models

public class UsefullLink
{
    public int Id { get; set; }

    public string Name { get; set; }


    public bool Active { get; set; }

    public DateTime CreateDate { get; set; }

    public string Image { get; set; }

    public int LinkTypeId { get; set; }

    public LinkType LinkType { get; set; }
  
    public int Priority { get; set; }
    
...       
 
    public string Description { get; set; }
  

    public int UseFullLinkTypeId { get; set; }
   
    public UseFullLinkType UseFullLinkType { get; set; }
}

public class UseFullLinkType
{
    public int Id { get; set; }
    [Required, DataType(DataType.Text)]
    public string Name { get; set; }
  
    [DataType(DataType.MultilineText)]
    public string Description { get; set; }

    [Required, DataType(DataType.Date)]
    public DateTime InputDate { get; set; }

    public int Priority { get; set; }

   public ICollection UsefullLinks { get; set; }
}

my view

@using Univer.Business.Model
@model List>
@{
   ViewData["Title"] = "ServiceTebbedTable";
}



    
         service table
        

            @foreach (var group in Model)
            {
                 @group.Key
                
                foreach (var link in group.Values)
                {
                    
                        @if (link.Image != "1")
                        {
                            
                        }
                        else
                        {
                            
                        }
                         @link.Name

                    
                }
            }

after run project I got this error

InvalidOperationException: Processing of the LINQ expression 'GroupByShaperExpression: KeySelector: u.Name, ElementSelector:EntityShaperExpression: EntityType: UsefullLink ValueBufferExpression: ProjectionBindingExpression: EmptyProjectionMember IsNullable: False

UPDATE1: and for group I have this in Univer.Business.Model

public class Group
{
        public k Key;
        public IEnumerable Values;
}

Update2 this is constructor for my Repository public class UsefullLinkRepository : IUsefullLinkRepository { private readonly UniverContext _db;

    public UsefullLinkRepository(UniverContext db)
    {
        _db = db;
    }
}

and I change it method to

var data = from b in _db.UsefullLinks.AsEnumerable()
                   join c in _db.UseFullLinkTypes on b.UseFullLinkTypeId equals c.Id
                   group b by b.UseFullLinkType.Name into g
                   select g;

        return data.Select(g => new Group
        {
            Key = g.Key,
            Values = g
        }).ToList();

After run Update2 I got this error

InvalidOperationException: There is already an open DataReader associated with this Connection which must be closed first.

user_10646316 · Answer

Problem here that EF 2.x silently evaluates non translatable query on the client side. It is a bug that EF Core 5 do not throw correct exception in this case.

Consider to rewrite your query

var grouped = from b in _db.UsefullLinks.AsEnumerable()
              group b by b.UseFullLinkType.Name into g
              select g;

return grouped.Select(g => new Univer.Business.Model.Group 
    { 
        Key = g.Key, 
        Values = g // this assignment is not translatable
    }).ToList();

user_11398810 · Answer

Change like below:

var data = from b in _db.UsefullLinks.AsEnumerable()
                join c in _db.UseFullLinkType.AsEnumerable() on b.UseFullLinkTypeId equals c.Id
                group b by b.UseFullLinkType.Name into g
                select g;

return data.Select(g => new Group
{
    Key = g.Key,
    Values = g 
}).ToList();

Result:

user_386253 · Answer

 return (from b in _db.UsefullLinks
            group b by b.UseFullLinkType.Name into g
            select new Univer.Business.Model.Group 
            { Key = g.Key, Values = g }).ToList();

Here, does UsefullLinks collection has 'Key' as a column? You may want to use g.Name instead of g.Key

user_6052905 · Answer

and thanks for you help, after your help I got many Ideas, and I had many unknown think about that, another side I like Linq Lambda, I one of suggestion, I got that add "MultipleActiveResultSets=true" but I think I had not problem before upgrade my project, it mean this problem come from my query and in the next time if I want edit my another query, maybe I send many connection to database and it's not good for performance, another thinks was that I use external class from business layer of my project, it was from .net core 2.2 and in .net core 5.0 I try to use better approach, and the result was, I remove "MultipleActiveResultSets=true" from my connection string and cut use of group class from another layer of project, the final Query become in my repository :

  return _db.UsefullLinks
            .Join(_db.UseFullLinkTypes.AsEnumerable(),
                  ul => ul.UseFullLinkTypeId,
                  ult => ult.Id,
                  (ul, ult) => new { ul = ul, ult = ult }).AsEnumerable()
            .GroupBy(
            x=>x.ul.UseFullLinkType.Name,x=>x.ul )
            .Select (g=>g).ToList();

And in my View I had small change

@model List>
@{
 ViewData["Title"] = "ServiceTebbedTable";
}




    
         Service Table
        

            @foreach (var group in Model)
            {
                 @group.Key
                
                foreach (var link in group)
                {

                    

                        @if (link.Image != "1")
                        {
                            
                        }
                        else
                        {
                            
                        }
                         @link.Name

                    

                   
                }
            }

"Processing of the LINQ expression 'GroupByShaperExpression" Got error after upgrade to ef core . 5

4 Answers