EF 6 select from other table without navigation property

c#, entity-framework, linq-to-sql, sql

Solution

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

Problem

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?

Original source

Related problems