Блог

Я хочу создать набор данных для отображения древовидного представления со всем автопарком, который есть у одного пользователя, и всеми автомобилями каждого автопарка

Итак, дерево должно выглядеть следующим образом

 |
| - fleetName  -- fleet_id as node key
|  |
|  |--- plate_number  -- gps_device_id as node key
| - plate_number   -- for cars wont belong to any fleet      
  

customer_cars

 CREATE TABLE tracker.customer_cars
(
  customer_car_id integer NOT NULL DEFAULT nextval('customer_cars_car_id_seq'::regclass),
  gps_device_id integer,
  plate_number  string
  user_id text NOT NULL,
  fleet_id integer,

  CONSTRAINT customer_cars_idx PRIMARY KEY (customer_car_id),
  CONSTRAINT cars_gps_device_fk FOREIGN KEY (gps_device_id)
      REFERENCES tracker.gps_devices (gps_device_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fleet_fk FOREIGN KEY (fleet_id)
      REFERENCES tracker.fleet_cars (fleet_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
  

fleet_cars

 CREATE TABLE tracker.fleet_cars
(
  fleet_id serial NOT NULL,
  fleet_name text,
  user_id text NOT NULL,
  CONSTRAINT fleet_idx PRIMARY KEY (fleet_id)
)
  

Что я пытаюсь

     var customer_cars = db.customer_cars
              .Include(c => c.gps_devices)
              .Include(c => c.fleet_cars)
              .Where(c => c.user_id.CompareTo(UserId) == 0)  
              .GroupBy(c => c.fleet_id,
                       c => c.fleet_cars,
                       (key, g) => new
                       {
                           FleetID = key,                                   
                           Cars = g.ToList()
                       }
              );
  

Но его нет fleet_name , только fleet_id , а в машинах есть customer_cars строки без gps_device_id

 foreach (var fleet in customer_cars)
{
    -- have id but no name
    string fleet_id = fleet.FleetID.HasValue ? fleet.FleetID.ToString() : "0"; 

    foreach (var car in fleet.Cars ){
        string fleet_name = car.fleet_name; -- have fleet_name
        string gps_id = car.gps_device_id ; -- dont have gps_device_id             
    }
}
  

Немного изменив свою группу по ключу, вы получите то, что хотите:

 var customer_cars = db.customer_cars
                      .Include(c => c.gps_devices)
                      .Include(c => c.fleet_cars)
                      .Where(c => c.user_id.CompareTo(UserId) == 0)  
                      .GroupBy(c => new { c.fleet_id, c.fleet_cars})
                      .Select(gr => new
                      {
                          FleetID = gr.Key.fleet_id,     
                          FleetName = gr.Key.fleet_cars.fleet_name,                                  
                          Cars = gr.ToList()
                      });