Entity Framework Code First and Collections of Primitive Types

Question

When creating POCO classes that contain collections of primitive types and are persisted by EF Code First, the best advice I have found so far is to create a new class that has an ID plus the primitive type:

Entity Framework and Models with Simple Arrays

If I now have several classes that require properties of type ObservableCollection<string> and replace them with ObservableCollection<EntityString> (where EntityString is a custom type with an Id and a string property), I end up with a table EntityString that has multiple foreign key columns, one for each property of type ObservableCollection<EntityString> across all concrete types with such properties.

This leads to a bloating of mostly-null foreign key columns in the EntityString table.

One approach would be to create a subclass of EntityString and use the Table per Type model for those subclasses. However, that requires making awkward changes to the object model simply to accommodate Entity Framework.

Questions:

• Is the encapsulating type the best way to manage Collection<PrimitiveType>?
• If so, what are the pro's and con's of allowing multiple (many) foreign key columns vs. creating custom tables per type (at the cost of an awkward model)?

Answer 1

Promoting simple type to entity is one option. If you want to use that new primitive type entity in more relations it is better to completely remove navigation properties from that entity and use independent association (no FK properties).

public class StringEntity
{
    public int Id { get; set; }
    public string Text { get; set; }
}

and mapping:

modelBuilder.Entity<Foo1>().HasMany(f => f.Strings).WithOptional();
modelBuilder.Entity<Foo2>().HasMany(f => f.Strings).WithOptional();

In database you will get new nullable FK per related principal - there is no way to avoid it except create special StringEntity class per principal (don't use inheritance for that because it affects performance).

There is an alternative:

public class StringEntity
{
    public int Id { get; set; }
    public List<string> Strings { get; private set; }

    public string Text 
    {
        get
        {
            return String.Join(";", Strings);
        }

        set
        {
            Strings = value.Split(";").ToList();
        }
    }   
}

In this case you don't need related entity type (and additional table) but your entity is polluted with additional property Text which is only for persistence.