Configuring many One-To-One relationships with Ent

Question

I have a horrible database model (in MySQL though I don't think that matters) that someone else designed so I'm stuck with it, but want to use with Entity Framework with it anyway:

Database

Item

ASSET_ID*   ASSET_NAME  FIELDS_ID
1          Cat         1
2          Dog         2
3          Fish        3

ItemFields

ID*    CUSTOMFIELD1ID    CUSTOMFIELD2ID    CUSTOMFIELD3ID
1     1001              1002              1003
2     1004              1005              1006
3     1007              1008              1009

Field

ID*    STRINGVAL
1001   Meow Mix
1002   House
1003   5lbs
1004   Kibble
1005   Yard
1006   30lbs
1007   Fish Food
1008   Tank
1009   0.5 ounces

*indicates table's PK

---

Goal

Ultimately, I'm trying to configure the relationships so I can get custom data like this:

Item item = GetItem(1);
item.Food.ToString(); // Output: meow mix
item.Place.ToString(); // Output: house
item.Weight.ToString(); // Output: 5 lbs 

...etc. but honestly I'd settle for this at this point:

Item item = GetItem(1);
Item.ItemFields.CustomField3.Value // Output: 5 lbs
Item item = GetItem(2);
Item.ItemFields.CustomField2.Value // Output: Yard
Item item = GetItem(3);
Item.ItemFields.CustomField1.Value // Output: Fish Food

---

Solution thus far

So far I've got this:

One-To-One: Item - ItemFields

modelBuilder.Entity<Item>()
    .HasRequired(x => x.ItemFields)
    .WithRequiredPrincipal(y => y.Item);

But what about mapping ItemFields.CustomField1ID to Field.ID?

Is it even possible to configure a relationship like this using EF code first? I'm pretty confused about whether or not this is a one-to-one or a one-to-many... I think what it actually is is many one-to-ones (if that makes sense).

Any suggestions?

Answer 1

I may have figured this out. I edited my ItemFields class to look like this:

public class ItemFields
{
    public int Id { get; set; }
    public int CUSTOMFIELD1ID { get; set; }
    public virtual Field CustomField1 { get; set; }

And then configured these relationships:

modelBuilder.Entity<Item>()
    .HasRequired(x => x.CustomField1)
    .WithMany()
    .HasForeignKey(x => x.CUSTOMFIELD01_ID);
modelBuilder.Entity<Item>()
    .HasRequired(x => x.CustomField2)
    .WithMany()
    .HasForeignKey(x => x.CUSTOMFIELD02_ID);

And it seems to have worked, though to be honest I don't fully understand why. I'd still ideally like to map them to more user-friendly classes.

Answer 2

I think you agree that at this case we actually have one-to-many relationship: one item has several fields and table ItemFields is surrogate thing. I recommend you to perform refactoring and create only two tables: Items and Fields. In Fields table will be added column Type - similar CUSTOMFIELD1ID(2ID,3ID) and foreign key referenced directly to Items table. In Item class were added properties: Food, Place and Weight. They will not be mapped to columns, but you can use them as you specified in your question for accessing to particular fields(look at IMPLEMENTATION section):

MODELS:

public enum CustomType
{
    Food,
    Place,
    Weight
}

public class Item
{
    public int ID { get; set; }
    public string ASSET_NAME { get; set; }
    public virtual ICollection<Field> fields { get; set; }

    [NotMapped]
    public Field Food { get { return fields == null ? null : fields.Where(x => x.Type == CustomType.Food).FirstOrDefault(); } }
    [NotMapped]
    public Field Place { get { return fields == null ? null : fields.Where(x => x.Type == CustomType.Place).FirstOrDefault(); } }
    [NotMapped]
    public Field Weight { get { return fields == null ? null : fields.Where(x => x.Type == CustomType.Weight).FirstOrDefault(); } }
}

public class Field
{        
    public int ID { get; set; }
    public string STRINGVAL { get; set; }
    public CustomType Type { get; set; }

    public virtual Item Item { get; set; }
}

TABLES:

IMPLEMENTATION:

var db = new DataContext();
var item = db.Items.Include("fields").Where(x => x.ID == 1).First();
var Food = item.Food;
var Place = item.Place;
var Weight = item.Weight;