one-to-many relation using two columns in Entity F

Question

In my project I have a table Translation that can have translations for any model. To achieve this, the table has two fields: Model and ModelId. The Model property holds an integer indicating the type of the model and the ModelId has the id of this model.
So, for example: the Product table has modeltype id 1. To get all translations for a product with id 317, I search for translations with Model=1 AND ModelId=317.

Now I would like to create this relation in Entity Framework Core. All my models inherit from the class BaseModel that has a property ModelType holding the id of the model type. This field is not mapped, so it is not available in the database.

I have tried to create the relation using fluent api, but it doesn't allow me to specify more columns to filter on.

modelBuilder.Entity<BaseModel>()
    .HasMany<Translation>(bm => bm.Translations)
    // Extra filters

Is there any way to create this relation without having to manually create a join for every query that requires translations?

Answer 1

Yes, there is. Use this:

modelBuilder.Entity<BaseModel>()
.HasMany(bm => bm.Translations)
.WithForeignKey(x => new { x.Key1, x.Key2 });

Key1 and Key2 obviously need to be the keys to the relation and you may also need to define them as such (in the same order) for the Translation entity.

Answer 2

Since modelBuilder.Entity<BaseModel>() will use TPH inheritance approach, I assume you are not using EF code first approach for database creation and you are using it to map your models to an existing database. Then you can try something like this:

Models:

public class Translation
{
    public int Id { get; set; }
    public int Model { get; set; }
    public int ModelId { get; set; }
}

public class BaseModel
{
    public BaseModel(int modelType)
    {
        ModelType = modelType;
    }
    public int Id { get; set; }
    public int ModelType { get; set; }

    public ICollection<Translation> Translations { get; set; }// only for internal use
    public IEnumerable<Translation> ModelTypeTranslations
    {
        get
        {
            return this.Translations.Where(t => t.Model == this.ModelType);
        }
    }

}

public class SomeModel : BaseModel
{
    public SomeModel() : base(1) { }
    public int SomeProperty { get; set; }
}

public class AnotherModel : BaseModel
{
    public AnotherModel() : base(2) { }
    public int AnotherProperty { get; set; }
}

DbContext:

public class MyDbContext: DbContext
{
    ...

    public DbSet<Translation> Translations { get; set; }
    public DbSet<SomeModel> SomeModels { get; set; }
    public DbSet<AnotherModel> AnotherModels { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        ...
        modelBuilder.Entity<Translation>().HasKey(e => e.Id);

        var baseModelTypes = typeof(BaseModel).Assembly.GetExportedTypes()
            .Where(t => typeof(BaseModel).IsAssignableFrom(t) && t != typeof(BaseModel)).ToList();

        foreach (var type in baseModelTypes)
        {
            modelBuilder.Entity<Translation>().HasOne(type).WithMany(nameof(BaseModel.Translations)).HasForeignKey(nameof(Translation.ModelId));

            modelBuilder.Entity(type).Ignore(nameof(BaseModel.ModelType));
            modelBuilder.Entity(type).Ignore(nameof(BaseModel.ModelTypeTranslations));
            modelBuilder.Entity(type).HasKey(nameof(BaseModel.Id));
        }
    }
}

As you can see you can use ModelTypeTranslations to get Translations only for current model type.

I should note this approach may have performance issues since it filters Translations by ModelType only in memory. Also I tried to avoid filtering in memory by using lazy loading but I got some exception even if I just installed that package without invoking optionsBuilder.UseLazyLoadingProxies(). I hope it will be fixed in the next releases.