Navigation Property Mapping on two different fields

Question

I have a squirrely situation, where in some relationships a tables ID field is used as the key reference. But in other, older implementations, the Name field was used as the relationship mapping.

FileType:
ID | Name |....

FileTypeDetails:
ID | FileTypeId | ....

PostingGroupDetails :
FileType | ....

PostingGroupDetails.FileType has a map to FileType.Name. FileTypeDetails.FileTypeId maps to FileType.ID.

I am using the Fluent API structure to do the mappings manually but am running into a wall with this relationship mapping.

I though that doing a multi-key mapping might work but am unsure. Still in the design stage, and about to implement the interface side of the project.

Any ideas on how i make the mapping available to both, until we can consolidate the relationship mapping to one or the other?

EF Implementation:

FileType

I know FileType is currently Optional, but its only a direct correlation to the Table design. I am flagging it for schema updating, currently in practice it is a required field in order for the entry to be submitted.

ToTable( "FileType" , "Int" );

HasKey( ftd => ftd.ID );
Property( ftd => ftd.ID ).HasColumnName( "ID" )
    .HasDatabaseGeneratedOption( DatabaseGeneratedOption.Identity );
Property( ftd => ftd.Name).HasColumnName( "Name" )
    .IsOptional();
//Navigation Properties
HasMany( ftd => ftd.FileNames )
    .WithRequired( fn => fn.FileType );
HasMany( ftd => ftd.PostingGroupDetails )
    .WithRequired( pgd => pgd.FileTypeDetails );

FileNames

FileNames.FileTypeID relates to FileType.ID

ToTable( "FileNames" , "Int" );

HasKey( fn => fn.ID );
Property( fn => fn.ID ).HasColumnName( "ID" )
    .HasDatabaseGeneratedOption( DatabaseGeneratedOption.Identity );
Property( fn => fn.FileTypeID ).HasColumnName( "FileTypeID" )
    .IsRequired();
//Navigation Properties
HasRequired( fn => fn.FileType )
    .WithMany( ftd => ftd.FileNames )
    .HasForeignKey( fn => fn.FileTypeID );

PostingGroupDetails

PostingGroupDetails.FileType relates to FileType.Name

ToTable( "PostingGroupDetails " , "Int" );

HasKey( pgd => pgd.ID );
Property( pgd => pgd.ID ).HasColumnName( "ID" )
    .HasDatabaseGeneratedOption( DatabaseGeneratedOption.Identity );
//Required Properties
Property( pgd => pgd.FileType ).HasColumnName( "FileType" )
    .IsRequired();

//Navigation Properties
HasRequired( pgd => pgd.FileTypeDetails )
    .WithMany( ftd => ftd.PostingGroupDetails );

Any ideas would be greatly appreciated. Refactoring the Database structure is not an option right now, but it is on the plate to be done.

Do the older tables (like PostingGroupDetails) have primary keys? — Gert Arnold, Jan 27 '14 at 20:25
Not necessarily. I am in the redev stage, so in the future i will do it properly. But right now i have to design with existing design patterns, while also prepping for "proper" design patterns. All of them have Identity Fields but they did not declare them as PK, consistently. Although the `FileType.Name` field references are Unique, but no UK index was declared. — GoldBishop, Jan 27 '14 at 20:58
You'll have to choose which fields you want to mark as PK in the class model. You can only define associations to PKs that are known by EF and there can only be one PK per entity. So for `FileType` you must either take `ID` or `Name` and then you can define an association with `FileTypeDetails` *or* `PostingGroupDetails`. For the other associations you know exist you'll have to write join queries. — Gert Arnold, Jan 27 '14 at 21:26
So hard map the ones that goto the PK. For the others dont define a Navigation Property, but instead use the LINQ framework to obtain it by name. I assume that is what your talking about? Something like `Entity.Where(ft => ft.Name == name)`? — GoldBishop, Jan 27 '14 at 21:39
Yes, that's basically it, or `join y from Y on x.Name equals y.Name`. — Gert Arnold, Jan 27 '14 at 21:42
@GertArnold If you could provide that as the answer ill mark you +1 answer ;) — GoldBishop, Jan 27 '14 at 21:45

score 1 · Accepted Answer · answered Jan 27 '14 at 21:52

Entity Framework allows for differences between the database model and the class model (or conceptual model). You can define primary keys in the conceptual model that don't exist, or aren't even constrained as unique, in the data model.

So you can (and have to) choose which fields you want to mark as PK in the conceptual model. You can only define associations to PKs that are defined in the conceptual model and there can only be one PK per entity. So for FileType you must either take ID or Name and then you can define an association with FileTypeDetails or PostingGroupDetails. For the other associations you know exist you'll have to write join queries. As in

from x in X
join y from Y on x.Name equals y.Name
select ...

Navigation Property Mapping on two different fields

EF Implementation:

FileType

FileNames

PostingGroupDetails

1 Answers1