0

I´m currently learning to use EF and I have the following relationships:

An Alert has 1 to n occurences. Each occurence can have 0 to n values (additional information).

public class Alert
{
    // PK
    public int AlertId { get; set; }

    // Attributes
    public int CurrentAlertLevel { get; set; }

    public DateTime TimeRaised { get; set; }
    public DateTime TimeLastRaised { get; set; }

// Some other attributes ommitted...        

    // Relations
    public ICollection<AlertOccurrence> Occurrences { get; set; }
}

public class AlertOccurrence
{
    // Relations which are part of the primary key
    public int AlertId { get; set; }

    // Attributes
    public int Ordinal { get; set; }

// some ommited attributes

    // Relations
    public ICollection<AlertDetailValue> AlertDetailValues { get; set; }
}

public class AlertDetailValue
{
    public int AlertDetailValueId { get; set; }
    public int Order { get; set; }
    public string Value { get; set; }
}

In the DB Context OnModelCreating I´m setting the combined PK for AlertOccurence:

modelBuilder.Entity<AlertOccurrence>().HasKey(ao => new {ao.AlertId, ao.Ordinal});

While it seems that this is working - what I would actually like to archive is the same relationship without the need to have the AlertDetailValueId as PK. The table that EF generates also includes AlertOccurrenceAlertId and AlertOccurrenceOrdinal which seems a waste of space to me.

So what I would like to do is: Have a combined primary key for AlertDetailValue consisting of AlertDetailValue.Order and the (already combined) PK of AlertOccurence instead of the "artificial" AlertDetailValueId. Is that even possible ?

Part of my problem might be that the PK defined using the fluent api is not part of the data classes. So probably another question to ask would be: Is there a way to use a key defined in fluent api in a entity class ?

Or do I need to include AlertOccurrenceAlertId and AlertOccurrenceOrdinal in my entity class AlertDetailValue - but how do I link them then ?

As I said I´m still trying to get my head around EF so while there might be better ways to do this I´m interested in this special kind of relation / combined(combined) PK even if it might be academic... Any help would be highly appreciated.

BigKid
  • 9
  • 4
  • If EF generated tables includes columns like `AlertOccurrenceAlertId`, `AlertOccurrenceOrdinal` etc, that'd be because you haven't defined/configured the foreign-key yourself and EF is trying to do it for you using its naming conventions. – atiyar Oct 25 '20 at 10:27

1 Answers1

0

Trying to explain what I try to do and what my problem is - and taking a good shower - helped me to ask different questions to google and focus more on the foreign key. It´s not that I did not try to google it before... I just asked the wrong questions..

So I found this: Mapping composite foreign key to composite primary key where the foreign key is also a primary key (While trying the new approch @atiyar also hinted on the missing foreign key...)

My new solution was to change AlertDetailValue to intentionally include the parts that the Occurence PK is build of:

    public class AlertDetailValue
    {
         // relations will be set up in fluent api in OnModelCreating of db context
        public int AlertOccurenceAlertId { get; set; }
        public int AlertOccurenceOrdinal { get; set; }

        public int Order { get; set; }
        public string Value { get; set; }
    }

And then to tell EF that there is a combined PK and also a combined foreign key:

            modelBuilder.Entity<AlertDetailValue>().HasKey(adv => new { adv.AlertOccurenceAlertId, adv.AlertOccurenceOrdinal, adv.Order  });

            modelBuilder.Entity<AlertOccurrence>().HasMany<AlertDetailValue>(adv => adv.AlertDetailValues).WithOne()
                .HasForeignKey(adv => new {adv.AlertOccurenceAlertId, adv.AlertOccurenceOrdinal});
BigKid
  • 9
  • 4