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)
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))
});