Entity framework - code first - class with two navigation properties to same class/table

Question

Forgive me if this is a duplicate as I am not sure exactly how to frame the question/search.

I have two MS SQL tables, match and team.

Match
- matchId (PK int)
- homeTeamId (FK int)
- awayTeamId (FK int)

Team
- teamId (PK int)
- Name (nvarchar)

I also have two vb.net classes 'Match' and 'Team'.

Public Class Match
    Public Property MatchId As Integer
    Public Property HomeTeamId As Integer
    Public Property AwayTeamId As Integer
    Public Overridable Property AwayTeam As Team
    Public Overridable Property HomeTeam As Team
End Class

Public Class Team
    Public Property TeamId As Integer
    Public Property Name As String
End Class

I have two relationships set up where Team.TeamId links to both Match.HomeTeamId and Match.AwayTeamId.

When I run my app, I get an exception (innerException) that says

{"Invalid column name 'AwayTeam_TeamId'.

Invalid column name 'HomeTeam_TeamId'.

Invalid column name 'Team_TeamId'."}

I have tried to use the fluent API to map the relationships, but I am afraid that I am not exactly sure how to set this up so that I can get both properties to properly map to the properties in Match.

Using the Fluent API, I have been able to map one property (HomeTeam), while commenting out the 'Match.AwayTeam' property, but not both.

modelBuilder.Entity(Of Match)().HasRequired(Function(m) m.HomeTeam).WithMany(Function(t) t.Matches).HasForeignKey(Function(m) m.HomeTeamId)

How should I set this up so that the Match class contains both the HomeTeam and AwayTeam properties?

Update

I managed to get this working with the following code in the 'OnModelCreating' method of my context.

Thanks to @Slauma for the correction regarding 'WithMany'.

modelBuilder.Entity(Of Match).HasRequired(Function(m) m.HomeTeam).WithMany().Map(Function(t) t.MapKey("HomeTeamId"))
modelBuilder.Entity(Of Match).HasRequired(Function(m) m.AwayTeam).WithMany().Map(Function(t) t.MapKey("AwayTeamId"))

Judo · Answer 1 · 2011-08-05T05:36:10.003

If you are using Entity Framework it will handle the mapping for you and you don't need to create your own foreign key, so just omit the HomeTeamId and AwayTeamId properties from the Match class. Also the AwayTeam and HomeTeam properties need to be ICollection objects.

Public Class Match
Public Property MatchId As Integer 
Public Overridable Property  AwayTeam As ICollection<Team>
Public Overridable Property HomeTeam As ICollection<Team>

End Class

Then when you create a Match object you can just apply a Team object to the AwayTeam and HomeTeam properties. Not sure about the error message though, you will need to provide more details on what action triggered it.

Good call on removing the 'homeTeamId' and 'awayTeamId'. For some reason, I thought those were required to help EF map the relationship. — OverForkOver, Aug 05 '11 at 14:16

score 0 · Answer 2 · answered Aug 05 '11 at 12:03

Have you created a model for EF ? you want to create a Custom Context class inherited with DbContext and should have two collections.

in C#

public class  ProjectXEFDbContext:DbContext
{
    public DbSet<Match> MatchCollection { get; set; }
    public DbSet<Team> TeamCollection { get; set; }


protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Configurations.Add(new MatchConfiguration());
        modelBuilder.Configurations.Add(new TeamConfiguration());



        base.OnModelCreating(modelBuilder);
    }

}

public class MatchConfiguration

{

public MatchConfiguration()
    {
        Map(s =>
                {
                    s.Properties(b => new { b.Address });
                    s.ToTable("Address");
                }
            );
        Map(s =>
                {
                    s.Properties(b => new { b.ContactDetail });
                    s.ToTable("ContactDetail");
                }
            );

    }

}

There is a dbContext, with dbSets, that I left out of my question/example. I was able to get this working myself with the code in the update of my question. — OverForkOver, Aug 05 '11 at 14:16

OverForkOver · Accepted Answer · 2011-08-08T14:21:31.987

0

Hate to answer my own question, but I managed to get this working with the following code in the 'OnModelCreating' method of my context.

modelBuilder.Entity(Of Match).HasRequired(Function(m) m.HomeTeam).WithMany().Map(Function(t) t.MapKey("HomeTeamId"))
modelBuilder.Entity(Of Match).HasRequired(Function(m) m.AwayTeam).WithMany().Map(Function(t) t.MapKey("AwayTeamId"))

I am assuming that my earlier difficulties were due to EF not being able to map the 'HomeTeam' and 'AwayTeam' properties to the 'Team' table, as the property names did not match object name 'team' closely enough. Had thought the database relationships would have provided the mapping I needed.

Am I off the mark on this?

Is there a better way of doing this?

edited Aug 08 '11 at 14:21

answered Aug 05 '11 at 14:31

OverForkOver

88
9

1

Doesn't have a team *many* matches in the DB? If yes, you could consider to replace `WithRequiredDependent` by `WithMany`. Your setup expresses a one-to-one relationship between team and match. – Slauma Aug 05 '11 at 16:29
You are correct. I have corrected the update to the original question. Thank you for pointing out my oversight! – OverForkOver Aug 06 '11 at 01:36

Entity framework - code first - class with two navigation properties to same class/table

Update

3 Answers3