how to create a left join query for a many to many related table with LINQ-CodePudding

I implemented the asp.net identity in my project I have 3 tables(Users,Roles,UserRoles) I want to get a list of users even if they not assigned to any Role yet basically I want to create a left join between User and roles here is my db tables

Users

  public class User:IdentityUser<string>
{
    public string  Name { get; set; }
    public ICollection<UserRoles> UserRoles { get; set; }

}

Roles

   public class Roles:IdentityRole<string>
{
 public ICollection<UserRoles> UserRoles { get; set; }

 public Roles():base(){}

 public Roles(string roleName) : base(roleName){}

}

User Roles

   public class UserRoles:IdentityUserRole<string>
{
    
    public  User User { get; set; }
    public  Roles Role { get; set; }
}

I want to get a table with columns UserName,Mail,Name,RoleName

CodePudding user response：

You do not need explict join. Everything can be retrieved via navigation properties:

var query =
    from u in context.Users
    from ur in u.UserRoles.DefaultIfEmpty()
    select new
    {
        UserName = u.Name,
        Mail = u.Mail,
        RoleName = ur.Role.Name
    }