LINQ Obscura: outer join a base class table to a subclass table

By May 8, 2014 Uncategorized No Comments

I’ve gone on at length before about inheritance in EF, specifically Table Per Type inheritance, and for the sake of this post, I’m going to assume you already know how to perform an outer join with LINQ. But what if you want to perform an outer join of a base class table to one (or more) subclass tables? I couldn’t think of a way to get it done, but a colleague presented a solution that seems blindingly obvious once demonstrated. In the following incantation, assume that Student is modeled as a subclass of Person:

from p in Persons
join s in Persons.OfType<Student>() on p.ID equals s.ID into ss
from s in ss.DefaultIfEmpty()
select new { p.Name, p.BirthDate, s.GraduationDate }

The key is simply to realize that Persons represents the base table in the DB and Persons.OfType<Student>() represents the subclass table, and the rest falls into place.

Did you like this post? Then you would love FalafelCon! Sign up and let us know you’re coming on Twitter!

The following two tabs change content below.
Adam Anderson is a Microsoft Certified Solution Developer with over 19 years of experience. He started as a consultant using Delphi to rapidly deliver custom, high-quality business solutions in a wide variety of industries. His later experiences afforded him the opportunity to become familiar with a number of different products, technologies, and disciplines, including SQL Server and T-SQL, Oracle and PL/SQL, Crystal Reports, SQL Reporting Services, Internet Information Services, .NET Framework, LINQ, Entity Framework, CSS, HTML, Javascript, Kendo UI, ASP.NET MVC & Web API, ServiceStack, Command batch files, PowerShell, normalized relational database design, dynamic databases, and good object-oriented design based on design patterns and refactoring.