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
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.