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

Featured

  • Cloud-Focused .NET Aspire 9.1 Released

    Along with .NET 10 Preview 1, Microsoft released.NET Aspire 9.1, the latest update to its opinionated, cloud-ready stack for building resilient, observable, and configurable cloud-native applications with .NET.

  • Microsoft Ships First .NET 10 Preview

    Microsoft shipped .NET 10 Preview 1, introducing a raft of improvements and fixes across performance, libraries, and the developer experience.

  • C# Dev Kit Previews .NET Aspire Orchestration

    Microsoft's dev team has been busy updating the C# Dev Kit, a Visual Studio Code extension that enhances the C# development experience by providing tools for managing, debugging, and editing C# projects.

  • Hands On: New VS Code Insiders Build Creates Web Page from Image in Seconds

    New Vision support with GitHub Copilot in the latest Visual Studio Code Insiders build takes a user-supplied mockup image and creates a web page from it in seconds, handling all the HTML and CSS.

  • Naive Bayes Regression Using C#

    Dr. James McCaffrey from Microsoft Research presents a complete end-to-end demonstration of the naive Bayes regression technique, where the goal is to predict a single numeric value. Compared to other machine learning regression techniques, naive Bayes regression is usually less accurate, but is simple, easy to implement and customize, works on both large and small datasets, is highly interpretable, and doesn't require tuning any hyperparameters.

Subscribe on YouTube

Upcoming Training Events