I have 3 tables, one is kind of the base table and is big. Then I have an extension table which is also big Then I have a tiny table with only one attribute except for the PK
In essence the primary key for all 3 tables is the same, except for the name first table :
[Table("KLANTEN")]
public class Klant
{
public Klant()
{
this.ContactPersonen = new HashSet<ContactPersoon>();
this.Afleveringen = new HashSet<Aflever>();
}
[Key]
[StringLength(8000)]
public string NUMMER { get; set; }
.... removed for brevity
#region Navigation Properties
public virtual Aanspreking Aanspreking { get; set; }
public virtual PuntenKlant PuntenKlant { get; set; } // fail
public virtual ExtraVeldenKlant ExtraVeldenKlant { get; set; } //ok
public virtual ICollection<ContactPersoon> ContactPersonen { get; set; }
public virtual ICollection<Aflever> Afleveringen { get; set; }
#endregion
}
second table:
[Table("EXVKLA")]
public class ExtraVeldenKlant
{
[Key]
[StringLength(8000)]
public string NUMMER { get; set; }
... removed for brevity
#region Navigation Properties
public virtual Klant Klant { get; set; }
#endregion
}
third table:
[Table("[_CTPUNT]")]
public class PuntenKlant
{
[Key]
[StringLength(8000)]
public string KODE { get; set; }
[StringLength(8000)]
public string OMSCHR { get; set; }
#region Navigation Properties
public virtual Klant Klant { get; set; }
#endregion
}
As you see the key is defined alsways on the first column not using the fluent api. I like it that way and the way I understood it, fluent api and attribute syntax can be mixed right ?
So for the configuration i am doing this :
class KlantConfiguration : EntityTypeConfiguration<Klant>
{
public KlantConfiguration()
{
.... removed for brevity
this.HasMany(e => e.ContactPersonen)
.WithRequired(e => e.Klant)
.HasForeignKey(e => e.KLANTCODE)
.WillCascadeOnDelete(false);
this.HasMany(e => e.Afleveringen)
.WithRequired(e => e.Klant)
.HasForeignKey(e => e.KLANTCODE)
.WillCascadeOnDelete(false);
this.HasOptional(e => e.PuntenKlant)
.WithRequired(f => f.Klant);
this.HasOptional(e => e.ExtraVeldenKlant)
.WithRequired(e => e.Klant);
}
}
executing following SQL statement just to verify everything is in place :
select klanten.nummer,P.KODE,E.NUMMER,p.OMSCHR from klanten
inner join "_CTPUNT" P on P.KODE=klanten.nummer
inner join exvkla E on E.NUMMER=klanten.nummer
where klanten.nummer='000002'
results in :
NUMMER KODE NUMMER OMSCHR
======== ====== ======== ========================================
000002 000002 000002 571
So this proves that they key is present in all three tables
Now if I try this using the real stuff in EF :
var puntenquery =
from p in repoKlant.Queryable().Include("PuntenKlant").Include("ExtraVeldenKlant")
where p.NUMMER == "000002"
select p;
then i get the weird result that the navigation property PuntenKlant is null But the navigation property ExtraVeldenKlant is properly filled in. I fail the see where i am going wrong. Debugging EF shows that the query is in fact correctly executed but it is quite large, but the fields from that particular navigation property are int the query that is generated by EF. Though it looks as if through the materialisation process it does not work.
What am i doing wrong or is there a bug in EF, or in the pervasive ADO.NET driver, this is not SQL Server
