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 Articles