Linking separate attributes to the same table in Entity Framework / .NET MVC

I have spent hours trying to find an answer to this, and have come up empty.

I am completely new to .NET and MVC - I completed the MvcMusicStore tutorial and have now started working on a more complex - but similar project. I only say this to explain my low level of experience and knowledge in this area, and hope that any responses can be considerate of this.

Anyway, the problem I am having is that I have one table "Fixtures" which has two attributes "TeamId1" and "TeamId2" these both map to another table "Team".

What I want is for each fixture to also include the Team data for both Team1 AND Team2. I have been using the Include method of ObjectQuery so far to perform similar operations with no problems.

However, I found that .NET or MVC or Entity Framework or whatever, w开发者_运维百科asn't happy when I did this. After MUCH experimentation, I figured that it will only work when the Foreign Key has the same name as the Table it refers to. Basically my attribute must have the name "TeamId" or it just won't work. This wouldn't be a problem if I didn't have another attribute which had to map to this table as well. I obviously can't have two attributes with the same name in a table. So I am stuck.

I can only assume that it is possible to map to a table using an attribute name other than "TeamId" but I can't find any instruction/previous post/documentation to explain how to do this.

I have considered the possibility of breaking my Fixture data up and having a Fixture table then a separate table to hold the team ids, scores etc, however this seems a bit of overkill given that each fixture will never have more than two teams. So I am calling this Plan B.

I'd rather not sidestep this problem, if possible - as it doesn't seem unreasonable for it to be possible using EF and ObjectQuery. I am also aware that I can create the queries manually, specifying the joins. Unfortunatley my experience of LINQ is limited and the multiple joins on one table hasn't worked out every time I've tried it so far.

So what I really need to know is if there is some setting or code I can use to achieve the above without resorting to LINQ or restructuring my Fixtures table.

Edit:

This image shows the relevant models. As it stands: this works, however queries obviously only return one Team Instance linked to the "TeamId1" column. The commented code shows what I need to also have in the model (as far as my limited previous experience has shown). So that my Model holds two separate teams linked to two separate columns.

I think what you're looking for is some custom mappings rather than the 'by convention' mappings that are supplied with the framework.

If you decorate and add some properties to your Fixture it should work fine.

Like this:

public int TeamId1 { get; set; }
public int TeamId2 { get; set; }

[ForeignKey("TeamId1")]
public Team Team1 { get; set; }

[ForeignKey("TeamId2")]
public Team Team2 { get; set; }