Eliminate Joins by Modifying Entity Framework Models

It makes sense to add associations to Entity Framework if it helps you avoid using Joins.

• By Peter Vogel
• 10/06/2011

In a recent article I showed that using Entity Framework navigation properties makes your code considerably simpler than using the more "SQL-like" Joins (though you certainly can do Joins in LINQ). I also suggested that it makes sense to add associations to Entity Framework if it helps you avoid using Joins.

For instance, in the version of the Northwind database I'm using, there's a Region table with RegionId and RegionDescription columns. The Customer table has a corresponding Region column. What the database doesn't have is a foreign key/primary key relationship between the Region and Customer tables. As a result, the default EF model doesn't have navigation properties connecting Regions to Customers or Customers to Regions. If you want Customer information along with the RegionDescription you'll have to use a Join. Fortunately, you can add the association to the model and eliminate Joins.

To create a new association in the EF model, just open the model's edmx file; in the Toolbox, select the Association tool. Then click on one of the entities you want to navigate between (e.g. the Region entity) and drag to the other entity (e.g. the Customer entity). That will add a new navigation property (in this case, called Customers) to the Regions Entity and a new navigation property to the Customers Entity (called Region_1—a name worth changing).

Now switch to the Model Browser (typically nested with Solution Explorer) and, under the Associations node, find your new Association (my example has resulted in an Association called RegionCustomer). In the Properties window, select the Association's Referential Constraint property and click its builder button (the one with the three dots). This will bring up a dialog where you can specify which is the Principal entity in the Assocation (the entity on the "one" side of the relationship—Region, in this case) and what properties are to be used in the relationship (in this case, RegionId from the Region table and Region from the Customer table). That should complete the Association and cause Entity Framework to generate the appropriate Navigation properties.

With the new Association added, you can find all the Customers based on the RegionDescription in the Region entity:

 Dim res = From  r In nw.Regions

  Where  r.RegionDescription = "Spain"

  Select  r.Customers

Or, going the other way, retrieve a RegionDescription for a Customer's Region:

 Dim res = From c In  nw.Customers

  Where  c.CustomerID = "ALFKI"

  Select  c.Region_1.RegionDescription

If the columns involved contain Nulls you'll probably need to adjust the default settings for the End1 Multiplicity and End2 Multiplicity properties on the Association. If these default to 1…* then your code may blow up when Entity Framework hits the null fields. Changing the multiplicity to 0…* (and setting the column's nullable property to True) will give Entity Framework permission to deal with those null values.

Reader Comments: