5

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();
Joe
  • 11,147
  • 7
  • 49
  • 60

1 Answers1

6

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()
Jon Skeet
  • 1,421,763
  • 867
  • 9,128
  • 9,194
  • The where is ok... until I have to start doing `Vehicles.Select(x=>x.Car.Producer.AnotherTable.Etc.Field)`. I just thought wouldn't it be cool if there was a relationship reference that does not return all the nulls of the outer table. The goal is to minimalise the query string. (given the length and the wordyness of the join, i'd stick with the where if there is not a more elegant way of expressing JOIN) – Joe Apr 04 '11 at 06:33
  • @Joe: The problem is that your field is nullable. This wouldn't be an issue for non-nullable fields. – Jon Skeet Apr 04 '11 at 06:34
  • @Jon, that's interesting. i don't understand that fully. When i mouse over the return types, the select returns a IQueryable. normally if it's nullable, it would return IQueryable. However do you mean that because a vehicle is either a car or a bike that there is not always a relationship between the vehicle and the car table? – Joe Apr 04 '11 at 06:37
  • @Joe: I mean that the `Car` property is nullable... because there isn't always a `Car` associated with the vehicle. So in your table, presumably `CarID` is a nullable field. – Jon Skeet Apr 04 '11 at 06:38
  • @Jon, it doesn't actually come up in linqpad as being a nullable type. IQueryable. hum... actually i have forgotten one important detail in making this simplied example. – Joe Apr 04 '11 at 06:46
  • @Joe: Giving a *complete* example, with all the details of the types involved and which fields are nullable, would really help. – Jon Skeet Apr 04 '11 at 06:48
  • @Jon, sorry, should be more complete now. I've updated the first sentence in the question. None of the keys queried are nullable. – Joe Apr 04 '11 at 06:49
  • @Joe: Okay, so the key relationship is in the opposite direction to how I'd anticipated - but fundamentally for any Vehicle entry, there isn't necessary a Car associated with it. – Jon Skeet Apr 04 '11 at 06:51
  • @Jon, the mapping between Car-Vehicle and Bike-Vehicle are both 1 to 1 but neither of them are complete mappings. Not sure what that's called but Cars + Bikes to vehicles represents a complete 1-1 mapping. However, none of the fields can be null as they are FKs hence they dont come up as nullable. – Joe Apr 04 '11 at 06:52
  • @Joe: Not sure what you mean by "neither of them are complete mappings". Can you give more information? If it were really 1:1, I'd suggest using `Cars` instead of `Vehicles.Car` to start with. Is every `Car` associated with a `Vehicle`? – Jon Skeet Apr 04 '11 at 06:53
  • @Jon, well a vehicle is either a Car or a Bike. So VehicleID 1 is a car and VehicleID 2 is a bike. A vehicle will always be one or the other. – Joe Apr 04 '11 at 06:55
  • @Joe: Yes, but will any entry in the `Cars` table always have an associated `Vehicle`? If so, why do you want to do the join at all? Why not just use the `Cars` table to start with? Use `Cars.Count()` instead of `Vehicles.Select(v => v.Car).Count()`. You could *try* using `OfType()` instead of the `Where` clause, but I'm not sure whether it'll work. – Jon Skeet Apr 04 '11 at 06:57
  • @Jon, because Vehicles is the result of another query where i select a subset of vehicles based on other requriements. So for example `ParkingLot.Where(x=>x.Day == DateTime.Today).Select(x=>x.Vehicle.Car.CountryID).Distinct()` – Joe Apr 04 '11 at 07:00
  • @Joe: Okay, so in that case I think you basically *do* have to use the Where clause, or an explicit join. – Jon Skeet Apr 04 '11 at 07:02
  • @Jon, so that leads me back to my question. At the core I don't understand why doing x.Car gives me a INNER JOIN and not just a JOIN. It would be alot easier to just have a join. and it would make more sense too... to me anyway. – Joe Apr 04 '11 at 07:09
  • @Joe: Do you mean a LEFT JOIN rather than a JOIN? Logically, using x.Car represents a LEFT JOIN rather than an INNER JOIN because it could be null, where the vehicle isn't a car. You've got a property which can reasonably be null - LINQ isn't going to remove that possibility for you automatically. (Sometimes you might *want* the null values.) – Jon Skeet Apr 04 '11 at 07:15
  • @Jon, (yes i meant to say LEFT JOIN) ah.. i see, so if x.Car returned an INNER JOIN which is what i'd expect it to do, then there might be null pointer exceptions being thrown from doing `x.Car.SomeOtherRelationship.Field` and so it makes more sense to give a LEFT JOIN result... hum... i guess that's just the way it works. – Joe Apr 04 '11 at 07:52
  • @Joe: No, that wouldn't give a NullReferenceException, but it logically *should* because there are rows which *logically* have a null `Car` property. – Jon Skeet Apr 04 '11 at 08:18