Conditional Association with Entity Framework

Question

I want to create conditional association with Entity Framework. As far as I know we can't create conditional foreign keys, so I can't solve this issue at database server level. I have tables like that:

---Property---
int     id
string  Name
int     TypeId      --> Condition on this.
int     ValueId

---ValueString---
int     id
string  Value

---ValueInteger---
int     id
int     Value

---ValueBoolean---
int     id
bool    Value

Now, the TypeId field in the Property table contains the type of the value. For instance, if the TypeId == 0, then ValueId points to ValueString table, if the TypeId == 1, then ValueId points to ValueInteger table, etc.

I did some workaround but I stuck somewhere:

I have an enum like that:

public enum PropertyType
{
    String = 0, 
    Integer = 1,
    Boolean = 2,
    DateTime = 3,
    List = 4 
}

and I implemented a partial class like that:

public partial class ProductProperty : EntityObject
{
    public object Value
    {
        get
        {
            switch (Property.Type)
            {
                case PropertyType.String:
                    return ValueString.Where(w => w.id == this.ValueId ).Select(s => s);//how to return?
                    break;
                case PropertyType.Integer:
                    return ValueInteger.Where(w => w.id == this.ValueId ).Select(s => s) //how to return?
                    break;
                case PropertyType.Boolean:
                    return ValueBoolean.Where(w => w.id == this.ValueId ).Select(s => s) //how to return?
                    break;
                case PropertyType.DateTime:
                    return ValueDateTime.Where(w => w.id == this.ValueId ).Select(s => s) //how to return?
                    break;
                default:
                    return null;
                    break;
            }
        }
        set
        {

        }
    }
}

But I don't know how to reach context object within an EntityObject, so I couldn't reach Value* tables in the Property EntityObject.

So, is this approach true or what should I do? if it is true, how can I get entity context object in an EntityObject?

Edit: If you don't suggest this approach, what would you suggest? Please share your opinion with us. I think, the best alternative to this approach may be something like this:

---Property---
int     id
string  ValueString
int     ValueInteger
bool    ValueBoolean
etc...

But this way, If I want to add another value type, I will have to change table structure, and I will have to update entity and object models in my project. I can't use serialized objects, because I need to filter data on the value. Edit ended

Answer 1

The closest I think you may be able to get to that fits both on the relational side and on the object-oriented side is to map and object model to a TPC abstraction in the database, which is already very close to the table structure you have. For simplicity I'll show this using Code First in EF 4.3.1.

Let's define a simple object model like so:

public class Property
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int ValueId { get; set; }
    public virtual Value Value { get; set; }
}

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

public class ValueString : Value
{
    public string Value { get; set; }

    public override string ToString()
    {
        return "String value of " + Value;
    }
}

public class ValueInteger : Value
{
    public int Value { get; set; }

    public override string ToString()
    {
        return "Integer value of " + Value;
    }
}

public class ValueBoolean : Value
{
    public bool Value { get; set; }

    public override string ToString()
    {
        return "Boolean value of " + Value;
    }
}

(I put in some ToString methods just to make it easy to see what's going on when we use these classes.)

This can be mapped using TPC such that each type gets its own table:

public class PropertyAndValuesContext : DbContext
{
    public DbSet<Property> Properties { get; set; }
    public DbSet<Value> Values { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<ValueString>().Map(
            m =>
            {
                m.MapInheritedProperties();
                m.ToTable("ValueString");
            });

        modelBuilder.Entity<ValueInteger>().Map(
            m =>
            {
                m.MapInheritedProperties();
                m.ToTable("ValueInteger");
            });

        modelBuilder.Entity<ValueBoolean>().Map(
            m =>
            {
                m.MapInheritedProperties();
                m.ToTable("ValueBoolean");
            });
    }
}

So now the tables we have match the layout you provided at the start of your question, except that the TypeId conditional column is missing since it is not needed here.

Let's write an initializer and a console app to add some test data and display it:

public class TestInitializer : DropCreateDatabaseAlways<PropertyAndValuesContext>
{
    protected override void Seed(PropertyAndValuesContext context)
    {
        new List<Property>
        {
            new Property { Name = "PropWithBool", Value = new ValueBoolean { Id = 1, Value = true } },
            new Property { Name = "PropWithString1", Value = new ValueString { Id = 2, Value = "Magic" } },
            new Property { Name = "PropWithString2", Value = new ValueString { Id = 3, Value = "Unicorn" } },
            new Property { Name = "PropWithInt1", Value = new ValueInteger { Id = 4, Value = 6 } },
            new Property { Name = "PropWithInt2", Value = new ValueInteger { Id = 5, Value = 7 } },
        }.ForEach(p => context.Properties.Add(p));
    }
}

public class Program
{
    public static void Main(string[] args)
    {
        Database.SetInitializer(new TestInitializer());

        using (var context = new PropertyAndValuesContext())
        {
            foreach (var property in context.Properties)
            {
                Console.WriteLine("{0} with {1}", property.Name, property.Value);
            }
        }
    }
}

Running this prints out:

PropWithBool with Boolean value of True
PropWithString1 with String value of Magic
PropWithString2 with String value of Unicorn
PropWithInt1 with Integer value of 6
PropWithInt2 with Integer value of 7

You can see that we're easily able to add different types of values, have them stored in the appropriate tables, and then query these values back.

Now maybe you really want a property the returns the value typed as "object" as in your example. Well, we can now do this with a simple abstract property:

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

    public abstract object TheValue { get; set; }
}

public class ValueString : Value
{
    public string Value { get; set; }

    public override object TheValue
    {
        get { return Value; }
        set { Value = (string)value; }
    }

    public override string ToString()
    {
        return "String value of " + Value;
    }
}

public class ValueInteger : Value
{
    public int Value { get; set; }

    public override object TheValue
    {
        get { return Value; }
        set { Value = (int)value; }
    }

    public override string ToString()
    {
        return "Integer value of " + Value;
    }
}

public class ValueBoolean : Value
{
    public bool Value { get; set; }

    public override object TheValue
    {
        get { return Value; }
        set { Value = (bool)value; }
    }

    public override string ToString()
    {
        return "Boolean value of " + Value;
    }
}

You could also imagine doing this if you wanted:

public class Property
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int ValueId { get; set; }
    public virtual Value Value { get; set; }

    public object TheValue
    {
        get { return Value.TheValue; }
        set { Value.TheValue = value;  }
    }
}

Finally, if you really need the TypeId property/column in the Property entity/table, then you can add it, but you'll have to make sure you set it to some appropriate value since it isn't needed for the mapping.

Answer 2

Arthur's answer is the answer but I want to share my results.

First, I tried to implement Property values as a generic type. And then I implmeneted every ValueString, ValueInteger ,etc. classes that drives from this generic type. It worked but generic type approach caused a lot of casting in the usage. So I've sticked at object values.

This is property class that holds value and the type:

public class ProductProperty
{
    public int ProductPropertyId { get; set; }
    public Product Product { get; set; }
    public int TypeId { get; set; }
    public int ValueId { get; set; }
    [ForeignKey("TypeId")]
    public PropertyType Type { get; set; }
    [ForeignKey("ValueId")]
    public PropertyValue Value { get; set; }
}

This is the type of the property. It has simple type like string which is kept in db as string, int, etc.. For instance, this property type can be "Name" which its simple type would be string, or can be "Price" which its simple type would be float.

public class PropertyType
{
    public int PropertyTypeId { get; set; }
    [MaxLength(150)]
    public string Name { get; set; }

    //For before EF 5, there is no enum support
    [Column("SimpleType")]
    public int InternalSimpleType { get; set; }
    [NotMapped]
    public SimpleType SimpleType
    {
        get { return (SimpleType)InternalSimpleType; }
        set { InternalSimpleType = (int)value; }
    }

    public ICollection<ProductProperty> ProductProperties { get; set; }
}

public enum SimpleType : int
{
    String = 1, 
    Integer = 2,
    Float = 4,
    Boolean = 8,
    DateTime = 16,
    List = 32
}

This is the abstract base class for value tables which Arthur has gived the idea:

public abstract class PropertyValue
{
    [Key]
    public int PropertyValueId { get; set; }
    [NotMapped]
    public abstract object Value { get; set; }
}

These are value classes/tables:

public class PropertyValueString : PropertyValue
{
    [Column("Value", TypeName="ntext")]
    public string InternalValue { get; set; }
    public override object Value
    {
        get
        {
            return (string)InternalValue;
        }
        set
        {
            InternalValue = (string)value;
        }
    }
}

public class PropertyValueInteger : PropertyValue
{
    [Column("Value")]
    public int InternalValue { get; set; }
    public override object Value
    {
        get
        {
            return (int)InternalValue;
        }
        set
        {
            InternalValue = (int)value;
        }
    }
}

public class PropertyValueBoolean : PropertyValue
{
    [Column("Value")]
    public bool InternalValue { get; set; }
    public override object Value
    {
        get
        {
            return (bool)InternalValue;
        }
        set
        {
            InternalValue = (bool)value;
        }
    }
}

public class PropertyValueFloat : PropertyValue
{
    [Column("Value")]
    public float InternalValue { get; set; }
    public override object Value
    {
        get
        {
            return (float)InternalValue;
        }
        set
        {
            InternalValue = (float)value;
        }
    }
}

public class PropertyValueDateTime : PropertyValue
{
    [Column("Value", TypeName = "datetime2")]
    public DateTime InternalValue { get; set; }
    public override object Value
    {
        get
        {
            return (DateTime)InternalValue;
        }
        set
        {
            InternalValue = (DateTime)value;
        }
    }
}

This will be in the conext class which drived from DbContext:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<PropertyValueString>().Map(
            m =>
            {
                m.MapInheritedProperties();
                m.ToTable("PropertyValueString");
            });

        modelBuilder.Entity<PropertyValueInteger>().Map(
            m =>
            {
                m.MapInheritedProperties();
                m.ToTable("PropertyValueInteger");
            });

        modelBuilder.Entity<PropertyValueBoolean>().Map(
            m =>
            {
                m.MapInheritedProperties();
                m.ToTable("PropertyValueBoolean");
            });

        modelBuilder.Entity<PropertyValueFloat>().Map(
            m =>
            {
                m.MapInheritedProperties();
                m.ToTable("PropertyValueFloat");
            });

        modelBuilder.Entity<PropertyValueDateTime>().Map(
            m =>
            {
                m.MapInheritedProperties();
                m.ToTable("PropertyValueDateTime");
            });

So, my problem was solved. And I wanted to share this.