0

I have the following database model

Entity
Id, ...

PropertyType
Id, Name, DataType, ...

EntityProperty
Id, EntityId, PropertyTypeId

PropertyValueString
Id, Value

PropertyValueDateTime
ID, Value

I am using EF6 code first to map my entities to this data model

class Entity {
  public Guid Id { get; set; }
  ...
}

class PropertyType {
  public Guid Id { get; set; }
  public string Name { get; set; }
  public Type DataType { get; set; }
  ...
}


class EntityProperty {
  public Guid Id { get; set; }
  public Guid EntityId { get; set; }
  public Entity Entity { get; set; }
  public Guid PropertyTypeId { get; set; }
  public PropertyType PropertyType { get; set; }
  public string StringValue { get; set; }
  public DateTime? DateTimeValue { get; set; }
}

Mapping Entity and PropertyType class to respective tables is straight forward. EntityProperty is mapped using

Map(m => m.ToTable("EntityProperty").Properties(p => new { p.Id, p.EntityId, p.PropertyTypeId });
Map(m => m.ToTable("PropertyValueString").Properties(p => new { p.Id, p.StringValue });
Map(m => m.ToTable("PropertyValueDateTime").Properties(p => new { p.Id, p.DateTimeValue });

How do I map my EntityProperty to PropertyValueString or PropertyValueDateTime tables depending on which field has value? Also, the query generated when including EntityProperty should LEFT JOIN with PropertyValueString and PropertyValueDateTime.

Is this even possible with Entity Framework?

sujith karivelil
  • 28,671
  • 6
  • 55
  • 88
user2321864
  • 2,207
  • 5
  • 25
  • 35
  • Seems like you are trying to outsmart the ORM ("Also, the query generated when including EntityProperty should `LEFT JOIN` with [...]"). If you work with ORM, leave the internals like the join strategy to the ORM and concern yourself with more high level design properties. Another thing: The `Type` property will most probably produce an error when you try to generate database tables from it. – grek40 Mar 29 '16 at 11:03
  • @grek40 I understand about the Type field, this was just to illustrate that PropertyType is a sort of meta table which describes the property and may have additional metadata e.g. IsRequired etc. – user2321864 Mar 29 '16 at 11:05
  • 1
    If you wrap `StringValue` and `DateTimeValue` into a sub class each and have them inherit the same base class, an EF table per type hierarchy might be somewhat close to what you want. However, I get the feeling that your problem and your question are not really the same thing. – grek40 Mar 29 '16 at 11:33
  • @grek40 The problem is when creating the mapping as described in the question EF assumes a 1-1 relation between EntityProperty, PropertyValueString, and PropertyValueDateTime. However what I want is 1-0..1 relationship between EntityProperty and PropertyValueString and PropertyValueDateTime and only 1 of them will have a corresponding record for a given EntityProperty.Id. After reading the conditions for entity splitting at msdn.microsoft.com/en-us/library/bb896233.aspx I need to revisit my classes so will try TPT. Thanks – user2321864 Mar 29 '16 at 12:07
  • You are confusing EAV with 6NF. The (necessary) 6NF of EAV tables does not guarantee 6NF relevant to your application. EAV stores *metadata*, ie encodes the table(s) actually relevant to an application into a collection of other tables. Your entities are your application entities *plus* entities that are parts of tables representing application entities & relationships. Eg 6nf application table person "person with id *i* at time *t* had name *n*" row is encoded (ignoring types) by rows property(i, p, "name"), string(p,string(n)) & time(p,t). The 6NF of the EAV tables is irrelevant. – philipxy Jun 06 '17 at 23:00

0 Answers0