EF 6从没有导航属性的其他表中选择 [英] EF 6 select from other table without navigation property

问题描述

我有一个小问题，需要帮助解决:

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

我有以下情况:

例如:我想选择所有有狗的学生.

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

我有2张桌子:

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

我也避免这样做

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?

推荐答案

使用联接.

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

对于lambda语法，您可以使用以下代码:

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

这篇关于EF 6从没有导航属性的其他表中选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！