Entity Framework Core: use auto-generated foreign key as part of composite primary key

user_7691321 0
Viewed 674

I have a generic class TimeSeries<T> which contains a list of generic state variables State<T>. State<T> contains the state at a certain time.

public class TimeSeries<T>
{
    [Key]
    public int ID { get; set; }
    public List<State<T>> States { get; set; }
}

public class State<T>
{
    [Key]
    public int StateID { get; set; }
    public DateTime Date { get; set; }
    public T State { get; set; }
}

Furthermore, I have two classes IntClass and FloatClass, both of which contain a time series, but of different types. To obtain two tables for TimeSeries, where one has a foreign key pointing to IntClass and the other one has a foreign key pointing to FloatClass, I have derived two more classes from TimeSeries<T>, each containing a property for the foreign key.

public class IntTimeSeries : TimeSeries<int>
{
    public int IntClassID{ get; set; }
}
public class FloatTimeSeries : TimeSeries<float>
{
    public int FloatClassID{ get; set; }
}

IntClass and FloatClass contain an instance of the derived classes:

public class IntClass
{
    [Key]
    public int ID { get; set; }
    public IntTimeSeries TimeSeries { get; set; }
}
public class FloatClass
{
    [Key]
    public int ID { get; set; }
    public FloatTimeSeries TimeSeries { get; set; }
}

This approach generates among others the tables State<int> and State<float>. These have the (auto-generated) foreign keys IntClassID and FloatClassID, respectively. If I annotate StateID with [Key] as I have done here, then that is of course the primary key.

What I want, however, is that the primary keys are the composites (Date, IntClassID) and (Date, FloatClassID), respectively. I need this because I sometimes need to update the values from a second database where I have only reading permission. Is this possible?

entity-framework-core entity-framework asp.net c#
2 Answers

In EF-core 3 and 5, you can achieve this by modifying EF's metadata which is easily accessible in these later versions:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    var stateTypes = modelBuilder.Model.GetEntityTypes()
        .Where(e => e.ClrType.IsConstructedGenericType
            && e.ClrType.GetGenericTypeDefinition() == (typeof(State<>)))
        .ToList();
    stateTypes
        .ForEach(t => t.SetPrimaryKey(t.FindProperties(new[] { "StateID", "Date"} )));
}

This first collects the entity types that are derived from State<> and then sets the primary key properties for these types. Using nameof would of course make this more runtime-safe.

For EF core 2 users, the last statement would be:

    stateTypes.ForEach(t => t.SetPrimaryKey(t.GetProperties()
        .Where(p => new[] { "StateID", "Date" }.Contains(p.Name))
            .OrderByDescending(p => p.Name)
        .ToList()));

(OrderByDescending because in this case the properties aren't taken in the order of the array).

user_8617160

Thanks to Gert, I found a solution:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    var stateTypes = modelBuilder.Model.GetEntityTypes()
        .Where(e => e.ClrType.IsConstructedGenericType
            && e.ClrType.GetGenericTypeDefinition() == (typeof(State<>)))
        .ToList();
    stateTypes.ForEach(t => t.FindProperty(GeneratedForeignKeyName(t)).IsNullable = false);
    stateTypes
        .ForEach(t => t.SetPrimaryKey(t.FindProperties(new[] { GeneratedForeignKeyName(t), "Date"} )));
}

private string GeneratedForeignKeyName(IMutableEntityType t)
{
    if (t.Name.Equals(/* data type namespace here */ + ".State<float>"))
    {
        return "FloatClassID";
    }
    else if (t.Name.Equals(/* data type namespace here */ + ".State<int>"))
    {
        return "IntClassID";
    }
    else throw new ArgumentException();
}
user_76913210
Related