c# - When should I use navigation properties to query the database?
问题描述
In my Entity Framework 6 application, I have a table of people's email addresses:
public class EmailAddress
{
public int Id { get; set; }
public int PersonId { get; set; }
public string EmailAddress { get; set; }
public virtual Person Person { get; set; }
}
And the Person
object references these email addresses also:
public class Person
{
public int Id { get; set; }
{...}
public virtual ICollection<EmailAddress> EmailAddresses { get; set; }
}
If I want to get all the email addresses for a single person and check whether the person actually exists, would it be more efficient to either:
Run an
Any()
query on thePersons
table and then another query on theEmailAddresses
table, usingPersonId
as a parameter:public IEnumerable<EmailAddress> GetAddressesByPerson(int personId) { if (!Context.Persons.Any(x => x.Id == personId)) { throw new Exception("Person not found"); } return Context.EmailAddresses.Where(x => x.PersonId == personId).ToList(); }
Get the
Person
object and return theEmailAddresses
navigation property:public IEnumerable<EmailAddress> GetAddressesByPerson(int personId) { var person = Context.Persons.Find(personId); if (person == null) { throw new Exception("Person not found") } return person.EmailAddress; }
解决方案
In case of 1st solution, EF will generate sql query which includes EXISTS
statement. Then, if it exits you will execute completely different 2nd query against to the database.
In case of 2nd solution, you will send just a select ... from Persons where ..
statement. And as you set EmailAddress
as navigational property, if Lazy Loading
is enabled then EF will generate and execute query against EmailAdress
table based on personId
. If Lazy Loading
is not enabled, then EmailAddress
will be null or empty.
As a 3rd option you use Eager Loading
feature, which will let EF to generate join
query and will fecth person
and related EmailAddress
es in one go.
So, if mostly you expect to have correct personId
, then you can switch to Eager Loading
mode. Lazy Loading
is mostly helpful in scnearios, when you need to fetch related entities only in some cases.
By the way, I suggest you to turn on logging in EF, to see generated queries.
As a result, here is the code sample for loading related entities eagerly:
var person = Context.Persons
.Include(s ⇒ s.EmailAddresses)
.FirstOrDefault(x => x.Id == personId);
The key point is to add a call to Include
method and pass the navigational property. Passed entity will be loaded eagerly. And at the end of query you can use any of the methods which will do immediate execution, like First
, FirstOrDefault
, Single
, SingleOrDefault
, ToList
and so on.
You can't use Include
with Find
, because the latter one is the method of DbSet
. In your case the most relevant one is Single
, which will automatically throw exception if there is no person in the table with the specified id.
推荐阅读
- python - 可以单独对多个列进行 GROUP BY 并使用 django ORM 将它们中的每一列聚合到其他列?
- heroku - 如何诊断和减少 NodeJS Heroku App 的启动时间
- java - 如何访问maven多模块父java类?
- javascript - C# selenium 如何在任何页面上打开一个新选项卡
- git - 将 Github 存储库添加到 Jenkins 失败
- amazon-web-services - 在 AWS lambda 上运行 terraform init 和 plan 命令
- node.js - 现有云功能迁移到nodejs14后部署失败
- python - Flask 在 app 文件夹下找不到模板和静态文件夹,而是始终查看根文件夹
- c# - 通过 C# 应用程序在客户端机器上安装 Microsoft SQL Server localdb
- xampp - XAMPP 退出问题,xampp 没有退出,而是显示以下错误