Trying to populate very similar relations through same property

Question

I am trying to define two classes with the same collection-relation, through the same key on a third class, preferably using EF 6 code annotations.

I have three tables;

Current is a quick reference. It contains only the latest version of each object, and not all of the fields. It is keyed on ObjectId (but also has a VersionId that ticks up each time an object is saved)
Archive is a history of Current. It has exactly the same fields as Current, but here VersionId is included in the key. So it is keyed on ObjectId + VersionId
Change contains an XML blob with field-by-field modifications to the objects. It has a complete list of the fields modified between two versions of an object, stored as 'field name', 'old value', 'new value'.
It has an ObjectId column, but you have to parse the XML to get the VersionIds involved (found in the form of 'old value', 'new value')

On both Current and Archive classes, I want a Changes collection with the changes related to that ObjectId. Having reverse relations back to Current and Archive would also be nice.

In SQL terms, the code for populating each of those relations would be:

'Current.Changes' => SELECT * FROM Change WHERE ObjectId = <this.ObjectId>
'Archive.Changes' => SELECT * FROM Change WHERE ObjectId = <this.ObjectId>
'Change.Current' => SELECT * FROM Current WHERE ObjectId = <this.ObjectId>
'Change.ArchivedObjects' => SELECT * FROM Archive WHERE ObjectId = <this.ObjectId>

This is how it looks right now:

[Table(nameof(Current))]
public class Current : ITrObject // = common interface for 'Current' and 'Archive'
{
      [Key, DatabaseGenerated(DatabaseGeneratedOption.None)]
      public int ObjectId { get; set; }
      public int VersionId { get; set; }

      // ... Lots of properties ...

      [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
      public virtual ICollection<Change> Changes { get; set; }

}

[Table(nameof(Archive))]
public class Archive : ITrObject
{
      [Key, Column(Order = 0), DatabaseGenerated(DatabaseGeneratedOption.None)]
      public int ObjectId { get; set; }
      [Key, Column(Order = 1), DatabaseGenerated(DatabaseGeneratedOption.None)]
      public int VersionId { get; set; }

      // ... Lots of properties ...

      [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
      public virtual ICollection<Change> Changes { get; set; }
}

[Table(nameof(Change))]
public class Change
{
      [Key, DatabaseGenerated(DatabaseGeneratedOption.None)]
      public int ChangeId { get; set; }

      public int? ObjectId { get; set; }  // 'int?' because the source system sometimes creates changes that are logged, but not saved yet. Probably some kind of 'autosave' feature.

      // ... Lots of properties ...

      [ForeignKey(nameof(ObjectId))]
      public virtual Current Current { get; set; }

      [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
      public virtual ICollection<Archive> ArchivedObjects { get; set; }
}

Modelbuilder code:

modelBuilder.Entity<Current>()
        .HasMany(cur => cur.Changes)
        .WithOptional(chg => chg.Current)
        .HasForeignKey<int?>(chg => chg.ObjectId)
        .WillCascadeOnDelete(false);

modelBuilder.Entity<Archive>()
        .HasMany(arch => arch.Changes)
        .WithMany(); // Stuck here...

(All code has been cut down and anonymized somewhat, but I hope I caught all typos now.)

Is this at all possible in EF 6? If not, do you have any suggestions for how to do it in some other way, that doesn't completely crap on the rest of the EF 6 data retrieval?

The relations aren't really that crazy, so it would be nice if it could be done.

I have some influence over the database, but it's mostly given.

I have much more influence over the C# code, but can't do too crazy stuff there either - like migrating to another ORM for example...

EDIT: Some of the configurations I have tried, and their SQL errors

modelBuilder.Entity<Archive>()
   .HasMany(arch => arch.Changes)
   .WithMany();

... gives the error SqlException: Invalid object name 'Change_ChangeId'.

modelBuilder.Entity<Archive>()
   .HasMany(arch => arch.Changes)
   .WithMany(chg => chg.Archive);

... gives the error SqlException: Invalid object name 'dbo.ArchiveChanges'.

modelBuilder.Entity<Change>()
   .HasMany(chg => chg.ArchivedObjects)
   .WithMany();

... gives the error SqlException: Invalid column name 'Archive_ObjectId' Invalid column name 'Archive_VersionId'. Invalid column name 'Archive_ObjecId'.... repeated until truncated.

modelBuilder.Entity<Change>()
   .HasMany(chg => chg.ArchivedObjects)
   .WithMany(arch => arch.Changes);

... gives the error SqlException: Invalid object name 'dbo.ChangeArchivedObjects'..

"it seems EF doesn't like that Archive has a composite key, but I try to use only ObjectId, not VersionId, in the relation." what actual issue are you seeing? And is there any reason you don't want to use EF Core? — Charlieface
– Charlieface, Commented Oct 21 at 16:14
The inner exception with the code in the shown state is 'SqlException: Invalid object name 'dbo.ArchiveChanges'. Will edit question with some cases I've tried and the messages from each. The app is a .Net Framework app, not a .Net Core app. That's why I dont think I can use EF Core. — Joachim J
– Joachim J, Commented Oct 22 at 8:25
Updated the question with some configurations and their exceptions. Removed the 'general impression', since it was based on less systematic code-juggling. — Joachim J
– Joachim J, Commented Oct 22 at 9:50
Is it possible to define your own SQL for populating individual properties/collection properties? The database is basically read-only, so I don't need to worry about updates. — Joachim J
– Joachim J, Commented Oct 22 at 12:43

Joachim J · Accepted Answer · 2025-10-22 14:30:27Z

1

I haven't been able to solve the problem as stated, but I think I have a workaround that will work for me.

- Just make the relations go through the Current object instead. To do that, I just need to add a Current property to Archive, and an ArchivedObjects property to Current.

public class Archive
{
  ...
  public Current Current { get;set; } 
}

public class Current
{
   ... 
   [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
   public virtual ICollection<Archive> ArchivedObjects { get; set; }
}

modelBuilder.Entity<Archive>()
   .HasRequired(arch => arch.Current)
   .WithMany(cur => cur.ArchivedObjects)
   .HasForeignKey(arch => arch.ObjectId)
   .WillCascadeOnDelete(false);

This will let me change the relations to:

public class Archive : ITrObject
{
   ...
   public ICollection<Change> Changes => Current.Changes
}

public class Change
{
   ...
   public ICollection<Archive> ArchivedObjects => Current.ArchivedObjects
}

I'm working on the code and the tests now, but I'm hopeful that it will work out.

answered Oct 22 at 14:30

Joachim J

2042 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Joachim J Oct 23 at 15:42

It worked out in the end. The workaround does, however, have some flaws, so I will wait before flagging it as the answer. For instance, I had a lot of code working on IQueryables on the interface 'ITrObject', that had to be reworked since it used LinQ queries with conditions on the 'ITrObject.Changes'-collection. And on the Archive objects, that collection is no longer a database relation, so LinQ barfs. Long story short: If somebody can come up with a solution for the original problem, I will accept that answer instead.

Collectives™ on Stack Overflow

Trying to populate very similar relations through same property

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related