linq to sql: specifying JOIN instead of LEFT OUTER JOIN

Question

Given that I have three tables, Vehicles, Cars, Bikes. Both Cars and Bikes have a VehicleID FK that links back to Vehicles.

I want to count all vehicles that are cars like this.

Vehicles.Select(x=>x.Car).Count();

However, this will give me ALL the rows of vehicles and put null in the rows where the vehicle type is Bikes.
I'm using linqpad to do this and seeing the sql statment I realised the reason why it does this is because on the x.Car join it performs a LEFT OUTER JOIN between vehicle and car which means it will return all vehicles. If I change the query to just use JOIN, then it works as expected.

Is there a way to tell linq to do a join using this type of syntax? Ultimately I want to do something like:

Vehicles.Select(x=>x.Car.CountryID).Distinct().Dump();

But because of this error:

InvalidOperationException: The null value cannot be assigned to a member with type System.Int32 which is a non-nullable value type.

I end up doing this:

Vehicles.Where(x=>x.Car!=null).Select(x=>x.Car.CountryID).Distinct().Dump();

Answer 1

Well, the Where clause seems reasonable, or you can use an actual join, assuming that you've got a CarID property or something similar:

Vehicles.Join(Cars, v => v.CarID, c => c.ID, (v, c) => c.CountryID).Distinct()