Entity Framework - relationship with fake Foreign Key (no foreign key in the db)

Question

I have many tables that have TextID column which refers to the translation table. Translation table needs also LanguageID to get translated text in desired language. My problem is that I do not have LanguageID in my database, it is predefined in the system and I do not know how can I define it using Fluent API, i.e this can be my model:

public partial class MyEntity
{    
    public short ID { get; set; }
    public Nullable<int> TextID { get; set; }
    [NotMapped]
    public Nullable<int> LanguageID { get; set; }
    public virtual TEXT_TRANSLATION Translation { get; set; }
}

And the translation table:

public partial class TEXT_TRANSLATION
{
    [Key, Column(Order = 0)]
    public int TextID { get; set; }

    [Key, Column(Order = 1)]
    public int LanguageID { get; set; }

    public string TranslatedText { get; set; } 
}

Basically I need navigation like this:

myEntity.Translation.TranslatedText

While using SQL, I would do it like this:

Left Join TEXT_TRANSLATION ON 
  MyEntity.TextID = TEXT_TRANSLATION.TextID 
  AND TEXT_TRANSLATION.LanguageID = 1033 

Basically I want to use TextID foreign key and get ONLY ONE translation - LanguageID is static and predefined in context.

I can't change existing DB schema. It would be perfect if I won't need to map LanguageID field in my code, just use it inside mapping like a system parameter. Is it even possible with EF?

Answer 1

If your LanguageID is static you can try to use this hack.

Define your entities like:

public class Entity {
    public int Id { get; set; }
    public int TextId { get; set; }
    public Translation Translation { get; set; }
}

// No LanguageId in translation
public class Translation {
    public int TextId { get; set; }
    public string TranslatedText { get; set; }
}

And add this fluent mapping to OnModelCreating in your derived DbContext:

// Define foreign key
modelBuilder.Entity<Entity>()
            .HasRequired(e => e.Translation)
            .WithMany()
            .HasForeignKey(e => e.TextId);

// Trick - EF believes that only TextId is PK. Without this trick you cannot
// make navigation property on your entity
modelBuilder.Entity<Translation>()
            .HasKey(t => t.TextId);

// If you are going to insert translations as well your TextId cannot be 
// handled as autogenerated column
modelBuilder.Entity<Translation>()
            .Property(t => t.TextId)
            .HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);

// The HACK - conditional mapping. This tells EF to "see" only records
// with LanguageId set to 1033. Without this hack you cannot filter 
// translations for only single language.
modelBuilder.Entity<Translation>()
            .Map(m => {
                        m.Requires("LanguageId").HasValue(1033);
                        m.ToTable("Translations");   
                    });

The hack is based on concept used for TPH mapping but in this case you are only using single entity type to load only subset of records with predefined LanguageId. Even FK from the main entity should work because you cannot have to translations with the same TextId - it would mean that they also have the same LanguageId which is not possible because TextId and LanguageId form primary key.

I'm not sure if there is any hidden issue in this solution. I just gave it a quick try and it worked.