score:0

this will do the trick:

iqueryable<animal> query = 
    from animal in context.animals
    where animal.animalaliases
        .any(a => a.alias.contains("userinput"))
    select animal;

alternatively, you can do it the other way around (start at the animalalias entity instead of starting at the animal entity):

iqueryable<animal> query = (
    from animalalias in context.animalaliases
    where animalalias.alias.contains("userinput")
    select animalalias.animal)
    .distinct();

score:3

i think what you're looking for is just a normal where clause?

var query = from animal in context.animals
   join animalalias in context.animalaliases on animal.animalid equals animalalias.animalid
   where animalalias.alias.contains(userinput)
   select animal;

the alias text is not part of the foreign key relationship - so it shouldn't be in the join.


update - after comments - including from @steven - while the query above is correct and does mimic the original sql - it might be wise to use distinct() on the output - this will prevent the case where multiple alias matches could occur for a single animal.

var query = (from animal in context.animals
   join animalalias in context.animalaliases on animal.animalid equals animalalias.animalid
   where animalalias.alias.contains(userinput)
   select animal).distinct();

Related Query

More Query from same tag