Linq: select elements on the N side 1: N relationship

Question

I have a table called Text (what is called a name) and a TextTranslations table

A text can have several text translations (texttranslation has FK textid for text.textid) It also has a translation property.

Now I have a list of text objects, all of which have 1 or more child texttranslation objects.

and I want to match all text objects that have a child object (texttranslation) with the 'translation' property equal to something.

so in pseudo code it will be:

list.Where (1 or more z.childs.texttranslation contains 'bla')

perhaps?

Answer 1

from text in context.Text
    where text.TextTranslations.Any(tt => tt.Translation.Contains("bla"))
select text;

or

context.Text
    .Where(
        text => text.TextTranslations.Any(tt => tt.Translation.Contains("bla")
     );

or

(from textTranslation in context.TextTranslations
    where textTranslation.Translation.Contains("bla")
select textTranslation.Text)
.Distinct();

or

context.TextTranslations
    .Where(textTranslation => textTranslation.Translation.Contains("bla"))
    .Select(textTranslation => textTranslation.Text)
    .Distinct();