Entity Framework Code First - One Table / Multiple Types

Question

In my database I have created a table similar to this:

dbo.Words
    WordId INT PRIMARY KEY
    WordText NVARCHAR(75)
    WordTypeId INT FK

WordTypeId references another table which is a lookup. It will have one of the following values:

Verb
Noun
Adjective

I would like to create entity classes like this:

public class Word
{ ... }

public class Noun : Word
{ ... }

public class Verb : Word
{ ... }

public class WordType
{ ... }

public class MyContext : DbContext
{
    public DbSet<Noun> Nouns { get; set; }
    public DbSet<Verb> Verbs { get; set; }
    public DbSet<Word> Words { get; set; }
    public DbSet<WordType> WordTypes { get; set; }
}

How would I accomplish this in Entity Framework 4.1 - Code First? When I query the Nouns DbSet I want it to only return words with WordTypeId of whatever my Noun type is in the WordTypes table.

Answer 1

You need to adopt Table per Hierarchy (TPH) where ehe entire hierarchy is mapped to a single database table (dbo.Words) holding all the properties of all the classes and use the WordTypeId column as a Discriminator column .. here you go.

1. Domain Model

public abstract class Word
{
    public int WordId { get; set; }
    public string WordText { get; set; }
    //DO NOT map the WordTypeId column
    //as it is used as the Discriminator column
}

public class Noun : Word { }
public class Verb : Word { }
public class Adjective : Word { }

2. Context

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    //Assuming WordTypeId eqauls 1 for Nouns, 2 for Verbs, 3 for Adjectives
    modelBuilder.Entity<Noun>().Map<Word>(c => c.Requires("WordTypeId").HasValue(1));
    modelBuilder.Entity<Verb>().Map<Word>(c => c.Requires("WordTypeId").HasValue(2));
    modelBuilder.Entity<Adjective>().Map<Word>(c => c.Requires("WordTypeId").HasValue(3));
}

To get your verbs use :

var verbs = x.Words.OfType<Verb>()