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.

Hossam · Accepted Answer · 2011-09-12 21:32:34Z

10

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>()

answered Sep 12, 2011 at 21:32

Hossam

1,0372 gold badges13 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Dismissile Over a year ago

This is good...but is there no way to have a property in my DbContext like DbSet<Adjective> Adjectives { get; set; } then?

Hossam Over a year ago

Yes you may have such a property and use var adjs = ctx.Adjectives... the key solution here is the TPH mapping in the OnModelCreating void.

Jerther Over a year ago

Is it just me or are TDerived and TEntityType swapped here?

Collectives™ on Stack Overflow

Entity Framework Code First - One Table / Multiple Types

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related