我需要将我的sql语句转换为linq我尝试过使用linqer,但没有什么神奇的效果

sgtfey8w  于 2021-08-09  发布在  Java
关注(0)|答案(1)|浏览(301)

这是我的sql查询:

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

我用它来显示项目中的用户和角色(默认应用程序设置VisualStudioCommunity2019)。它工作得很好,但我宁愿使用linq,因为代码。有人能帮忙吗?

bt1cpqcv

bt1cpqcv1#

请尝试以下代码:

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

以下是生成的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]

相关问题