Filtering on the Collection Navigation property

Question

I would like to filter my 'TranslationSet' entities, based on their 'Translations' Collection Navigation Property.

E.g.

If a 'Translation' has a 'LanguageId' of 5 (Italian), then the 'TranslationSet' that contains this 'Translation' should be removed from the result.

Here are my Entity classes:

public class Language
{
    public int LanguageId { get; set; }
    public string NationalLanguage { get; set; }

    //Make table multi tenanted.
    public int TenantId { get; set; }
    public ApplicationTenant Tenant { get; set; }

    public List<Translation> Translation { get; set; } = new List<Translation>();

}

public class Translation
{
    public int TranslationId { get; set; }
    public string TranslatedText { get; set; }

    public int LanguageId { get; set; }
    public Language Language { get; set; }

    //Make table multi tenanted.
    public int TenantId { get; set; }
    public ApplicationTenant Tenant { get; set; }


    public int TranslationSetId { get; set; }
    public TranslationSet TranslationSet {get; set;}

}

public class TranslationSet
{
    public int TranslationSetId { get; set; }
    public int TenantId { get; set; }
    public ApplicationTenant Tenant { get; set; } 

    public IEnumerable<Translation> Translations { get; set; }

}

Here is my attempt

From the image you can see that the query fails because a Translation exists with LanguageId of 5.

I have tried many many attempts to resolve this but I can't even get close the LINQ which returns my query correctly.

Please let me know if any further clarification is needed and thanks in advance to anybody who offers help.

Answer 1

My rule of the thumb that nearly always work is: start by querying the entities you want. That will prevent duplicates as you see in your query result. Then add predicates to filter the entities, using navigation properties. That will be:

var sets = TranslationSets // start the query here
    .Where(ts => ts.Translations.All(t => t.LanguageId != 5)); // Filter

Or if you like this better:

var sets = TranslationSets // start the query here
    .Where(ts => !ts.Translations.Any(t => t.LanguageId == 5)); // Filter

EF will translate both queries as WHERE NOT EXISTS.