One to many relationship: why does ID field go in other entity?

Question

In Entity Framework when I want to specify that an entity has many of another type of entity it seems to do things backwards to me.

For instance let's say I have a keyword entity that is used in several places throughout my app. All keywords are unique but on my other entities I want to have multiple keywords attached them so to me this would make sense:

class Page
{
    public int ID { get; set; }
    public string Name { get; set; }

    public virtual ICollection<Keyword> Keywords { get; set; }
}

class Search
{
    public int ID { get; set; }
    public DateTime Date { get; set; }

    public virtual ICollection<Keyword> Keywords { get; set; }
}

class Keyword
{
    public int ID { get; set; }
    public string Name { get; set; }
}

However when I do this the foreign key is added to the Keyword table whereas I want it to be on the actual entity so I can look at it in database and see small list of keywords instead of looking at keyword and seeing a ridiculous large number of page results.

So instead to get Entity Framework to put the Keyword_IDs on Page and Search entities I am doing this:

class Page
{
    public int ID { get; set; }
    public string Name { get; set; }
}

class Search
{
    public int ID { get; set; }
    public DateTime Date { get; set; }
}

class Keyword
{
    public int ID { get; set; }
    public string Name { get; set; }

    public virtual ICollection<Page> Pages { get; set; }
    public virtual ICollection<Search> Searches { get; set; }
}

This feels backwards as I am specifying the relationship on the entity that doesn't get the foreign ID field in the database table.

I feel like I am doing something wrong as I should be able to see the relationship by looking at my search & page class.

I am sorry for the basic question but for some reason I have read documentation and I am not fully understanding it.

With your first example what db schema do you end up with? What do you want it to look like instead? In query analyzer what rows do you get back now? How do you want the rows returned instead? — Jasen, Aug 29 '15 at 21:57
first example i get Search_ID and Page_ID column in my Keyword table and not any Keyword_ID in search or page table. Second example I get the Keyword_ID in search and page table (which is what I prefer). To me this seems back to front — Guerrilla, Aug 29 '15 at 21:59
Your second example means you can only have one Keyword for a Search (or Page). I think you want a [many-to-many](http://stackoverflow.com/questions/8927278/how-to-configure-many-to-many-relationship-using-entity-framework-fluent-api) relationship instead. — Jasen, Aug 29 '15 at 22:24

score 1 · Accepted Answer · answered Aug 29 '15 at 23:23

In a one-to-many association it's always the many side that refers to the one side. How else would you implement it? If a Page would have a KeywordId as FK, it could only have one keyword, ever.

Also, even when a Keyword would belong to a myriad of pages, that doesn't mean you always have to access all of these pages through one keyword. You'd only do that if you'd do a search for pages in which specific keywords are used.

But now back to your model. You can't have one-to-many associations here. It would mean that any keyword can only belong to one Page or one Search. And if you invert the relationship, as you proposed, a Page or Search can only ever have one keyword (the one that Keyword_ID refers to).

In reality, you're dealing with many-to-many associations and the good news is, it leaves your Keyword intact.

Modelling it as many-to-many doesn't change the way your model looks (the first version), but the mapping is different:

modelBuilder.Entity<Page>().HasMany(p => p.Keywords)
    .WithMany()
    .Map(m =>
        {
            m.ToTable("PageKeyword");
            m.MapLeftKey("PageID");
            m.MapRightKey("KeywordID");
        });

modelBuilder.Entity<Search>().HasMany(s => s.Keywords)
    .WithMany()
    .Map(m =>
        {
            m.ToTable("SearchKeyword");
            m.MapLeftKey("SearchID");
            m.MapRightKey("KeywordID");
        });

This will generate two junction tables in your database, PageKeyword and SearchKeyword that record the many-to-many associations.

One to many relationship: why does ID field go in other entity?

1 Answers1