Practical .NET

The Best Option for Selecting Related LINQ Objects

Here's the best performing option when you're retrieving the objects at the end of an entity class's navigation property, either when you only want some of the objects or when you only want them some of the time.

This is the second (or third?) option I've found for retrieving related Entity Framework objects in a LINQ query, either when you only want some of the related objects or when you only want them some of the time. The problem I'm addressing is when, for example, you want to retrieve only some of the SalesOrders for a Customer or want to retrieve SalesOrders only for some Customers.

In these scenarios, for the best possible performance, you want to reduce the data transferred by retrieving only the SalesOrders you need. And, for best performance, you also want to retrieve all the rows (both Customers and SalesOrders) in a single trip to the database. I also assume you want to achieve these goals with readable code.

The simplest, most effective way to do that is to incorporate the LINQ queries that retrieve the related rows into your Select statement.

For example, assume that you want to retrieve Customers objects with their related SalesOrders -- but only those SalesOrders with a ShipDate in the future. To achieve that, in the Select clause, you build an anonymous object with two properties: one to hold the Customer object and one to hold all of the Customer's future SalesOrders. To implement this solution all you have to do is load the SalesOrders property from a LINQ statement in the Select clause, like this:

From c In db.Customers
Select New With {.Customer = c,
                 .SalesOrders = (From so In c.SalesOrders
                                 Where so.ShipDate > DateTime.Now
                                 Select so)}

It gets better: You're not restricted to using properties tied to the object at the end of the navigation property. This query retrieves all the Customers but only retrieves SalesOrders for the Customers whose Valid property is set to True:

From c In c.Customer
Select New With {.Customer = c,
                 .SalesOrders = (From so In c.SalesOrders
                                 Where c.Valid
                                 Select so)}

The good news is that the SQL statement that's issued in both cases gives you the best possible performance, at least with SQL Server: The SQL statement joins the Customer and SalesOrder tables together while integrating the Where clauses to retrieve all (and only) the rows you want. And does it in one trip to the database.

I think it's pretty readable, too.

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

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