CODEWITHSUNDEEP
c#.netentity-framework
LINQ2Vodka
LINQ2Vodka

Reputation: 3036

Entity Framework: Composite Foreign Key on unique (not primary keys) parent fields

I have a table that needs to be bound to another table by its Id column, and to the third table by its (Id, Code) columns. I can easily do in in SQL Server using both a primary key and a unique index, but have no idea how to implement it in Entity Framework.

If I put [Key] attribute on both columns then I'm unable to create a first relationship.

Otherwise, if I only apply one [Key] then I cannot make second relationship.

Is there any workaround?

Upvotes: 3

Views: 3679

Answers (1)

LINQ2Vodka
LINQ2Vodka

Reputation: 3036

Ok, I was able to make as many keys as needed using Fluent API. It works now.

More detailed:
In business-terms, i have a Form object that has Fields just like in html document - of types input, choice, text, radiogroup etc.
I prefere to store fields data strongly typed, in separate tables, but the form needs to know what fields are in to maintain their unique order.

So in the database i have tables:

Form - contains Form's name and description
FormField - (0 to many rows for each form) contains FormId, FieldType and OrderNo. By setting (FormId, OrderNo) to unique I maintain the right order.

The trick is that the typed fields itselves are stored in different tables like InputField, ChoiceField, NumberField etc. I want them to be tied to the parent FormField rows like 0 or 1 to 1 and not to allow them to get lost when parent row is deleted.
If it was simple parent-child relation, i could use standard FK, but here i have variuos child tables.

To make it all consistent, in the SQL Server i've created a composite PK (FormId, FieldType) in the parent FormField table and FKs as (FormId, FieldType) in all child tables where FieldType is set to constant computed value (say 0 in the InputField table, 1 in the ChileField table etc).
Thus i've got a good parent-child constraint for a single FormField and multiple child specific-typed tables.
In EF approach, to make a composite FK you need all the corresponding fields to be Keys, so you put the [Key] attribute on both fields.
But if your type-specific table needs to have its own children (like ChoiceField has its ChoiceOptions), and you already have a composite Key, then you need either use the existing composite key or add a new keyset (with only Id field).

So, my problem was to make ChoiceField table to have two sets of keys: one, composite, for the parent FieldForm row, and the second, simple - for the ChoiceOption child table.
Using Fluent API I was able to add a second key.

This is my EF model: 

[Table("Form")]
public class Form : IEntity
{
    [Key, Column("FormID")]
    public int Id { get; set; }

    [Required, StringLength(50)]
    public string Name { get; set; }

    public ICollection<FormField> FormFields { get; set; }
}

[Table("FormField")]
public class FormField : IEntity
{
    [Key, Column("FormFieldID", Order = 0)]
    public int Id { get; set; }

    [Key, Column(Order = 1)]
    public FieldType FieldType { get; set; }

    [ForeignKey("Form")]
    public int FormId { get; set; }

    public int OrderNo { get; set; }

    public virtual Form Form { get; set; }

    public virtual AddressField AddressField { get; set; }
    public virtual ChoiceField ChoiceField { get; set; }
    public virtual DateTimeField DateTimeField { get; set; }
// etc
}

[Table("ChoiceField")]
public class ChoiceField : BaseField, IEntity
{
    [Key, ForeignKey("FormField"), Column("FormFieldID", Order = 0)] // these keys are for parent FormField table
    public int Id { get; set; }

    [Key, ForeignKey("FormField"), Column(Order = 1)]
    public FieldType FieldType { get; set; }

    public virtual FormField FormField { get; set; }

    public ICollection<ChoiceFieldOption> ChoiceFieldOptions { get; set; }
}

[Table("ChoiceFieldOption")]
public class ChoiceFieldOption : IEntity
{
    [Key, Column("ChoiceFieldOptionID")]
    public int Id { get; set; }

    [ForeignKey("ChoiceField")] // this FK are bound to simple ChoiceField.Id key defined in fluent API
    public int ChoiceFieldId { get; set; }

    [Required, StringLength(50)]
    public string Option { get; set; }

    public int OrderNo { get; set; }

    public virtual ChoiceField ChoiceField { get; set; }
}

...

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {

        modelBuilder.Entity<Form>();
        var eFormField = modelBuilder.Entity<FormField>();

        var eChoiceField = modelBuilder.Entity<ChoiceField>();

    // here I add a second Key for ChoiceField table
        eChoiceField.HasKey(cf => new { cf.Id });

        var eChoiceFieldOption = modelBuilder.Entity<ChoiceFieldOption>();

        modelBuilder.Entity<AddressField>();
        modelBuilder.Entity<DateTimeField>();
    // ...
    }

Upvotes: 2

Related Questions