How do you selectively join in tables with linq?

Question

Let's say you have these two tables:

CARS
ID   CAR_MODEL 
11   Mustang          
22   Camaro           
33   F-150     

PARTS
ID   CAR_ID  PART_NAME       
1    11      Steering Wheel  
2    22      Steering Wheel 
3    22      Headlights

You need to create a search form. How would you you make this work:

var query = db.CARS.include(u => u.PARTS);

if(model.CarPartName != "")
   query = query.where(u => u.PARTS.PART_NAME == model.CarPartName); //this doesn't work

return query.ToList();

There is currently a foreign key relationship between PARTS.CAR_ID and CARS.ID but for some reason when I attempt u.PARTS. I can't append on a column name.

Any idea how to make this optional join work?

Answer 1

That wouldn't work that way, because a car can have many parts, thus u.PARTS is returning a collection. There are many solutions to this problem, you can start by Cars collection (as you did), or you can start by PARTS collection. If you started with PARTS collection, it would look like:

var query = db.PARTS.Include(p => p.Car);

if(model.CarPartName != "")
   query = query.Where(u => u.PART_NAME == model.CarPartName); 

return query.Select(p => p.Car).Distinct().ToList();

With Cars:

var query = db.CARS.include(u => u.PARTS);

if(model.CarPartName != "")
   query = query.Where(u => u.PARTS.Any( up => up.PART_NAME == model.CarPartName)); 

return query.ToList();

Note: I added the first one, just because I wanted to show what is going on.