1

I am traing to get identity users with roles using GroupJoin

I am getting this exception System.InvalidOperationException: The LINQ expression 'DbSet()

System.InvalidOperationException: The LINQ expression 'DbSet() .GroupJoin( inner: DbSet<IdentityUserRole>() .Join( inner: DbSet(), outerKeySelector: ur => ur.RoleId, innerKeySelector: r => r.Id, resultSelector: (userRole, role) => new { userRole = userRole, role = role }), outerKeySelector: u => u.Id, innerKeySelector: urGroup => urGroup.userRole.UserId, resultSelector: (u, roleGroup) => new { u = u, roleGroup = roleGroup })' could not be translated.

The code that generate exeption is :

var users = _userManeger.Users
                    .GroupJoin(
                        _context.UserRoles
                        .Join(_roleManager.Roles,
                             ur => ur.RoleId, r => r.Id,
                             (userRole, role) => new { userRole, role }),
                        u => u.Id, urGroup => urGroup.userRole.UserId,
                        (u, roleGroup) => new { u, roleGroup })
                    .Select(urg => new SearchResponseDTO
                    {
                        UserName = urg.u.UserName,
                        Email = urg.u.Email,
                        PhoneNumber = urg.u.PhoneNumber,
                        FirstName = urg.u.FirstName,
                        LastName = urg.u.LastName,
                        Roles = urg.roleGroup.Select(c => c.role.Name).ToList()
                    });
        var list = users.ToList();

Can someone help me with this

Svyatoslav Danyliv
  • 21,911
  • 3
  • 16
  • 32
Vaqif Qurbanov
  • 11
  • 1
  • I tried it it dint helped or did not undestand how to aply it – Vaqif Qurbanov Aug 04 '22 at 12:44
  • @VaqifQurbanov Instead of `GroupJoin`, use correlated sub query. And better define and use navigations properties, thus eliminate the need of joins (group or regular) at all. – Ivan Stoev Aug 04 '22 at 12:48
  • Show your model, I will help to rewrite query. – Svyatoslav Danyliv Aug 04 '22 at 12:55
  • @Ivan Stoev It is not my models it is asp.net core identity models . Should i configure it with fluent api ? – Vaqif Qurbanov Aug 04 '22 at 13:35
  • I see. Yeah, [adding navigation properties](https://learn.microsoft.com/en-us/aspnet/core/security/authentication/customize-identity-model?view=aspnetcore-6.0#add-navigation-properties) is unfortunately not easy to do, even though once done, it's the easiest way to query the data in EF Core. Try correlated query then, I've shown that the linked post by Svyatoslav, you'd basically need to familiarize with LINQ *query syntax*, which for joins is much easier than the method syntax you are using in the example. Of course the same can be done with method syntax, but not so easy to explain. – Ivan Stoev Aug 04 '22 at 13:50
  • @IvanStoev ye thanks it worked with sub quarry but why it didnt worked with GroupJoin ? – Vaqif Qurbanov Aug 04 '22 at 13:53
  • 1
    @VaqifQurbanov As I explained in the linked post (and links inside), EF Core team is not willing to add translation for LINQ `GroupJoin` construct, that's why. It is perfectly possible, just they don't want to do it (they don't see a value for doing it since you have the aforementioned "workaround"). – Ivan Stoev Aug 04 '22 at 13:55

0 Answers0