Jun 10, 2009

EF JOIN

Zlatko Michailov, the Entity SQL program manager at Microsoft, writes in his blog: "A well defined query against a well defined entity data model does not need JOIN. Navigation properties in combination with nesting sub-queries should be used instead. These latter constructs represent task requirements much more closely than JOIN does."—http://blogs.msdn.com/esql/ (November 1, 2007). To show it means, here is an example.

In this model the Contact is referenced by multiple Address. Here we explicitly expression their relationship by a reference object. Because we have this navigation properties, we can write the following code using this navigation properties and sub-query like below.

var test = from c in context.Contacts
           select new
           {
               c.FirstName,
               c.LastName,
               StreetsCities = from a in c.Addresses select new { a.Street1, a.City }
           };

If the navigation properties is missing(as shown in the following chart), then you have to use join.

var test = from c in context.Contacts
           join address in context.Addresses on c.ContactID equals address.ContactID
           select new
           {
               c.FirstName,
               c.LastName,
               StreetsCities = new { address.Street1, address.City }
           };