How To Do A Multi-Column Join In LINQ

This is an exclusive guest post from Shaun Tirubeni, please visit his blog located here for more of his work.

Since we live in a complex world, creating even a simple application takes a fair amount of effort. We do multi-column joins every day using SQL. Many companies are moving away from raw SQL and stored procedures to ORMs and other layers of abstraction.

One of the great features of the .Net Framework is LINQ. LINQ allows native data querying in a .Net language. Below is an example equivalent of a multi-join in C# using LINQ.

Assume you have 2 objects: Person and Address.

Person has the following fields: PersonId, Name, AddressId, IsActive

Address has the following fields: AddressId, StreetNumber, Line1, Line2, Line3, ZipCode.

So, as we can see, we would be able to easily join the two objects on the AddressId. However, what if we wanted to also include the ‘IsActive’ field in the join and not in the where clause to improve performance? Do a multi-column join:

Assumption: we have a DbContext derived class that we are using to access our database called context

var result = from p in context.Person
			 join a in context.Address on
				new { AddressId = p.AddressId, IsActive = p.IsActive } equals
				new { AddressId = a.AddressId, IsActive = true }
		     select new
		     {
			   p.Name,
			   a.StreetNumber,
			   a.Line1,
			   a.Line2,
			   a.Line3,
			   a.ZipCode
		     };

It’s pretty much self-explanatory and easy to understand. Join the two objects by creating two anonymous typed objects with the same fields and assign the values accordingly.

This is something we would use in everyday development but when I was first starting out with LINQ & the Entity Framework, it was something I had to search and figure out for myself.