0

I have problems with some logged entities from a read-only database. Among other, i read 4 tables, EntityOne, EntityTwo, EntityOneLogged and EntityTwoLogged.

EntityOne has a 3-part compositekey, KeyString1, KeyString2, KeyString3
EntityTwo has a 4-part compositekey, KeyString1, KeyString2, KeyString3, KeyString4
EntityOne has a one to many relationship with EntityTwo

this works and is mapped like this:

HasRequired(r => r.EntityOne).WithMany(m => m.EntityTwos).HasForeignKey(f => new { f.KeyString1, f.KeyString2, f.KeyString3 });

EntityOneLogged has a 4-part compositekey, KeyString1, KeyString2, KeyString3, ActionTime
EntityTwoLogged has a 5-part compositekey, KeyString1, KeyString2, KeyString3, KeyString4, ActionTime
EntityOneLogged has a many to many relationship with EntityTwo (not mappable)

The logged tables can contain removed rows from the normal table

The problem starts here, when i want to map 0..1 to many from EntityTwoLogged to EntityOne

HasOptional(r => r.EntityOne).WithMany(m => m.EntityTwoLogs).HasForeignKey(f => new { f.KeyString1, f.KeyString2, f.KeyString3 });

This fails validation with the following error:

One or more validation errors were detected during model generation: EntityTwoLogged_EntityOne: : Multiplicity conflicts with the referential constraint in Role 'EntityTwoLogged_EntityOne_Target' in relationship 'EntityTwoLogged_EntityOne'. Because all of the properties in the Dependent Role are non-nullable, multiplicity of the Principal Role must be '1'.

Which I interpret as a fault in EF as all combination of composite keys have to exist in all related tables? Or do i miss something?

Erik Karlsson
  • 570
  • 1
  • 6
  • 17
  • 1
    As part of a PK, **they are non nullable** (or at least considered as such)(http://stackoverflow.com/questions/386040/whats-wrong-with-nullable-columns-in-composite-primary-keys). That is the problem. – tschmit007 May 20 '15 at 09:14
  • Yes, most real dbs dont allow null keys, but in this case i have the complete combined key in one table but not the other where it is possible that it have been removed? ie. the PK can exist in one table but not the other – Erik Karlsson May 20 '15 at 09:25
  • I agree, but in your case this can't be overrided (afaik with EF): PK => non nullable => cardinality = 1. The only kind of way I can imagine is to use an identity pk for the logs more the full FK – tschmit007 May 20 '15 at 09:35
  • Yeah sadly i dont have any way to modify the db for that. But one would assume HasRequried = INNER JOIN, HasOptional = LEFT JOIN, no matter how ef think the key works? – Erik Karlsson May 20 '15 at 09:51
  • can't you create a view like `select row_number() over (order_by KS1, KS2, KS3, KS4) as rn, * from EntityTwoLogged` and then you map your entity to the view by declaring rn as a PK and KS1, KS2, KS3 as FK ? (be careful this is a 'non deterministic' PK :), but EF needs a PK ) – tschmit007 May 20 '15 at 12:09

0 Answers0