Mapping of translated entities. How?

Question

C++ / C#

I am developing a multilingual application in C# (Web API). As ORM I use Entity Framework. For mapping I use Automapper.
Translated entities are stored in the database as follows (example):
Posts: id, category_id, date_updated, date_created
PostTranslations: post_id, language_code, title, text
Languages: code, name
Question: how to map data from database entity classes to DTO? The request can come with 2 parameters: language and fallback_language.
The DTO, in the case of posts, looks like this:

public class PostDto {
    public int Id {get; set;}
    public string Title {get; set;}
    public DateTime CreatedAt {get; set;}
}

Answer 1

You can make a view at the database level, in which you display the post and all its translations, i.e. multiplication of posts by languages ​​+ connection by the code of the language of its translation (here you can also display the default text for a translation that does not exist).
Then, just do:

var posts = db.PostViews.AsNoTracking().Where(x=>x.LanguageCode == CurrentLanguageCode)
  .ProjectTo<PostDto>();

where PostViews is your view in the database, with the fields from the post and at least the language code.