linq - Stuck into "Include" limitation (need to join by custom fields in addition to key fields)

Question

I have the following class:

public class A {
  public B B_a_1 { get; set; }
  ...
  public B B_a_[N] { get; set; }
}

public class C {
  public B B_c_1 { get; set; }
  ...
  public B B_c_[M] { get; set; }
}

Class B is mapped to the table via OnModelCreating and has a composite key. But one of the fields in this key is a language code: in some cases it's a current thread language, in other cases all languages need to be selected - for instance, when admin is going to edit localizations (it's a client-created DB for many years and the structure is not going to be changed). This poses the problem of data selection for me. I cannot use Include as-is, because Language code needs to be joined conditionally. Due to large amount of different kinds of B entities (which differ by code - one of key fields) I cannot create a .NET class per each B entity, inheriting it from base class and use HasDiscriminator, HasQueryFilter in base class and stuff like that. In fact, what I now need is to select specific type of B entity by code, using some Extension method like that (pseudo-code is following):

DbSet<A>.Include(x => x.B_a_1).Where(x => x.B_a_1.LanguageCode = "E").Include(x => x.B_a_[N]).Where(x => x.B_a_[N].SomeProperty = "Something")

which would be translated to:

FROM Table_A a
LEFT JOIN Table_B b1 ON b1.Code = a.Code AND b1.LanguageCode = 'E'
LEFT JOIN Table_B b2 ON b2.Code = a.Code AND b2.SomeProperty = 'Something'

I need to 'group' include-where to be able to independently control JOIN conditions per each B-kind entity.

Answer 1

I ended up with the following approach.

Having for instance the class ModuleLanguage with child entity Language, which cannot be explicitly bound via key relationship:

public class ModuleLanguage
{
    ...
    public string LanguageCode { get; set; }
    public FixCodeValue Language { get; set; }
    ...
}

1. I ignore such property in model creation:

       builder.Entity<ModuleLanguage>(b =>
       {
            b.Ignore(x => x.Language);
       }
   

2. manual load this property (FixCodeValue class is actually used by many child entities and all of them need to be selected from the same DB table, but based on different criteria):

        var currentLanguageCode = DbContext.CurrentLanguage;
   
        await query.ForEachAsync(moduleLanguage =>
        {
            moduleLanguage.Language = DbContext.FixCodeValues
                .FirstOrDefault(language =>
                    language.DomainId == CentralToolsDomainTypes.CentralTools
                    && language.CodeName == CentralToolsFieldTypes.LANGUAGE
                    && language.StringValue == moduleLanguage.LanguageCode
                    && language.LanguageCode == currentLanguageCode);
        });