How to write SQL SELECT INNER JOIN with multiple conditions (with LIKE) query to LINQ to SQL

I've been looking up how to do this, and I found something close, but not quite what I'm looking for. I wonder if this might help others as well, but I could really use the help. I've got a pretty simple SELECT statement I need to convert into LINQ to SQL to speed up searches in our software:

SELECT  Animals.*
FROM    Animals 
        INNER JOIN AnimalAliases
        ON  Animals.AnimalID = AnimalAliases.AnimalID 
        AND AnimalAliases.Alias LIKE N'%USERINPUT%';

Basically, I want to be able to do a Inner join with multiple conditions, but one of the conditions has nothing to do with one of the tables, the LIKE statement, which is where I get stuck.

var query = 
    from animal in context.Animals
    join animalAlias in context.AnimalAliases
        on new { animal.AnimalID, "USERINPUT" }
    equals new { animalAlias.AnimalID, animalAlias.Alias }
    select animal;

but that doesn't work obviously, because I dont want EQUALS to 开发者_如何学GoUSERINPUT, I want to perform various LIKE operations on it..

Anyone have any insight?

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();

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();