0

This is my SQL query:

SELECT AspNetRoles.Id as RoleId, AspNetUsers.UserName, AspNetRoles.Name As RoleName
FROM AspNetUsers 
LEFT JOIN AspNetUserRoles ON  AspNetUserRoles.UserId = AspNetUsers.Id 
LEFT JOIN AspNetRoles ON AspNetRoles.Id = AspNetUserRoles.RoleId

I'm using it to show user and roles in my project (Default Application setup Visual studio community 2019). It works fine but I rather use linq because of the code. Can anyone be of help?

GvB
  • 1
  • 2
  • Are you using Entity Framework or some kind of other ORM in your project? – trashr0x May 20 '20 at 15:07
  • If you are using EntityFramework or LINQ2SQL, the `GroupJoin` method can be used to create `LEFT JOIN`s. – JamesFaix May 20 '20 at 15:12
  • Perhaps my [SQL to Linq Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) might help you. If you are using EF (which one?) then you should use a navigation property. – NetMage May 20 '20 at 19:06
  • Yes I'm using EF EntityFramework, Version=6.0.0.0 – GvB May 21 '20 at 09:16

1 Answers1

0

Try the following code:

using System.Linq;
using Microsoft.AspNetCore.Mvc;

using SO.Data;

namespace SO.Controllers
{
    public class ResultDto
    {
        public string RoleId { get; set; }
        public string UserName { get; set; }
        public string RoleName { get; set; }
    }
    public class HomeController : Controller
    {
        private readonly ApplicationDbContext _context;

        public HomeController(ApplicationDbContext context)
        {
            _context = context;
        }
        public IActionResult Index()
        {
            var result = (from u in _context.Users
                    join ur in _context.UserRoles on u.Id equals ur.UserId into uur
                    from uurD in uur.DefaultIfEmpty()
                    join r in _context.Roles on uurD.RoleId equals r.Id into uurDr
                    from uurDrD in uurDr.DefaultIfEmpty()
                    select new ResultDto {RoleId = uurD.RoleId, RoleName = uurDrD.Name, UserName = u.UserName})
                .ToList();

            return View(result);
        }
    }
}

Here is the generated SQL:

 SELECT [ur].[RoleId], [r].[Name] AS [RoleName], [u].[UserName]
      FROM [AspNetUsers] AS [u]
      LEFT JOIN [AspNetUserRoles] AS [ur] ON [u].[Id] = [ur].[UserId]
      LEFT JOIN [AspNetRoles] AS [r] ON [ur].[RoleId] = [r].[Id]
Jakub Kozera
  • 3,141
  • 1
  • 13
  • 23
  • Hi this gives the following result: IdentityRole does not contain a definition for and no accessible extension method accepting a first argument of type could be found. If I change the ur.UserId into ur.Users then the join clause is incorrect – GvB May 21 '20 at 09:08