Database Reference
In-Depth Information
foreach(var employee in employees)
{
Console.WriteLine("{0}, years worked: {1}",employee.Name,
employee.YearsWorked);
}
}
using (var context = new EFRecipesEntities())
{
Console.WriteLine("Employees (using ESQL w/named constructor)");
string esql = @"select value Recipe3_6.Employee(e.EmployeeId,
e.Name,
case when e.YearsWorked is null then 0
else e.YearsWorked end)
from Employees as e";
var employees = context.Database.SqlQuery<Employee>(esql);
foreach(var employee in employees)
{
Console.WriteLine("{0}, years worked: {1}",employee.Name,
employee.YearsWorked.ToString());
}
}
Following is the output of the code in Listing 3-13:
Employees (using LINQ)
Robin Rosen, years worked: 3
John Hancock, years worked: 0
Employees (using ESQL w/named constructor)
Robin Rosen, years worked: 3
John Hancock, years worked: 0
How It Works
Here, our approach is to use either LINQ or eSQL to project the results into a collection of an anonymous type. The
query sets the YearsWorked to 0 when the underlying value is null.
For the LINQ approach, we use the C# null-coalescing operator
??
to assign the value of 0 when the underlying
value is null. We project the results into a collection of an anonymous type.
For Entity SQL, we use a case statement to assign the value of 0 to YearsWorked when the underlying value
is null. Here, we demonstrate how to use Entity SQL to materialize instances of the Employee entity type without
setting the Default Value property for the entity. To do this, we use the named constructor for the entity type. This
constructor assigns the values from the parameters to the properties in the same order as the properties are defined in
the entity. In our case, the properties for the Employee entity are defined in the following order: EmployeeId, Name,
and YearsWorked. The parameters to the constructor, as do our arguments in the eSQL query, follow this same order.
Unfortunately, there is no corresponding name constructor syntax for LINQ to Entities.
