The LINQ expression 'GroupByShaperExpression: could not be translated while using GROUPBY in LINQ query

Question

I want to Sum each column in table who looks like this: enter image description here

Model of this table is called TotalConfiguration and looks like this:

public class TotalConfiguration
    {
        [Key]
        public int idTotalConfiguration { get; set; }
        [ForeignKey("Projects")]
        public Guid IdProjects { get; set; }
        public int January { get; set; }
        public int February { get; set; }
        public int March { get; set; }
        public int April { get; set; }
        public int May { get; set; }
        public int June { get; set; }
        public int July { get; set; }
        public int August { get; set; }
        public int September { get; set; }
        public int October { get; set; }
        public int November { get; set; }
        public int December { get; set; }
        public Projects Projects { get; set; }
    }

Model of this table called Projects looks like this:

public class Projects
    {
        [Key]
        public Guid IdProject { get; set; }
        [Required]
        public string OwnerName { get; set; }
        [Required]
        public string ProjectAdress1 { get; set; }
        public string ProjectAdress2 { get; set; }
        [Required]
        public string ProjectPostcode { get; set; }
        [Required]
        public string ProjectCity { get; set; }
        public TotalConfiguration TotalConfiguration { get; set; }
}

And Model of this table called ProjectsUser looks like this:

 public class ProjectsUser
    { 
        [Key]
        public int IdProjectsUser { get; set; }
        [ForeignKey("User")]
        public Guid IdUser { get; set; }
        [ForeignKey("Projects")]
        public Guid IdProject { get; set; }
        [Required]
        public virtual User User { get; set;}
        [Required]
        public virtual Projects Projects { get; set; }
    }

In my Controller i want to sum all column to object of TotalConfiguration. My code:

public IActionResult GetTotalConfiguration(Guid userId)
{
    TotalConfiguration totalConfiguration3 = _context.ProjectsUser
        .Where(x => x.IdUser == userId)
        .GroupBy(x => x.IdUser)
        .Select(x => new TotalConfiguration
        {
            January = x.Sum(x => x.Projects.TotalConfiguration.January),
            February = x.Sum(x => x.Projects.TotalConfiguration.January),
            March = x.Sum(x => x.Projects.TotalConfiguration.January),
            April = x.Sum(x => x.Projects.TotalConfiguration.January),
            May = x.Sum(x => x.Projects.TotalConfiguration.January),
            June = x.Sum(x => x.Projects.TotalConfiguration.January),
            July = x.Sum(x => x.Projects.TotalConfiguration.January),
            August = x.Sum(x => x.Projects.TotalConfiguration.January),
            September = x.Sum(x => x.Projects.TotalConfiguration.January),
            October = x.Sum(x => x.Projects.TotalConfiguration.January),
            November = x.Sum(x => x.Projects.TotalConfiguration.January),
            December = x.Sum(x => x.Projects.TotalConfiguration.January),
        })
        .FirstOrDefault();

If i run this code, i got error :

System.InvalidOperationException: 'The LINQ expression 'GroupByShaperExpression:
KeySelector: p.IdUser, 
ElementSelector:EntityShaperExpression: 
    EntityType: ProjectsUser
    ValueBufferExpression: 
        ProjectionBindingExpression: EmptyProjectionMember
    IsNullable: False

    .Sum(x => x.Projects.TotalConfiguration.January)' 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.'

While i save result to IEnumerable i doesn't got this error (but i want to save result as TotalConfiguration object)

Answer 1

You cannot use navigation properties after GroupBy.

Query should be rewritten:

var query =
    from pu in _context.ProjectsUser
    let tc = pu.TotalConfiguration
    group tc by pu.IdUser into g
    select new TotalConfiguration
    {
        idTotalConfiguration = g.Key,
        
        January = x.Sum(x => x.January),
        February = x.Sum(x => x.February),
        March = x.Sum(x => x.March),
        April = x.Sum(x => x.April),
        May = x.Sum(x => x.May),
        June = x.Sum(x => x.June),
        July = x.Sum(x => x.July),
        August = x.Sum(x => x.August),
        September = x.Sum(x => x.September),
        October = x.Sum(x => x.October),
        November = x.Sum(x => x.November),
        December = x.Sum(x => x.December)
    };