Joining multiple tables with group by throws exception in Entity Framework Core

Question

I am developing a project with Entity Framework Core. I am using Code First approach. I have the below entities.

Account

public class Account
    {
        public Account()
        {
            Id = 0;
            IsActive = true;
            AccountRoles = new List<AccountRole>();
        }

        public int Id { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string Email { get; set; }
        public string Mobile { get; set; }
        public string UserName { get; set; }
        public string Password { get; set; }
        public DateTime? LastLogin { get; set; }
        public bool IsActive { get; set; }
        public bool? IsSystemAdmin { get; set; }        
        public string PhotoUrl { get; set; }
        public DateTime? CreateDate { get; set; }        
        public int? OrgId { get; set; }

        public IList<AccountRole> AccountRoles { get; set; }

        [NotMapped]
        public string UserFullName
        {
            get
            {
                return $"{FirstName} {LastName}";
            }
        }
    }

Role

public class Role
    {
        public Role()
        {
            Id = 0;
            IsActive = true;
            AccountRoles = new List<AccountRole>();
        }

        public int Id { get; set; }
        public string Title { get; set; }
        public bool? IsActive { get; set; }
        public int? OrgId { get; set; }   
        public IList<AccountRole> AccountRoles { get; set; }
    }

Account Role public class AccountRole { public AccountRole() { Id = 0; IsActive = true; }

        public int Id { get; set; }
        public int AccountId { get; set; }
        public int RoleId { get; set; }        
        public bool IsActive { get; set; }

        public Account Account { get; set; }
        public Role Role { get; set; }
    }

Now I have written a LINQ query to join and group by to get results of all account information with Roles in comma separate. The query is below:

var userAccount = (from account in _db.Accounts
                                   join accountRole in _db.AccountRoles on account.Id equals accountRole.AccountId into ars
                                   from ar in ars.DefaultIfEmpty()
                                   join role in _db.Roles on ar.RoleId equals role.Id
                                   where
                                      account.UserName == username
                                     && account.Password == password
                                   group new { account, role } by new
                                   {
                                       account.Id,
                                       account.FirstName,
                                       account.LastName,
                                       account.Email,
                                       account.Mobile,
                                       account.UserName,                                       
                                       account.PhotoUrl
                                   } into ag
                                   select new UserAccountInfo
                                   {
                                       AccountId = ag.Key.Id,
                                       FirstName = ag.Key.FirstName,
                                       LastName = ag.Key.LastName,
                                       Email = ag.Key.Email,
                                       Mobile = ag.Key.Mobile,
                                       Username = ag.Key.UserName,                                       
                                       PhotoUrl = ag.Key.PhotoUrl,
                                       Roles = string.Join(",", ag.Select(x => x.role.Title))
                                   }).FirstOrDefault();

When call the API through Postman, I found the below errors. Can anyone help me to solve the problem.

System.InvalidOperationException: Processing of the LINQ expression 'GroupByShaperExpression: KeySelector: new { Id = a.Id, FirstName = a.FirstName, LastName = a.LastName, Email = a.Email, Mobile = a.Mobile, UserName = a.UserName, PhotoUrl = a.PhotoUrl }, ElementSelector:new { account = EntityShaperExpression: EntityType: Account ValueBufferExpression: ProjectionBindingExpression: account IsNullable: False , role = EntityShaperExpression: EntityType: Role ValueBufferExpression: ProjectionBindingExpression: role IsNullable: True } ' by 'RelationalProjectionBindingExpressionVisitor' failed. This may indicate either a bug or a limitation in Entity Framework. See https://go.microsoft.com/fwlink/?linkid=2101433 for more detailed information. at CashFlow.Services.AccountService.ValidateLoginAsync(String username, String password) in D:\Workspace\My Projects\CashFlow\CashFlow-API\SourceCode\Libraries\Services\AccountService.cs:line 77 at Web.Controllers.AccountController.Login(LoginModel model) in D:\Workspace\My Projects\CashFlow\CashFlow-API\SourceCode\Web\Controllers\AccountController.cs:line 51 at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.TaskOfIActionResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments) at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.g__Awaited|12_0(ControllerActionInvoker invoker, ValueTask`1 actionResultValueTask) at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted) at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context) at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted) at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeInnerFilterAsync() --- End of stack trace from previous location where exception was thrown --- at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g__Awaited|19_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted) at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope) at Microsoft.AspNetCore.Routing.EndpointMiddleware.g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger) at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context) at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context) at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(HttpContext context)

It has nothing in common with table joins, the problem (not supported) is the string.Join method — Ivan Stoev
I could swear I answered this same question already this week. Read what Ivan wrote again. You do not need all those joins in the linq code, EF does all that for you. — Nicholas Hunter
If you're using code first, AND you either (1) follow the naming conventions for tables, primary and foreign keys, or (2) use the fluent API and/or data annotations to define the relationships between the tables, THEN Entity Framework will take care of the joins for you. That's kind of the whole point in using Entity Framework. If you're going to hand code the joins in the code, there's really no point using EF. — Nicholas Hunter

mnu-nasir mnu-nasir · Accepted Answer · 2021-04-17T16:15:17

I have written the query in below way and it works fine.

var userAccount = (from account in _db.Accounts
                                   join accountRole in _db.AccountRoles on account.Id equals accountRole.AccountId into ars
                                   from ar in ars.DefaultIfEmpty()
                                   join role in _db.Roles on ar.RoleId equals role.Id
                                   where account.UserName == username && account.Password == password
                                   select new UserAccountInfo
                                   {
                                       AccountId = account.Id,
                                       FirstName = account.FirstName,
                                       LastName = account.LastName,
                                       Email = account.Email,
                                       Mobile = account.Mobile,
                                       Username = account.UserName,
                                       PhotoUrl = account.PhotoUrl,
                                       IsActive = account.IsActive,
                                       Roles = role.Title
                                   }).ToList().GroupBy(x => new
                                   {
                                       x.AccountId,
                                       x.FirstName,
                                       x.LastName,
                                       x.Email,
                                       x.Mobile,
                                       x.Username,
                                       x.PhotoUrl,
                                       x.IsActive
                                   }).Select(y => new UserAccountInfo
                                   {
                                       AccountId = y.Key.AccountId,
                                       FirstName = y.Key.FirstName,
                                       LastName = y.Key.LastName,
                                       Email = y.Key.Email,
                                       Mobile = y.Key.Mobile,
                                       Username = y.Key.Username,
                                       PhotoUrl = y.Key.PhotoUrl,
                                       IsActive = y.Key.IsActive,
                                       Roles = string.Join(",", y.Select(a => a.Roles))
                                   });

Joining multiple tables with group by throws exception in Entity Framework Core

1 Answers