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 Query
- 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?
- When should I use .Count() and .Count in the context of an IEnumerable<T>
- Should we always use .Find() rather than .FirstOrDefault() when we have the primary key in Entity Framework Core?
- How to use expressions to build a LINQ query dynamically when using an interface to get the column name?
- is it possible to use Take in LINQ when the query returns an anonymous type?
- Why doesn't Visual Studio 2010 allow the use of "is null" in a linq query when VS2017 does?
- Does Entity Framework query the database multiple times if I use different fields of the same Linq query at different times?
- Linq to Sql NotSupportedException "The explicit construction of the entity in a query is invalid" when selecting new Type of other Database Table
- How can I use a predicate in a crm sdk 2011 linq query when the query contains a join?
- Can I Use The Same Linq Code to Query for Different Conditions?
- How can I build a LINQ predicate/dynamic.LINQ query based off grid filtering when the grid properties don't have the entity properties?
- When I use an IQueryable function, it significantly increase the time of the query
- How can I query a database with LINQ when the column is a number but I have a string in the WHERE?
- why can I only access the properties of an anonymous type when I add .toList to the end of my linq query
- How to assign LINQ Query to a variable and then use it later in the code
- How do I use a linq query to update the underlying database table
- How to create a Dynamic Query when there's a condition that won't use all fields in the where section with LinQ
- Alternative to use a method in a query to the database in c# using entity framework
- What should be the return type when writing Linq query for selecting specific columns?
- The specified type member 'Date' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties
- What is the biggest mistake people make when starting to use LINQ?
- Should I use Single() or SingleOrDefault() if there is a chance that the element won't be found?
- When should I use LINQ for C#?
- Could not find an implementation of the query pattern for source type 'System.Data.Entity.DbSet'
- Should the order of LINQ query clauses affect Entity Framework performance?
- Can LINQ use binary search when the collection is ordered?
- How can I use Nhibernate to retrieve data when the "WHERE IN()" have thousands of values? (too many parameters in the sql)
- EF Query using .Contains() and .ToLowerInvariant() Results in no matches when match is found at the end of a string
- Linq error generic parameter or the query must use a nullable type
More Query from same tag
- Update Linq Query For Image (File Upload)
- How to get .Top(5) when using non-generic EF db.Set(type)
- Change value of each element by linq
- How do I get Distinct() to work with a collection of custom objects
- using LINQ to extract sections out of a string array into a new collection
- How can I extend a field of a class?
- Error while converting datatable to array[] using linq
- linq to List problem in C#
- Querying MongoDB with List of IDs using Lambda Expression
- Query language for Entity Framework
- C#/LINQ: Concatenating strings
- Random Name Picker
- Numbering comments in ASP.NET and SQL Server
- Use LINQ select as method parameter in C#
- EF SQL changed when passing predicate as Parameter to Where Clause
- Querying a specific line from a txt file in C#
- LINQ: how to get an intersection of two sets of ints?
- Linq distinct method only for a specific property
- c# How to get count of value in a List based on value in another List using LINQ
- one to zero or one relationship in entity framework
- Is there a way I can get the count of all entries of data created by their day of week for each week
- SelectMany cannot be inferred from the usage
- LINQ to Entities does not recognize the method 'System.DateTime ToDateTime(System.String)'
- Not Parsing XML Data Correctly with Linq to XML
- What is the purpose of the EnumerableRowCollection<TRow> class?
- LINQ to Entities (Entity Framework) Join and .Include conflict
- Read IEnumerable or wait for it to finish changing first
- How can i put this custom Linq query to view?
- Joining 2 lists with condition
- Unsure on return type of linq to sql query