代码之家 › 专栏 › 技术社区 › ManniAT

EF6 M:N关系中的访问键表

entity-framework

ManniAT · 技术社区 · 6 年前

我有一个EF6代码第一模型,有一个n:m关系。课程和兴趣之间的表格有关键这2条道路和这2条道路相互影响。

我用

        modelBuilder.Entity<Course>()
            .HasMany(e => e.Interests)
            .WithMany(e => e.Courses)
            .Map(m => {
                m.ToTable("Interests2Courses");
                m.MapLeftKey("This2Course");
                m.MapRightKey("This2Interest");
            });


        modelBuilder.Entity<Interest>()
            .HasMany(e => e.Courses)
            .WithMany(e => e.Interests)
            .Map(m => {
                m.ToTable("Interests2Courses");
                m.MapLeftKey("This2Interest");
                m.MapRightKey("This2Course");
            });

在我的课程课上

        public virtual ICollection<Interests2Courses> Interests2Courses { get; set; }
    public virtual ICollection<Interest> Interests { get; set; }

这对利息收集很有用。但是,当我尝试像这样访问(加载)链接表时:

Course cO = dbC.Courses.Include(a => a.Interests2Courses).FirstOrDefault(a => a.ShortName == "K1");

我得到一个异常:无效的对象名dbo.interests2courses1

所以我只能访问“simple n:m”或链接表。有没有两种方法都可以使用?

1 回复 | 直到 6 年前

Harald Coppoolse 6 年前

所以每一个 Course 将有零个或更多 Interests 而每一个 Interest 将有零个或更多 Courses .您是对的,在关系数据库中,这是使用连接表实现的。但是,您的实体框架是数据库的抽象,您不需要定义这个连接表:

class Course
{
    public int Id {get; set;}
    ...

    // every Course has zero or more Interests (many-to-many)
    public virtual ICollection<Interest> Interests {get; set;}
}
class Interest
{
    public int Id {get; set;}
    ...

    // every Interest has zero or more Courses(many-to-many)
    public virtual ICollection<Course> Courses{get; set;}
}

为了完整起见,dbContext

class MyDbContext : DbContext
{
    public DbSet<Course> Courses {get; set;}
    public DbSet<Interest> Interests {get; set;}
}

这就是实体框架识别表、表中的列以及多对多关系所需要知道的全部信息。尽管您没有提到连接表,但是实体框架将为您创建一个连接表。

不需要属性,也不需要流畅的API。只有当您对实体框架为您创建的默认标识符不满意时,您才需要FluentAPI。

但是,如果我不能访问连接表,我如何(分组)加入课程和兴趣?

答:不要进行(组-)联接,请使用虚拟ICollections!

给我讲授所有(或某些)课程及其全部(或部分)兴趣

var result = dbContext.Courses
    .Where(course => ...)       // only if you don't want all Courses
    .Select(course => new
    {
        // only select the properties you actually plan to to use
        Id = course.Id,
        Name = course.Name,
        ...

        Interests = course.Interests
            .Where(interest => ...)  // only if you don't want all its Interests
            .Select(interest => new
            {
                 Id = interest.Id,
                 ...
            })
            .ToList(),
    });

实体框架了解您的多对多,并将为您进行适当的组联接。或者你也可以反过来说:“让我对他们的课程感兴趣”

评论后添加

如果连接表不是纯连接表,因为它有一些属性,您需要将其添加到dbContext中。在这种情况下,您必须添加一个表示这个连接项的类。

TODO:创建一个正确描述此连接项所代表内容的名称

请注意,课程和兴趣之间的多对多关系在课程和交叉点之间变为一对多关系。利益关系也由一变为多。

class Course
{
    public int Id {get; set;}
    ...

    // every Course has zero or more JunctionItems (one-to-many)
    public virtual ICollection<JunctionItem> JunctionItems {get; set;}
}
class Interest
{
    public int Id {get; set;}
    ...

    // every Interest has zero or more JunctionItems(one-to-many)
    public virtual ICollection<JunctionItem> JunctionItems{get; set;}
}

新的JuncionItem类:

class JunctionItem
{
    public int Id {get; set;}
    ... // other JunctionItem properties

    // every JunctionItem belongs to exactly one Course (using foreign key)
    public int CourseId {get; set;}
    public virtual Course Course {get; set;}

    // every JunctionItem belongs to exactly one Interest (using foreign key)
    public int InterestId {get; set;}
    public virtual Interest Interest {get; set;}
}

以及dbContext:

class MyDbContext : DbContext
{
    public DbSet<Course> Courses {get; set;}
    public DbSet<Interest> Interests {get; set;}

    public DbSet<JunctionItem> JunctionItems {get; set;}
}

因为您将关系定义为虚拟属性,所以实体框架已经检测到您的一对多关系,所以您不必通知ModelBuilder这些关系。但是,如果您需要:

var junctionEntity = modelBuilder.Entity<JunctionItem>();

// every junctionEntity has one-to-many with Course:
junctionEntity.HasRequired(junction => junction.Course)
   .WithMany(course => course.JunctionEntities)
   .HasForeignKey(junction => junction.CourseId);

与兴趣相似的东西:

junctionEntity.HasRequired(junction => junction.Interest)
    .WithMany(interest => interest.JunctionEntities)
    .HasForeignKey(junction => junction.InterestId);

注意:一个优化是将jointountable.courseid和jointountable.interestid组合成一个复合主键:

junctionEntity.HasKey(junction => new {junction.CourseId, junction.InterestId});

这将禁止两个单独的连接点指向同一个(过程、兴趣)组合:

Course courseA = ...
Interest interestB = ...

// not possible:
JunctionInterest j1 = new JunctionInterest {Course = courseA, Interest = interestB};
JunctionInterest j2 = new JunctionInterest {Course = courseA, Interest = interestB};

如果你想要这个,你需要在你的连接项中有一个单独的主键。

您的查询将类似于您的纯多对多查询,但现在它们是一对多查询。不要使用Join,请使用ICollection:

var result = dbContext.Courses
    .Select(course => new
    {
        Id = course.Id
        Name = course.Name,
        ...

       JunctionItems = course.JunctionItems.Select(junctionItem => new
       {
            ... // junction item properties

            Interests = junctionItem.Interest.Select(interest => new
            {
                 ... // interest properties
            })
            .ToList(),
       })
       .ToList(),
    });
    });