实体框架 - 与假外键的关系（db中没有外键） [英] Entity Framework - relationship with fake Foreign Key (no foreign key in the db)

问题描述

我有很多表具有指向翻译表的TextID列。翻译表还需要使用LanguageID来获取所需语言的翻译文本。我的问题是我的数据库中没有LanguageID，它是在系统中预定义的，我不知道如何使用Fluent API定义它，也就是说，这可以是我的模型：

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

而翻译表：

  public partial class TEXT_TRANSLATION 
 {
 [Key，Column（Order = 0）] 
 public int TextID {get;组; } 
 
 [Key，Column（Order = 1）] 
 public int LanguageID {get;组; } 
 
 public string TranslatedText {get;组; 
} 
  

基本上我需要这样的导航：

  myEntity.Translation.TranslatedText 
  

在使用SQL时，我会这样做：

 左加入TEXT_TRANSLATION ON 
 MyEntity.TextID = TEXT_TRANSLATION.TextID 
 AND TEXT_TRANSLATION.LanguageID = 1033 
  

基本上我想使用TextID外键， strong> ONLY ONE 翻译 - LanguageID在上下文中是静态的和预定义的。

我无法更改现有的数据库模式。这将是完美的，如果我不需要映射我的代码中的LanguageID字段，只需使用它在映射像系统参数。如果您的 LanguageID 是静态的，那么EF是否可以用EF？

解决方案

您可以尝试使用此黑客。

定义您的实体，如：

  public class Entity {
 public int Id {get;组; } 
 public int TextId {get;组; } 
 public translation translation {get;组; } 
} 
 
 //无翻译中的LanguageId 
 public class Translation {
 public int TextId {get;组; } 
 public string TranslatedText {get;组; } 
} 
  

并将这个流畅的映射添加到 OnModelCreating 你派生的 DbContext ：

  //定义外键
 modelBuilder.Entity< Entity>（）
 .HasRequired（e => e.Translation）
 .WithMany（）
 .HasForeignKey（e =>文本ID）; 
 
 // Trick  -  EF认为只有TextId是PK。没有这个技巧你不能
 //使你的实体的导航属性
 modelBuilder.Entity< Translation>（）
 .HasKey（t => t.TextId）; 
 
 //如果您要插入翻译，您的TextId不能为
 //作为自动生成列处理
 modelBuilder.Entity< Translation>（）
。属性（t => t.TextId）
 .HasDatabaseGeneratedOption（DatabaseGeneratedOption.None）; 
 
 // HACK  - 条件映射。这告诉EF只要记录
 //，将LanguageId设置为1033.没有这个黑客，你不能只为单一语言过滤
 //翻译。 
 modelBuilder.Entity< Translation>（）
 .Map（m => {
 m.Requires（LanguageId）。HasValue（1033）; 
 m.ToTable 翻译）; 
}）; 
  

黑客是基于用于TPH映射的概念，但在这种情况下，您只使用单个实体类型仅加载预定义的 LanguageId 的记录子集。即使来自主实体的FK也应该工作，因为您不能使用相同的 TextId 进行翻译 - 这意味着它们也具有相同的 LanguageId 这是不可能的，因为 TextId 和 LanguageId 形成主键。

我不知道这个解决方案是否有任何隐藏的问题。我刚刚给它一个快速的尝试，它的工作。

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?

解决方案

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.

这篇关于实体框架 - 与假外键的关系（db中没有外键）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！