Database Reference
In-Depth Information
How It Works
The key to grouping the registrations by the date portion of the RegistrationDate property is to use the
Truncate()
function. This built-in Entity Framework function, contained in the
DbFunctions
class, extracts just the date portion
of the DateTime value. The built-in
DbFunctions
contain a wide array of formatting, aggregation, string manipulation,
date-time, and mathematical services, and they are found in the
System.Data.Entity
namespace. The legacy
class,
EntityFunctions
, used prior to Entity Framework 6, will still work with Entity Framework 6, but will give you
a compiler warning suggesting you move to the
DbFunctions
class. We'll have a lot more to say about functions in
Chapter 11.
3-14. Flattening Query Results
Problem
You have two entity types in a one-to-many association, and you want, in one query, to obtain a flattened projection of
all of the entities in the association. By flattened, we are referring to denormalizing, or compressing, an object graph
with parent/child relationships into a result represented by a single class.
Solution
Let's say that you have a couple of entity types in a one-to-many association. Perhaps your model looks something like
the one shown in Figure
3-15
.
Figure 3-15.
A model with an Associate entity type representing an associate, and an AssociateSalary entity type
representing the salary history for the associate
You want to get all of the associates and all of their salary history in one query. There may be some new hires that
are in the system, but they don't yet have a salary set. You want your query results to include these associates as well.
To query the model and get the results you want, follow the pattern in Listing 3-30.
Listing 3-30.
Flattening Out the Results Using Both LINQ and Entity SQL
using (var context = new EFRecipesEntities())
{
// delete previous test data
context.Database.ExecuteSqlCommand("delete from chapter3.associatesalary");
context.Database.ExecuteSqlCommand("delete from chapter3.associate");
// add new test data
