How does one Left Outer Join on multiple OR columns? C# & Entity Framework

Question

I currently join like the following for a left outer join:

join temp_locations in db.Locations 
         on new { city_id = ((int?)c.city_id), state_id = ((int?)s.state_id), country_id = ((int?)country.country_id) } 
         equals new { temp_locations.city_id, temp_locations.state_id, temp_locations.country_id } into Temp_locations
from l in Temp_locations.DefaultIfEmpty()

But that is creating a left outer join with multiple AND commands. I need those to be OR. SQL example:

Left Outer Join 
    Locations l on l.city_id = c.city_id 
                or l.state_id = s.state_id 
                or l.country_id = country.country_id

How can I accomplish this?

Edit 7/11/18

I was not able to get this to work. The cross join answer did not work as records were appearing and disappearing that were not supposed to. I ended up making a stored procedure and doing it all in there. I'm still open to how to do this in entity framework if anyone knows.

@MitchWheat For us, a location is being able to associate a world with a bunch of other entities. If we wanted "East US" to mean NJ and NY, we would make locations have two state ids and not individual cities. Therefore locations are not just cities. — SolidSnake4444, Jun 21 '18 at 01:10
From my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) point 5, you can only do equijoins with and so you must move the conditions into `where` caluses. Since you are doing a left join, put the `where` before the `DefaultIfEmpty`. Since no conditions are left outside the `or`, use cross join. — NetMage, Jun 21 '18 at 19:16
@NetMage I'm confused at what you mean by use cross join. I tried using a from l in db.Locations.Where(w => w.city_id == c.city_id || w.state_id = s.state_id...).DefaultIfEmpty and this did not get the correct results. The results that should have come back when location was null did not appear. — SolidSnake4444, Jun 21 '18 at 19:26

score 0 · Answer 1 · answered Jun 21 '18 at 22:40

A cross join is done with two from clauses. In your case, you don't have any equijoin conditions so you just need a Where on the db.Locations to handle the left outer join:

from temp_locations in db.Locations.Where(tl =>
         c.city_id == tl.city_id ||
         s.state_id == tl.state_id ||
         country.country_id == tl.country_id)
    .DefaultIfEmpty()

How does one Left Outer Join on multiple OR columns? C# & Entity Framework

1 Answers1