EF6 Select with Many to Many relationship

Question

I am new to EF attempting to retrieve results that require a many to many relationship.

This is the schema

This is the SQL version of what I'm trying to get with LINQ

select v.ID ViewID, ve.Title, ve.VersionID, r.Role, vr.RoleID
from [View] v, Roles r, Versions ve, View_Roles vr
where v.ID = vr.ViewID
and r.ID = vr.RoleID
and ve.ContentStatusID = 2
and ve.ViewID = v.ID
order by r.Role

This is the results view of the above

This is how the View_Roles table is manifested in my context file

modelBuilder.Entity<Role>()
                .Property(e => e.Role1)
                .IsUnicode(false);

    modelBuilder.Entity<Role>()
                .HasMany(e => e.Views)
                .WithMany(e => e.Roles)
                .Map(m => m.ToTable("View_Roles").MapLeftKey("RoleID").MapRightKey("ViewID"));

This is Role.cs

public class Role
    {
        [SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
        public Role()
        {
            Users_Roles = new HashSet<Users_Roles>();
            Views = new HashSet<View>();
        }

        public int ID { get; set; }

        [Column("Role")]
        [Required]
        [StringLength(50)]
        public string Role1 { get; set; }

        public bool IsAdminRole { get; set; }

        public int OrderBy { get; set; }

        [SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
        public virtual ICollection<Users_Roles> Users_Roles { get; set; }

        [SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
        public virtual ICollection<View> Views { get; set; }
    }  

This is View.cs

[Table("View")]
public class View
{
    [SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
    public View()
    {
        PermanentRedirects = new HashSet<PermanentRedirect>();
        Users_Roles = new HashSet<Users_Roles>();
        Versions_View = new HashSet<Versions_View>();
        View_Links = new HashSet<View_Links>();
        View_Localized = new HashSet<View_Localized>();
        View1 = new HashSet<View>();
        ViewGroups = new HashSet<ViewGroup>();
        ViewGroups1 = new HashSet<ViewGroup>();
        languages = new HashSet<language>();
        Roles = new HashSet<Role>();
    }

[StringLength(32)]
public string ID { get; set; }

public bool HideFromNavigationOverride { get; set; }

[StringLength(32)]
public string ParentID { get; set; }

public int? ThemeID { get; set; }

[SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
public virtual ICollection<PermanentRedirect> PermanentRedirects { get; set; }

public virtual Theme Theme { get; set; }

[SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
public virtual ICollection<Users_Roles> Users_Roles { get; set; }

[SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
public virtual ICollection<Versions_View> Versions_View { get; set; }

[SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
public virtual ICollection<View_Links> View_Links { get; set; }

[SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
public virtual ICollection<View_Localized> View_Localized { get; set; }

[SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
public virtual ICollection<View> View1 { get; set; }

public virtual View View2 { get; set; }

[SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
public virtual ICollection<ViewGroup> ViewGroups { get; set; }

[SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
public virtual ICollection<ViewGroup> ViewGroups1 { get; set; }

[SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
public virtual ICollection<language> languages { get; set; }

[SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
public virtual ICollection<Role> Roles { get; set; }

}

This is the LINQ I'm attempting but since I don't understand how to use the View_Roles table it isn't getting what I need, certainly.

return (
                from v in PagesContext.Versions 
                from r in PagesContext.Roles
                where v.ContentStatusID == 2 && r.IsAdminRole == false
                select new RestrictedPage
                {
                    ViewID = v.ViewID,
                    Title = v.Title,
                    RoleID = r.ID,
                    Role = r.Role1,
                    VersionID = v.VersionID
                }
           ).ToList();

Answer 1

I think one way you can achieve what you are trying is doing the following:

var query= PagesContext.Versions.Where(ve=>ve.ContentStatusID == 2)
                                .SelectMany(ve=>ve.View.Roles
                                                  .Where(r=>r.IsAdminRole == false)
                                                  .Select(r=> new RestrictedPage
                                                                   {
                                                                     ViewID = ve.ViewID,
                                                                     Title = ve.Title,
                                                                     RoleID = r.ID,
                                                                     Role = r.Role1,
                                                                     VersionID = ve.VersionID
                                                                   })).ToList();

In your case the junction table is not mapped directly, it's hide, so one solution to get the data related you need is using SelectMany extension method. First apply the condition to one of the to ends of your query, in my example was Versions and then apply SelectMany, that is going generate an inner join between both tables and to flatten the result in one collection.

Update

I think the issue is because Version and View are not really related directly in your DB, so you are going to do an explicit inner join:

var query= PagesContext.Versions.Where(ve=>ve.ContentStatusID == 2)
                                .Join( PagesContext.Views, ve=>ve.ViewId, v=>v.ID,(ve,v)=>v)
                                .SelectMany(v=>v.Roles
                                                  .Where(r=>r.IsAdminRole == false)
                                                  .Select(r=> new RestrictedPage
                                                                   {
                                                                     ViewID = ve.ViewID,
                                                                     Title = ve.Title,
                                                                     RoleID = r.ID,
                                                                     Role = r.Role1,
                                                                     VersionID = ve.VersionID
                                                                   })).ToList();