具有多对多关系的EF6精选

Question

我是EF的新手，试图检索需要多对多关系的结果。

这就是模式

​

这是我试图通过LINQ获得的SQL版本

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

这是上面的结果视图

​

这就是View_Roles表在我的上下文文件中的显示方式

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

这是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; }
    }  

这是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; }

}

这是我正在尝试的LINQ，但是因为我不知道如何使用View_Roles表，所以它肯定得不到我需要的东西。

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

我认为实现你正在尝试的目标的一种方法是执行以下操作：

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

在您的例子中，连接表不是直接映射的，它是隐藏的，所以获取所需数据的一种解决方案是使用SelectMany扩展方法。首先将条件应用于查询的to端之一，在我的示例中是Versions，然后应用SelectMany，这将在两个表之间生成内连接，并使一个集合中的结果扁平化。

更新

我认为问题是因为Version和View在你的数据库中并不是直接相关的，所以你要做一个显式的内部连接：

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