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?

To make navigation property is not so easy, if someone need it, see https://stackoverflow.com/questions/4465855/entity-framework-add-navigation-property-manually/21455438#21455438 — Michael Freidgeim, Sep 05 '18 at 13:23

Joe Brunscheon · Accepted Answer · 2014-02-03T20:03:17.430

16

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

edited Feb 03 '14 at 20:03

answered Feb 03 '14 at 18:53

Joe Brunscheon

1,949
20
21

I will test it, but just curious what if i use this type of writing query: context.studnets.Where().Where().Select() ... how to use join here? context.students.Where().Join(...) how do i write the join in lambda here? – Alnedru Feb 03 '14 at 18:56
2

Here is an example to do it with Lamda http://stackoverflow.com/questions/5038288/lambda-expression-for-join – Ricky Stam Feb 03 '14 at 19:06
I have never liked the Lambda syntax for the join. It's not nearly as readable, IMHO. – Joe Brunscheon Feb 03 '14 at 19:07

EF 6 select from other table without navigation property

1 Answers1

Linked