Eliminate Joins by Modifying Entity Framework Models

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

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.

About the Author

Peter Vogel is a system architect and principal in PH&V Information Services. PH&V provides full-stack consulting from UX design through object modeling to database design. Peter tweets about his VSM columns with the hashtag #vogelarticles. His blog posts on user experience design can be found at http://blog.learningtree.com/tag/ui/.

comments powered by Disqus


  • AI for GitHub Collaboration? Maybe Not So Much

    No doubt GitHub Copilot has been a boon for developers, but AI might not be the best tool for collaboration, according to developers weighing in on a recent social media post from the GitHub team.

  • Visual Studio 2022 Getting VS Code 'Command Palette' Equivalent

    As any Visual Studio Code user knows, the editor's command palette is a powerful tool for getting things done quickly, without having to navigate through menus and dialogs. Now, we learn how an equivalent is coming for Microsoft's flagship Visual Studio IDE, invoked by the same familiar Ctrl+Shift+P keyboard shortcut.

  • .NET 9 Preview 3: 'I've Been Waiting 9 Years for This API!'

    Microsoft's third preview of .NET 9 sees a lot of minor tweaks and fixes with no earth-shaking new functionality, but little things can be important to individual developers.

  • Data Anomaly Detection Using a Neural Autoencoder with C#

    Dr. James McCaffrey of Microsoft Research tackles the process of examining a set of source data to find data items that are different in some way from the majority of the source items.

  • What's New for Python, Java in Visual Studio Code

    Microsoft announced March 2024 updates to its Python and Java extensions for Visual Studio Code, the open source-based, cross-platform code editor that has repeatedly been named the No. 1 tool in major development surveys.

Subscribe on YouTube