CODEWITHSUNDEEP
c#sqlentity-framework
Daniel
Daniel

Reputation: 1837

Entity Framework (code first) creates two foreign key fields for the same property when using subclasses

Overview

I'm building a web app (C#, MVC, EF, Azure) that helps people play a computer game (named "DotA") by allowing them to keep notes on how they should play. DotA is a 5v5 team game, and each player controls one of over 100 unique "heroes" that have various abilities and attributes.

In my app, you can make two different types of notes: tips and relationships.

Tips

A Tip is a tip on how to play the hero effectively.

[Table(nameof(Tip))]
public class Tip: UserOwnedEntity
{
    /// <summary>
    /// The "HeroSubject" of a tip is the hero this tip applies to.
    /// </summary>
    public Hero HeroSubject { get; set; }
    public int? HeroSubjectId { get; set; }

    //Tips can be categorized
    public TipType Type { get; set; } = TipType.Counter;

    [Required]
    [DataType(DataType.MultilineText)]
    public string Text { get; set; }

    /// <summary>
    /// As the game changes, tips may go out of date; tracking the
    /// applicable patch assists with this, and users can flag a
    /// specific tip as out of date ("deprecated").
    /// </summary>
    [Required]
    public string Patch { get; set; }
    public bool Deprecated { get; set; } = false;

    /// <summary>
    /// Where the tip was found, possibly a URL. Can give context to
    /// a tip that isn't very intuitive.
    /// </summary>
    public string Source { get; set; }
}

UserOwnedEntity

You'll notice that Tip inherits from UserOwnedEntity, a simple class that contains shared properties for entities that relate to a given User:

public abstract class UserOwnedEntity
{
    public int Id { get; set; }

    public int UserId { get; set; }
    public virtual User User { get; set; }
}

Relationships

A Relationship makes a comparison between two different heroes (e.g. "hero X synergizes with hero Y"). Relationships are a superset of tips; they have a subject (hero X), but they also have an object (hero Y). Here's the code:

[Table(nameof(Relationship))]
public class Relationship: Tip
{
    public int? HeroObjectId { get; set; }
    public Hero HeroObject { get; set; }
}

Note: I've removed all the extra methods from these classes so it's easier to understand the data relationships.

Users

That leaves us with the User object which brings it all together:

public class User
{
    public int Id { get; set; }

    [Required]
    [Index(IsUnique = true)]
    public string Username { get; set; }

    //Associated objects
    public virtual List<Hero> Heroes { get; set; }
    public virtual List<Tip> Tips { get; set; }
    public virtual List<Relationship> Relationships { get; set; }
}

Hero

Edit: Adding Hero class for completeness.

public class Hero: UserOwnedEntity
{
    public string Name { get; set; }

    [DataType(DataType.MultilineText)]
    public string Notes { get; set; }

    public virtual List<Tip> Tips { get; set; } = new List<Tip>();

    [NotMapped]
    public virtual List<Relationship> Relationships { get; set; } = new List<Relationship>();
}

Entity Framework's SQL Interpretation

Entity Framework creates a normal/expected Tip' table, but theRelationship` table contains the following fields:

Problem 1: Multiple User Foreign Keys

The Relationship table should not contain a User_Id field; it already has a UserId field in the Tip table . As it is, a Relationship can be associated with 2 User records (once in the Tip table and once in the Relationship table), which should not be possible. It is always null, so certain queries fail when they use this UserId field instead of the inherited field. Why is this second foreign key being created?

The specific circumstance where the query fails is when accessing Relationships via the User, as in:

var relationship = db.Users.First().Relationships.FirstOrDefault(r => r. Id == id);

Problem 2: Tips and Relationships Can't be Distinguished

Other than checking for a matching Relation.Id, there's no way to tell if a record in the Tip table is actually a Relationship.

Preferred Solution

What I want is two different tables: one for Tips and one for Relationships. I'm just not sure what combination of attributes need to be added to accomplish that goal.

Or, Failing That...

If I could get rid of the extra User_Id field and make it so Tips and Relationships would load as if they were in different tables, I would be happy with that. Maybe happier, actually.

Upvotes: 1

Views: 357

Answers (1)

Ivan Stoev
Ivan Stoev

Reputation: 205719

The problem is that your entity model is identified as eligible for using EF inheritance strategy (with this particular configuration - Table per Type (TPT)).

What I want is two different tables: one for Tips and one for Relationships.

While this is possible by configuring Table per Concrete Type (TPC) using fluent API, it will not solve the problem with navigation properties/relationships/FKs.

It's because anytime EF see ICollection<Tip>, it will make sure it also includes Relationships (because Relationship is a Tip). At the same time, in order to support ICollection<Relationship>, it would create another FK. So in your case, UserId will be used to map Relationship to User.Tips and User_Id (default name by convention) - Relationship to User.Relationships. Remember - in EF every collection is mapped to a separate relationship.

So you either have to remove/ignore all ICollection<Relationship> properties in addition to configuring TPT inheritance. Or, get rid of EF inheritance at all. There is no way to do the later with data annotations/fluent API setup, so you need to extract a base non entity class similar to UserOwnedEntity. Something like this:

public abstract class TipEntity : UserOwnedEntity
{
    /// <summary>
    /// The "HeroSubject" of a tip is the hero this tip applies to.
    /// </summary>
    public Hero HeroSubject { get; set; }
    public int? HeroSubjectId { get; set; }

    //Tips can be categorized
    public TipType Type { get; set; } = TipType.Counter;

    [Required]
    [DataType(DataType.MultilineText)]
    public string Text { get; set; }

    /// <summary>
    /// As the game changes, tips may go out of date; tracking the
    /// applicable patch assists with this, and users can flag a
    /// specific tip as out of date ("deprecated").
    /// </summary>
    [Required]
    public string Patch { get; set; }
    public bool Deprecated { get; set; } = false;

    /// <summary>
    /// Where the tip was found, possibly a URL. Can give context to
    /// a tip that isn't very intuitive.
    /// </summary>
    public string Source { get; set; }
}

[Table(nameof(Tip))]
public class Tip : TipEntity
{
}

[Table(nameof(Relationship))]
public class Relationship : TipEntity
{
    public int? HeroObjectId { get; set; }
    public Hero HeroObject { get; set; }
}

Of course this will affect other parts of your application because Relationship is no more Tip. Instead, both Tip and Relationship now are TipEntity, so the TipEntity can server the same purpose as Tip in non EF related polymorphic code.

Upvotes: 2

Related Questions