.NET Core 多对多查询

Question

我在项目模型和用户之间有很多对多的实现。我正在尝试显示登录用户所属的所有项目，但我无法真正正确处理。

用户模型

public class AppUser : IdentityUser
{
public ICollection<UserProject> UserProjects { get; set; }
}

项目模型

public class Project
{
public int ProjectId { get; set; }
public string ProjectName { get; set; }
public ICollection<UserProject> UserProjects { get; set; }
}

用户项目模型

public class UserProject
{
public string UserId { get; set; }
public AppUser AppUser { get; set; }
public int ProjectId { get; set; }
public Project Project { get; set; }
}

我尝试像这样用SQL编写查询，但它不起作用

AppUser user = await userManager.GetUserAsync(HttpContext.User);
var q = (from e in context.Projects
join t in context.UserProjects on e.ProjectId equals t.ProjectId
where t.UserId == user.Id
select e.ProjectName);

我已经设法编写了一个查询来通过 id 获取所有项目及其所有成员，如下所示

var project = context.Projects
.Where(x => x.ProjectId == id)
.Include(x => x.UserProjects)
.ThenInclude(x => x.AppUser)
.First();

但是我无法获得用户所属的所有项目。我感谢我能得到的任何帮助，谢谢！

Answer 1

您可以获取用户所属的所有项目，例如：

var userID = User.Claims.FirstOrDefault(x=>x.Type== ClaimTypes.NameIdentifier).Value;
var projects = _context.Users
.Where(p => p.Id == userID)
.SelectMany(p => p.UserProjects)
.Select(pc => pc.Project).ToList() ;

在多对多关系中：

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
modelBuilder.Entity<UserProject>()
.HasKey(pc => new { pc.UserId, pc.ProjectId });
modelBuilder.Entity<UserProject>()
.HasOne(pc => pc.AppUser)
.WithMany(p => p.UserProjects)
.HasForeignKey(pc => pc.UserId);
modelBuilder.Entity<UserProject>()
.HasOne(pc => pc.Project)
.WithMany(c => c.UserProjects)
.HasForeignKey(pc => pc.ProjectId);
}