I'm using EF Core Version 6.0.9 to connect to a database to export my data. Because of that I used database first approach and autogenerated my model with help of the Entity Framework. I load my tables with eager loading. Most things work correctly, but I can't include one table (BeschaeftigungsAddendum), because it's throwing an error:
Invalid column name 'BeschaeftigungBid'
I assume, that at this point Entity Framework creates a shadow key, because I never defined an attribute BeschaeftigungsBid, nor is something like that in the database.
My problem is, that I defined, as written in the documentation and many other threads, a foreign key. But it seems like the Entity Framework is still creating a shadow key.
Building the entity:
modelBuilder.Entity<BeschaeftigungsAddendum>(entity =>
{
entity.HasKey(e => e.Baid);
entity.HasIndex(e => new { e.Beschaeftigung, e.Addendum }, "IX_BeschaeftigungsAddendum")
.IsUnique();
entity.Property(e => e.Baid).HasColumnName("BAID");
entity.Property(e => e.Addendum)
.IsRequired()
.HasMaxLength(64)
.IsUnicode(false);
entity.Property(e => e.Expires)
.HasColumnType("datetime")
.HasDefaultValueSql("('9999-12-31T23:59:59')");
entity.Property(e => e.LastUpdate)
.IsRequired()
.HasMaxLength(20)
.IsUnicode(false)
.IsFixedLength();
entity.Property(e => e.Lastversion)
.IsRequired()
.IsRowVersion()
.IsConcurrencyToken()
.HasColumnName("lastversion");
entity.Property(e => e.Wert)
.IsRequired()
.HasMaxLength(256);
entity.HasOne(d => d.BeschaeftigungNavigation)
.WithMany(p => p.BeschaeftigungsAddendum)
.HasForeignKey(d => d.Beschaeftigung)
.HasConstraintName("FK_BeschaeftigungsAddendum_Beschaeftigung");
});
Entity Build Beschaeftigung:
modelBuilder.Entity<Beschaeftigung>(entity =>
{
entity.HasKey(e => e.Bid);
entity.HasComment("Nachweis der Beschaeftigungsverhaeltnisse");
entity.HasIndex(e => new { e.Mitarbeiter, e.Beginn, e.KostenStelle, e.DatenQuelle }, "IX_Beschaeftigung")
.IsUnique();
entity.Property(e => e.Bid)
.HasColumnName("BID")
.HasComment("Schluessel");
entity.Property(e => e.Beginn)
.HasColumnType("datetime")
.HasComment("Beschaeftigungsbeginn (W3CDTF)");
entity.Property(e => e.DienstBezeichnung).HasComment("Verweis nach Dienstbezeichnung");
entity.Property(e => e.Einrichtung).HasComment("Verweis nach Diensstelle");
entity.Property(e => e.Ende)
.HasColumnType("datetime")
.HasComment("Beschaeftigugsende (W3CDTF)");
entity.Property(e => e.Expires)
.HasColumnType("datetime")
.HasDefaultValueSql("('9999-12-31T23:59:59')");
entity.Property(e => e.KostenStelle)
.IsRequired()
.HasMaxLength(10)
.IsUnicode(false)
.HasComment("Verweis nach Diensstelle");
entity.Property(e => e.LastUpdate)
.IsRequired()
.HasMaxLength(20)
.IsUnicode(false)
.IsFixedLength()
.HasComment("Zeitstempel (W3CDTF)")
.UseCollation("Latin1_General_CI_AS");
entity.Property(e => e.Lastversion)
.IsRequired()
.IsRowVersion()
.IsConcurrencyToken()
.HasColumnName("lastversion")
.HasComment("wegen Concurrency");
entity.Property(e => e.Updater).HasComment("Verweis nach Person");
entity.Property(e => e.Wd).HasColumnName("WD");
entity.HasOne(d => d.DatenQuelleNavigation)
.WithMany(p => p.Beschaeftigung)
.HasForeignKey(d => d.DatenQuelle)
.OnDelete(DeleteBehavior.ClientSetNull)
.HasConstraintName("FK_Beschaeftigung_DatenQuelle");
entity.HasOne(d => d.DienstBezeichnungNavigation)
.WithMany(p => p.Beschaeftigung)
.HasForeignKey(d => d.DienstBezeichnung)
.OnDelete(DeleteBehavior.Cascade)
.HasConstraintName("FK_Beschaeftigung_Dienstbezeichnung");
entity.HasOne(d => d.EinrichtungNavigation)
.WithMany(p => p.Beschaeftigung)
.HasForeignKey(d => d.Einrichtung)
.OnDelete(DeleteBehavior.ClientSetNull)
.HasConstraintName("FK_Beschaeftigung_Einrichtung");
entity.HasOne(d => d.MitarbeiterNavigation)
.WithMany(p => p.Beschaeftigung)
.HasForeignKey(d => d.Mitarbeiter)
.OnDelete(DeleteBehavior.ClientSetNull)
.HasConstraintName("FK_Beschaeftigung_Mitarbeiter");
});
Model class Beschaeftigung (employment):
public partial class Beschaeftigung
{
public Beschaeftigung()
{
BeschaeftigungsAddendum = new HashSet<BeschaeftigungsAddendum>();
BueroAnschrift = new HashSet<BueroAnschrift>();
}
/// <summary>
/// Schluessel
/// </summary>
public int Bid { get; set; }
public int Mitarbeiter { get; set; }
/// <summary>
/// Beschaeftigungsbeginn (W3CDTF)
/// </summary>
public DateTime Beginn { get; set; }
/// <summary>
/// Beschaeftigugsende (W3CDTF)
/// </summary>
public DateTime Ende { get; set; }
public bool Wd { get; set; }
/// <summary>
/// Verweis nach Dienstbezeichnung
/// </summary>
public int? DienstBezeichnung { get; set; }
/// <summary>
/// Verweis nach Diensstelle
/// </summary>
public int Einrichtung { get; set; }
/// <summary>
/// Verweis nach Diensstelle
/// </summary>
public string KostenStelle { get; set; }
public DateTime Expires { get; set; }
/// <summary>
/// Verweis nach Person
/// </summary>
public int Updater { get; set; }
/// <summary>
/// Zeitstempel (W3CDTF)
/// </summary>
public string LastUpdate { get; set; }
/// <summary>
/// wegen Concurrency
/// </summary>
public byte[] Lastversion { get; set; }
public int DatenQuelle { get; set; }
public virtual DatenQuelle DatenQuelleNavigation { get; set; }
public virtual Dienstbezeichnung DienstBezeichnungNavigation { get; set; }
public virtual Einrichtung EinrichtungNavigation { get; set; }
public virtual Mitarbeiter MitarbeiterNavigation { get; set; }
public virtual ICollection<BeschaeftigungsAddendum> BeschaeftigungsAddendum { get; set; }
public virtual ICollection<BueroAnschrift> BueroAnschrift { get; set; }
}
The model class BeschaeftigungsAddendum:
public partial class BeschaeftigungsAddendum
{
public int Baid { get; set; }
public int Beschaeftigung { get; set; }
public string Addendum { get; set; }
public string Wert { get; set; }
public DateTime Expires { get; set; }
public int Updater { get; set; }
public string LastUpdate { get; set; }
public byte[] Lastversion { get; set; }
public virtual Beschaeftigung BeschaeftigungNavigation { get; set; }
}
I load the context like this:
using (var kommDBContext = new kommdbContext(kommDBConnection))
{
queryContext = kommDBContext.Person;
queryContext = queryContext
.Include(p => p.Mitarbeiter)
.ThenInclude(m => m.Beschaeftigung)
.ThenInclude(b => b.BeschaeftigungsAddendum);
var sql = queryContext.ToQueryString();
result = queryContext.ToList();
}
What I tried so far:
- I tried to rename
BeschaeftigungsAddendum.Beschaeftigung - I tried to add explicit in the
BeschaeftigungsAddendumclass a foreign key. - I tried to add an attribute
BescaheftigungsBidand not map it. But most of those solutions, just lead to a different name of the automatic generated shadow key, but Entity Framework still creates a shadow key that can't be found.
I also tried to recreate all of the model
I can't understand why this attribute is automatically added in the SQL query, even though correct keys are defined.
The relevant part of the query is
SELECT [b].[BID], [b].[Beginn], [b].[DatenQuelle], [b].[DienstBezeichnung], [b].[Einrichtung], [b].[Ende], [b].[Expires], [b].[KostenStelle], [b].[LastUpdate], [b].[lastversion], [b].[Mitarbeiter], [b].[Updater], [b].[WD], [b0].[BAID], [b0].[Addendum], [b0].[Beschaeftigung], [b0].[BeschaeftigungBid], [b0].[Expires] AS [Expires0], [b0].[LastUpdate] AS [LastUpdate0], [b0].[lastversion] AS [lastversion0], [b0].[Updater] AS [Updater0], [b0].[Wert]
FROM [Beschaeftigung] AS [b]
LEFT JOIN [BeschaeftigungsAddendum] AS [b0] ON [b].[BID] = [b0].[Beschaeftigung]
[b0].[BeschaeftigungBid] is nowhere defined.
Thank you for your help.
Best regards.