EF 6 select from other table without navigation property

Question

I have a small problem which I need a help to solve:

I have following situation:

For example: I want to select all students who have a dog.

I have 2 tables:

students 
id name petid

pet
id name 

BUT there is no specified foreign key between them no navigation property, although I have but I haven't specified it and I don't want for my case, but I still want to make a correct select statement.

So with navigation property i could query like this:

var students = (student s in context.students where s.Pet.Name.Equals("dog").ToList();

I would avoid doing this also

var students = context.students

foreach(student s in students)
{
    string pet = (from pet p in context.pets where p.Id==s.PetId select p.name).SingleOrDefault();
     if(pet=="dog")
     { 
         //do something
     } 
}

Of course it would be easy to make navigation property, but for my case I really don't want to.

So my question is how can i do this kind of query simple and with only one to DB?

Answer 1

Use a join.

var students = (from s in context.students
  join p in context.pets on s.petid equals p.id
  where p.name == "dog"
  select s).ToList();

For the lambda syntax, you can use this:

var students = context.students.Join(context.pets.Where(p => p.name== "dog"), //filter the pets
                             student => student.PetId, //left side key for the join
                             pet => pet.id, //right side key for the join
                             (student, pet) => student); //what do you want to select