Join two tables and get only one record of the second table for each record of the first table

I have two model classes; the first contains all the fields of the table which are not multilanguage fields.

public partial class Brand
{
    public long BrandID { get; set; }
    public string Name { get; set; } = "";
    public virtual ICollection<BrandTranslation>? BrandTranslations { get; set; }
}

The second model is used to store the multilanguage fields:

public partial class BrandTranslation : TranslationEditorFields
{
    public long BrandTranslationID { get; set; }
    public string Language { get; set; } = string.Empty;
    public string Title { get; set; } = "";

    public long BrandID { get; set; }
    [ForeignKey("BrandID")]
    public virtual Brand? Brand { get; set; }
}

The second table can have several records for each of the records of the first table. For the second table the Language field can have an empty string "" which means that this record will be the default so that it will be used if a specific language is required but does not exists. Below is a list of sample data that the tables can have.

Brand

BrandTranslation

My problem is that I want to create a Linq command to make the following. I want to be able to get all the records by defining the language that we want to get. For example if I need the English version I will get the following values

If I ask the Italian version of the list I will get a list with records and for our example I will get the default value because there is no Italian translation

I also want to be able to sort or filter using one of these columns The best code which gets close to what I want to do is the following

var dataIQueryable = context.Brands
    .Join(
        context.BrandTranslations
            .Where(m => m.Language == "" || m.Language == lang)
            .OrderByDescending(m => m.Language),
        brand => brand.BrandID,
        translation => translation.BrandID,
        (brand, translation) => new { Brand = brand, Translation = translation }
    );

// Search  
if (!string.IsNullOrEmpty(DT.Search))
{
    dataIQuerable = dataIQueryable.Where(m => m.Brand.Name.ToLower().Contains(DT.Search.ToLower()) ||
                                             m.Brand.Slug.ToLower().Contains(DT.Search.ToLower()) ||
                                             m.Translation.Title.ToLower().Contains(DT.Search.ToLower())
                                       );
}

// Sorting  
if (!(string.IsNullOrEmpty(DT.SortColumn) && string.IsNullOrEmpty(DT.SortColumnDirection)))
{
    if (DT.SortColumn.Equals("name"))
        dataIQueryable = DT.SortColumnDirection.Equals("asc") ? dataIQueryable.OrderBy(m => m.Brand.Name) : dataIQueryable.OrderByDescending(m => m.Brand.Name);
    else if (DT.SortColumn.Equals("slug"))
        dataIQueryable = DT.SortColumnDirection.Equals("asc") ? dataIQueryable.OrderBy(m => m.Brand.Slug) : dataIQueryable.OrderByDescending(m => m.Brand.Slug);
    else if (DT.SortColumn.Equals("title"))
        dataIQueryable = DT.SortColumnDirection.Equals("asc") ? dataIQueryable.OrderBy(m => m.Translation.Title) : dataIQueryable.OrderByDescending(m => m.Translation.Title);
}

var data = dataIQueryable.ToList();

This code first makes the join of the two tables. Then I make the search. I have separate code because a search may not required or additional filters can be applied. Finally I try to sort the records using the parameters received from the browser.

This has as a result the filtering and the paging to work but I can get double records because there are both the default language and the selected language. I manage to solve this problem by replacing the last line with the following.

var data = dataIQueryable.Select(m => m.Brand).Distinct().Include(m => m.BrandTranslations).ToList();

With this line I get only the Brands from the list, I use Distinct to remove the duplicated records and then I use the include method to get the translations. This solves the duplicate records problem but I loose the initial sorting because the Distinct resets the predefined sorting.

Can someone suggest a solution for one of my scenarios or suggest a different way to implement it.

Consider rewrite LINQ query to use Query syntax for better readability. We limit the translations to the first one that matches the language or is empty, ordered by language preference.

var dataQueryable = 
    from brand in context.Brands
    from bt in context.BrandTranslations
        .Where(m => (m.Language == lang || m.Language == "") && m.BrandID == brand.BrandID)
        .OrderByDescending(m => m.Language)
        .Take(1)
        .DefaultIfEmpty()
    select new { Brand = brand, Translation = bt };