Create navigation property without foreign key

Question

I have two classes like so:

[Table("GameLevels", Schema = "ref")]
public class GameLevel
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }
    public string Name { get; set; }
    public double PointsMin { get; set; }
    public double PointsMax { get; set; }
}

[Table("GameProfiles", Schema = "usr")]
public class UserGameProfile 
{
    [Key]
    [ForeignKey("ApplicationUser")]
    public string Id { get; set; }
    public int GamesPlayed { get; set; }
    public double Points { get; set; }
    public int WinCount { get; set; }
    public int LossCount { get; set; }
    public int DrawCount { get; set; }
    public int ForfeitCount { get; set; }
    public int GameLevelId { get; set; }

    public virtual GameLevel Level { get; set; }

    public virtual ApplicationUser ApplicationUser { get; set; }
}

Entity framework builds this so that UserGameProfile has a foreign key pointing to GameLevel. I guess this is because of the GameLevelId property.

Is there any way I can get this to generate the tables and navigation property without the foreign key?

I tried:

modelBuilder.Entity<UserGameProfile>().HasOptional<GameLevel>(x => x.Level).WithMany();

But then database fails to build. With this error:

One or more validation errors were detected during model generation:

Project.Domain.Data.UserGameProfile_Level: : Multiplicity conflicts with the referential constraint in Role 'UserGameProfile_Level_Target' in relationship 'UserGameProfile_Level'. Because all of the properties in the Dependent Role are non-nullable, multiplicity of the Principal Role must be '1'.

Basically what I want is a zero-or-one to zero-or-many relationship.

How do I keep levels independent but have the ability to add a level to a profile?

41686d6564 41686d6564 · Accepted Answer · 2019-03-13T00:52:10

You can't drop the foreign key completely, otherwise, how do you expect the two entities (i.e, tables) to be linked? What you can do instead is have a nullable FK, which will effectively make the relationship zero-or-one to many.

In your GameLevel class, add a navigation property as a collection of UserGameProfile:

public class GameLevel
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }
    public string Name { get; set; }
    public double PointsMin { get; set; }
    public double PointsMax { get; set; }

    public virtual ICollection<UserGameProfile> UserGameProfiles { get; set; }
}

Then in the UserGameProfile class, make the property GameLevelId nullable:

public class UserGameProfile 
{
    // ...
    // ...

    public int? GameLevelId { get; set; }

    [ForeignKey("GameLevelId")]
    public virtual GameLevel GameLevel { get; set; }
}

This should work without even having to use Fluent API.

Create navigation property without foreign key

1 Answers