Conditional Select() in Select()

Question

I have a simple table 'Cars':

IdCar      |   Name     |     Id_Base
 1             Toyota          null
 2             Honda           5
 3             Ford            4
 4             Buick           null 
 5             Volvo           1

Id_Base is a reference to IdCar field of this table Cars. So this is like a circular reference. I know it is weird, but I cannot do anything.

I have a CarDTO:

public class CarDTO
{       
    public int IdCar { get; set; }
    public int? Id_Base { get; set; }
    public string Name { get; set; }
    public CarDto InnerCar {get; set; }
}       

My goal is to project each element of table Cars to CarDTO if Id_Base is NOT NULL. That is I get a Car by its IdCar and fill InnerCar property of CarDTO.

So the wishful result should look like this:

new Car(){ IdCar = 1, Name = "Toyota", Id_Base = null},

new Car(){ IdCar = 2, Name = "Honda", Id_Base = 5, 
    InnerCar = new Car(){ IdCar = 5, Name="Volvo" }},

new Car(){ IdCar = 3, Name = "Ford", Id_Base = 4, 
    InnerCar = new Car(){ IdCar = 4, Name="Buick" }},

new Car(){ IdCar = 4, Name = "Buick", Id_Base = null },

new Car(){ IdCar = 5, Name = "Volvo", Id_Base = 1, 
    InnerCar = new Car(){ IdCar = 5, Name="Toyota" }}

What I've tried:

var cars = from car in db.Cars
     let innerCar =
         (from inCar in db.Cars
          where inCar.id_Base != null && inCar.IdCar == car.IdCar
          select new CarDTO
          {                                       
               Name = inCar.Name,
               IdCar = inCar.IdCar,                                       
               IdBase = inCar.id_Base,
               InnerCar = null
           }
           ).FirstOrDefault(x => x.IdBase == car.id_Base)
       select new CarDTO
           {                                  
               Name = car.Name,
               IdCar = car.IdCar,                                  
               IdBase = car.id_Base,
               InnerCar = innerCar
           };

But InnerCar is always null. I cannot understand what I'm doing wrong. Does anybody know how to project each element and create InnerCar?

Answer 1

That line inCar.IdCar == car.IdCar seems wrong.

It probably should be inCar.IdBase == car.IdCar.

Your line would only get those with the same IdCar.

Or you wanted to write:

inCar.IdCar != car.IdCar - unequals instead of equals

Personally I believe this is easier to read:

var cars = (from car in db.Cars
          select new CarDTO
          {                                       
               Name = car.Name,
               IdCar = car.IdCar,                                       
               IdBase = car.id_Base,
           }).ToList(); //call .ToList() to fix possible EF errors by materializing all objects

cars.ForEach(x => x.InnerCar = cars.FirstOrDefault(y => y.IdCar == x.IdBase));

Answer 2

Does anybody know how to project each element and create InnerCar?

I don't. But I still think I can help you.

First of all please check out what a mapper library is if you don't know what it is. 'Automapper' for example. It can hugely simplify your life. If you prefer not to use it you can set inner cars by simply using a dictionary. It would cost you O(n) and it is a simple code, which is a plus

      class Program
      {
        static void Main(string[] args)
        {
          var allCars = GetAllCars();
          var allCarsDtoDict = allCars.Select(a =>
          new CarDTO() { Id_Base = a.Id_Base, IdCar = a.IdCar, Name = a.Name }).ToDictionary(a => a.IdCar, a => a);
          foreach (var car in allCarsDtoDict.Values)
          {
            if (car.Id_Base.HasValue)
              car.InnerCar = allCarsDtoDict[car.Id_Base.Value];
          }

          var allCardWithSetInnerCard = allCarsDtoDict.Values;
        }

        private static IEnumerable<CarDO> GetAllCars()
        {
          return new List<CarDO>()
          {
            new CarDO(){ IdCar = 1, Name = "Toyota", Id_Base = null},
            new CarDO(){ IdCar = 2,Name = "Honda",Id_Base = 5,},
            new CarDO(){ IdCar = 3,Name = "Ford",Id_Base = 4},
            new CarDO(){ IdCar = 4, Name = "Buick", Id_Base = null },
            new CarDO(){IdCar = 5,Name = "Volvo",Id_Base = 1,}
          };
        }
      }