CODEWITHSUNDEEP
entity-framework-core
NovaDev
NovaDev

Reputation: 2971

EF Core 5.0 - The linq expression could not be translated. either rewrite the query

This question has been asked before and answered a few times, but not in a generalized way. Rather, the answers are specific to the asker (which makes sense)...but I use this pattern a lot, and I'm sure others do as well, so I'm looking for a more general answer. I know why this is happening, but I'm not sure what to do about it exactly. the offending line below is

RoleId = ur.First(xu => xu.UserId == x.Id).RoleId,

My understanding is that I have to convert this in some way before the projection, but that's where I'm stuck. Or use Contains() instead of First() (which is an implicit Where() filter), but I'm not sure how to rewrite it so the server will do the query, instead of the client. What is the best way forward?

         var roles = await _roleManager.Roles.ToListAsync();
         var rolesList = roles.Select(x => new {x.Id, x.Name}).ToList();

         var rid = ur.First(xu => xu.UserId == 4).RoleId;
         
         var ur = await _context.UserRoles.ToListAsync();
         var appUsers = await _context.Users
             .Select(x => new AppUserViewModel
             {
                 Id = x.Id,
                 StaffId = x.StaffId,
                 Email = x.Email,
                 UserName = x.UserName,
                 PhoneNumber = x.PhoneNumber,
                 RoleId = ur.First(xu => xu.UserId == x.Id).RoleId,
                 RoleSelectListItems = rolesList.Select(yy => new SelectListItem
                 {
                     Value = yy.Id.ToString(),
                     Text = yy.Name
                 }).ToList()
             })
             .ToListAsync();

Upvotes: 2

Views: 6859

Answers (2)

Svyatoslav Danyliv
Svyatoslav Danyliv

Reputation: 27406

Actually you should work with IQueryable, not lists.

var roles = _roleManager.Roles.AsQueryable();
var ur = _context.UserRoles.AsQueryable();

var appUsers = await _context.Users
    .Select(x => new AppUserViewModel
    {
        Id = x.Id,
        StaffId = x.StaffId,
        Email = x.Email,
        UserName = x.UserName,
        PhoneNumber = x.PhoneNumber,
        RoleId = ur.FirstOrDefault(xu => xu.UserId == x.Id).RoleId,
        RoleSelectListItems = roles.Select(yy => new SelectListItem
        {
            Value = yy.Id.ToString(),
            Text = yy.Name
        }).ToList()
    })
    .ToListAsync();

Upvotes: 4

NovaDev
NovaDev

Reputation: 2971

I got an answer to this. Linq sends itself down to the database, but certain expressions can't be interpreted by the translator which is why it throws this error. So if, for example, your Linq expression references a method to return a value, well, that method can't be passed down to your db, so you have to get that value before you use it in your Linq expression.

So in the example above, RoleId = ur.First(xu => xu.UserId == x.Id).RoleId, it's really two expressions, and the translator doesn't know what to do with that. The first part is "return to me the first object in the userRoles list where the id equals the id of the user" and the second part is "and now give me the RoleId". But you can't send that down - it can't be translated. So I'd have to get the RoleId from the userRoles list as a separate pair of transactions.

Get the role object, then separate out the RoleId value from that object.

Upvotes: 3

Related Questions