score:1
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.
score:1
An option:
public IEnumerable<EmailAddress> GetAddressesByPerson(int personId)
{
var queryResults = Context.Persons
.Where(x => x.Id == personId)
.Select(x => new { EmailAddresses = x.EmailAddresses })
.Single();
return queryResults.EmailAddresses;
}
The above query asserts that a single Person's e-mail addresses should be returned. You could do a SingleOrDefault
and then check the result for #null to customize the error message, though I tend to keep exception messages pure. We then return the selected collection. So if a person exists, but has no e-mail addresses, you'll receive an empty list. If the person doesn't exist you'll get an Expected 1, found 0 exception. If more than one person exists for the Id (shouldn't, but...) you'll get an Expected 1, found more than one exception. Don't use FirstOrDefault
unless you expect more than one is possible and provide an OrderBy
to ensure the data order is predictable.
Source: stackoverflow.com
Related Articles
- When should I use navigation properties to query the database?
- How can I check the number of calls to the database in LINQ query when using .NET Core and Code First?
- Should I materialize my LINQ query for database performance reasons?
- LINQ Query Using Navigation Properties Generates Multiple SELECT Statements
- LINQ query returns old results when source list is re-initialized
- Linq to Sql NotSupportedException "The explicit construction of the entity in a query is invalid" when selecting new Type of other Database Table
- Linq sub query when using a repository pattern with EF code first
- Using navigation properties in LINQ query causes problems such as relationship multiplicity
- Should my query return null when it doesn't find any items?
- Getting 'Data source is an invalid type' when binding Linq query to Gridview
- How to create an EF query which returns a nested list with navigation properties based on different entity ids?
- How can I build a LINQ predicate/dynamic.LINQ query based off grid filtering when the grid properties don't have the entity properties?
- Linq Query using navigation properties and Where clause
- How can I query a database with LINQ when the column is a number but I have a string in the WHERE?
- date format not correct when pulling from database using linq query
- Is there a way to conditionally include or exclude a navigation property when querying Database using EF Core
- C#: Mapping a database to class properties when DB names and column names will only be known at run time
- Linq query from navigation properties
- LINQ Query returns false when it should be true
- why can I only access the properties of an anonymous type when I add .toList to the end of my linq query
- how to fetch data from database using linq query for relationship 1:N and N:N (between 3 entity) in asp.net mvc EF code first?
- Using navigation properties in linq query throws error if foreign keys are null
- Navigation Properties using 2 different Database Views
- Common expression function for same where condition at different level navigation properties in linq query
- LINQ query with Inner Join not working when multiple records are added to database
- Navigation Properties not returning when included using a loop
- Argument.NullException when dealing with Navigation Properties in Entity Framework
- How to express LEFT JOIN when 2 entities are linked by a navigation properties
- Performance issue with Linq query - navigation properties to blame?
- What should be the return type when writing Linq query for selecting specific columns?
- LINQ query on sorting the data
- Regex.IsMatch() in dynamic linq
- Select a row only if a field from previous row is less from a field in the active row
- Reassign ItemsSource - Items collection must be empty before using ItemsSource
- How to dereference a string that might be null in LINQ
- How to GroupBy/GroupJoin XElements using LINQ
- how to correct the following linq?
- Linq - what to do when 'Queries with local collections are not supported'
- LINQ: Query to get information from 2 tables
- Create list of tuples with data from nested lists with Linq
- Comparing foreign keys in entity framework 3.5
- How to improve search performance
- Linq query where any item from List<int> belongs to parameter string[]
- Using Linq to query nested dynamic objects
- Why the postman is returning a "Non-static method requires a target."
- How do I perform a case-insensitive compare of GUIDs with LINQ?
- How to merge entity graphs in LLBLGen?
- Help me convert this SQL query to LINQ
- Is there any built-in .NET Framework alternative for my extension method?
- group by in linq doesn't show the expected result