Database Reference
In-Depth Information
{
if (history["Salary"] != DBNull.Value)
Console.WriteLine("{0} Salary on {1:d} was {2:c}", history["Name"],
history["SalaryDate"], history["Salary"]);
else
Console.WriteLine("{0} --",history["Name"]);
}
}
The trick here is that we want to “flatten” out hierarchical data, such as an associate with multiple salary inputs.
Following is the output of the code in Listing 3-30:
Using LINQ...
Associate Salary History
Bill Jordan Salary on 10/8/2009 was $33,500.00
Janis Roberts --
Kevin Hodges Salary on 8/4/2009 was $39,500.00
Kevin Hodges Salary on 2/5/2010 was $41,900.00
Using Entity SQL...
Bill Jordan Salary on 10/8/2009 was $33,500.00
Janis Roberts --
Kevin Hodges Salary on 8/4/2009 was $39,500.00
Kevin Hodges Salary on 2/5/2010 was $41,900.00
How It Works
To flatten the query results, we followed the strategy in Recipe 3-10 and used a nested
from
clause and the
DefaultIfEmpty()
method to get a left-outer join between the tables. The
DefaultIfEmpty()
method ensured that
we have rows from the left side (the Associate entities), even if there are no corresponding rows on the right side
(AssociateSalary entities). We project the results into an anonymous type, being careful to capture null values for the
salary and salary date when there are no corresponding AssociateSalary entities.
For the Entity SQL solution, we use the
outer apply
operator to create unique pairings between each Associate
entity and AssociateSalary entity. Both the
cross
and
outer apply
operators are available in SQL Server.
3-15. Grouping by Multiple Properties
Problem
You want to group the results of a query by multiple properties so as to group by multiple columns when the query
executes against the database.
Solution
Let's say that you have a model with an Event entity type like the one shown in Figure
3-16
. Event has a name, city, and
state. You want to group events by state and then by city.
