LINQ Joins on multiple columns in Entity Framework

9/9/2024

I came across a very annoying "issue" with LINQ joins (left joins) in Entity Framework where the compiler drove me nuts!

The original query

Basically I had a base query like this:

var query = dbContext.Entity.Where(...);

var result = from e in query
             join j in dbContext.JoinEntity on new { e.Id, e.OtherId } equals new { j.WId, j.OtherId } into group
             from g in group.DefaultIfEmpty()
             select new { e, g };

This query leads to a nasty compiler error:

The type arguments cannot be inferred from the query. Candidates are: ... (very long text here)

The solution

The problem is that I used multiple columns as join and ALL of them have to have the same name, otherwise the compiler will not be able to infer the type arguments (???). So the easy fix is something along the lines of:

var result = from e in query
             join j in dbContext.JoinEntity on new { WId = e.Id, OtherId= e.OtherId } equals new { WId = j.WId, OtherId = j.OtherId } into group
             from g in group.DefaultIfEmpty()
             select new { e, g };

So both columns have to have the same name in the anonymous types. And yes, I could have just write plain SQL (which I also do), but that is besides the point.

Are my EF LINQ to SQL queries safe?

Have you ever asked yourself if your EF LINQ queries are susceptible to SQL injection attacks? Either because you are querying some user data from a text field or directly taking whatever your API hits against the database?

Entity Framework - Storing complex objects as JSON

From time to time, it is nice to store complex objects or lists as JSON in the database. With Entity Framework 8, this is now easily possible. But this was possible all along with Entity Framework 7.

Entity Framework 8: Raw SQL queries on unmapped types

The next iteration of Entity Framework, namely Entity Framework 8, will have a new and exciting feature:

Support raw SQL queries without defining an entity type for the result

That means less boilerplate code!

Table of Contents

The original query

The solution

Want to read more? Check out these related blog posts!

Are my EF LINQ to SQL queries safe?

Entity Framework - Storing complex objects as JSON

Entity Framework 8: Raw SQL queries on unmapped types