Practical .NET

Leverage Joins in Entity Framework To Get Just the Data You Want

Every once in a while you'll need to use the LINQ Join to get the data you want.

By and large, I tell developers they won't use the LINQ Join clause because you always can just follow the navigation properties that link one entity to another. But that's not always true.

Let's say, for example, that you want to retrieve all of the SalesOrderDetails for SalesOrders that have a delivery date in the future. You could write this query to follow the SalesOrderDetail navigation property to get to the SalesOrderDetails:

Dim sods = From so In db.SalesOrders.Include(Function(so) so.SalesOrderDetails)
           Where so.DueDate > DateTime.Now
           Select so.SalesOrderDetails

Under the hood, Entity Framework will use a SQL Join to tie together the SalesOrder and SalesOrderDetail tables, but you don't have to use a LINQ Join in your query.

This will work fine ... but let's make the query more complicated: You don't want all of the SalesOrderDetails -- you only want SalesOrderDetails that have their IsDiscounted property is set to True. Unfortunately, you can't do a "conditional include" to retrieve just those SalesOrderDetails. If you stick with the query I just showed then you could, potentially, retrieve a lot of SalesOrderDetails that you don't want.

In this case, a LINQ Join is probably the best way to get just the data you want:

Dim sods = From so In db.SalesOrders
           Join sod In db.SalesOrderDetails
             On so.SalesOrderId = sod.SalesOrderID
           Where so.DueDate > DateTime.Now And
                 sod.IsDiscounted = True
           Select sod

If you're familiar with SQL this syntax will look very familiar, the one "addition" being the In keyword in the Join clause that establishes the variable name for referring to the SalesOrderDetails.

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

  • What's New in Visual Studio 2019 v16.5 Preview 2

    The second preview of Visual Studio 2019 v16.5 has arrived with improvements across the flagship IDE, including the core experience and different development areas such as C++, Python, web, mobile and so on.

  • C# Shows Strong in Tech Skills Reports

    Microsoft's C# programming language continues to show strong in tech industry skills reports, with the most recent examples coming from a skills testing company and a training company.

  • Color Shards

    Sharing Data and Splitting Components in Blazor

    ASP.NET Core Version 3.1 has at least two major changes that you'll want to take advantage of. Well, Peter thinks you will. Depending on your background, your response to one of them may be a resounding “meh.”

  • Architecture Small Graphic

    Microsoft Ships Preview SDK, Guidance for New Dual-Screen Mobile Era

    Microsoft announced a new SDK and developer guidance for dealing with the new dual-screen mobile era, ushered in by the advent of ultra-portable devices such as the Surface Duo.

  • How to Create a Machine Learning Decision Tree Classifier Using C#

    After earlier explaining how to compute disorder and split data in his exploration of machine learning decision tree classifiers, resident data scientist Dr. James McCaffrey of Microsoft Research now shows how to use the splitting and disorder code to create a working decision tree classifier.

.NET Insight

Sign up for our newsletter.

Terms and Privacy Policy consent

I agree to this site's Privacy Policy.

Upcoming Events