与Entity Framework中的联结表有多对多的关系？

I'm trying to create a many-to-many relationship in Entity Framework (code first), according to the following post: Database design for limited number of choices in MVC and Entity Framework?

我正在尝试在Entity Framework(代码优先)中创建多对多关系,根据以下帖子:MVC和Entity Framework中有限数量的选择的数据库设计?

However, I can't get it to work properly, and I'm sure I'm doing something very simple the wrong way. Here's the diagram I have no from my attempts:

但是,我无法让它正常工作,而且我确信我的做法非常简单。这是我尝试过的图表:

The point of the junction table is that I need to have an extra property, Level, in the relationship, so I can't just go with a direct relationship between Consultant and Program. I added the ConsultantProgramLink entity manually in the designer, and then added associations to Program and Consultant respectively, selecting to add a FK for each, and then made them both primary keys. But when I do it like this it doesn't work as I expected:

联结表的要点是我需要在关系中有一个额外的属性Level,所以我不能只顾问顾问和程序之间的直接关系。我在设计器中手动添加了ConsultantProgramLink实体,然后分别向Program和Consultant添加了关联,选择为每个添加FK,然后将它们作为主键。但是,当我这样做时,它不能像我预期的那样工作:

If I had done a direct association between Consultant and Program, I would have been able to refer to, say, Consultant.Programs in my code. But that doesn't work now with the junction table. Is there any way to remedy this, or do I always have to go through the junction property (Consultant.ConsultantProgramLink.Programs)? In any case, even if I do try to go through the junction property it doesn't help. I can do Consultant.ConsultantProgramLink in my code, but another dot doesn't give me the navigation property Programs (which for some reason also became simply Program, why? Can I just rename them if I eventually get access to them at all?).

如果我在顾问和程序之间建立了直接关联,那么我可以在我的代码中引用Consultant.Programs。但现在使用联结表不起作用。有没有办法解决这个问题,或者我是否总是需要通过交汇点属性(Consultant.ConsultantProgramLink.Programs)?在任何情况下,即使我尝试通过交汇处属性也无济于事。我可以在我的代码中做Consultant.ConsultantProgramLink,但是另一个点没有给我导航属性程序(由于某种原因,它也变成了简单的程序,为什么?如果我最终可以访问它们,我可以重命名它们吗?) 。

So what am I doing wrong? Why can't I access the properties through dot notation in my code?

那么我做错了什么?为什么我不能通过代码中的点表示法访问属性?

1 个解决方案

#1

Once you model a junction table as an entity you indeed lose direct many-to-many relation between Consultant and Program. That is how it works. You will either have direct many-to-many relation or additional properties in the junction table. Not both. If you want both you can try creating custom Programs property on Consultant and use linq query to get related programs:

将联结表建模为实体后,确实会失去顾问与程序之间的多对多关系。这就是它的工作原理。您将在联结表中具有直接的多对多关系或其他属性。不是都。如果您想要两者,您可以尝试在Consultant上创建自定义程序属性,并使用linq查询来获取相关程序:

public IEnumerable<Program> Programs
{
    get
    {
        return this.ConsultantProgramLinks.Select(l => l.Program);   
    }
}

The example is also the explanation of your last problem. You can't have Program property on ConsultantProgramLink because it is a collection of related entities, not single entity (it should be called ConsultantProgramLinks). The property in ConsultantProgramLink entity is called simply Programbecause it represents single entity not collection.

这个例子也解释了你的上一个问题。您不能在ConsultantProgramLink上拥有Program属性,因为它是相关实体的集合,而不是单个实体(应该称为ConsultantProgramLinks)。 ConsultantProgramLink实体中的属性简单地称为Programbe,因为它表示单个实体而不是集合。

Edit:

If you need each Program to be automatically associated with each Consultant you must enforce it when you are going to create new Program. Having junction table exposed as separate entity will probably allow you achieving it easily:

如果您需要自动将每个程序与每个顾问相关联,则必须在创建新程序时强制执行该程序。将联结表暴露为单独的实体可能会让您轻松实现:

var program = new Program();
...
context.Programs.AddObject(program);

var ids = from c in context.Consultants
          select c.Id;

foreach (var id in ids)
{
    var link = new ConsultantProgramLink
        {
            ConsultantId = id,
            Program = program
        };
    context.ConsultantProgramLinks.AddObject(link);
}

context.SaveChanges();

If you add new Consultant you will have to create links to all programs in the same way.

如果添加新顾问,则必须以相同方式创建指向所有程序的链接。

The disadvantage is that if you have for example 1000 consultants this construct will create 1001 database inserts where each insert will be executed in separate roundtrip to the database. To avoid it the only option is either use stored procedur or trigger on Program table.

缺点是,如果你有1000个顾问,这个构造将创建1001个数据库插入,其中每个插入将在单独的往返数据库中执行。要避免它,唯一的选择是在Program表上使用存储过程或触发器。