Querying an Entity Data Model - Entity Framework 6 Recipes - page 86

navigation property is either set to the associated TopSeller entity or to null if no TopSeller exists. If a TopSeller entity
does not exist for a given Product (that is, it has not been rated as a top seller), we simply assign a value of “0” for the
Product Rating.
In some cases, you might not have a relationship (for instance, a navigation property) between the entities that
you want to join. In these cases, you can explicitly join the entities, projecting the results into an anonymous type.
We need to project into an anonymous type because the unrelated entities won't have navigation properties, so we
wouldn't otherwise be able to reference the related entity.
The code in the second query block illustrates this approach. Here we join the entities on the ProductId key and
put the result into a new sequence entitled “g”. Then, from g we apply the DefaultIfEmpty() method to fill in nulls
when g is empty. Sure enough, when the SQL is generated, it includes a left-outer join between the two entities. We
include an orderby clause to order the results by the rating. Finally, we project the results into an anonymous type.
In the third approach, we show you how to do the left-outer join more explicitly using Entity SQL, embedding an
Entity SQL Case statement within the query.
3-11. Ordering by Derived Types
Problem
You are using Table per Hierarchy inheritance, and you want to sort results by the derived type.
Solution
Let's suppose that you have a model like the one shown in Figure 3-12 .
Figure 3-12. A model using Table per Hierarchy inheritance with three derived types