Juan Carlos Oropeza Juan Carlos Oropeza - 1 month ago 9
C# Question

How include another field on the Group BY

I want to create a data set to display a tree view with all the fleet one user has and all the cars of each fleet

So the tree should looks like this

|
| - 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)
)


What I try

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()
}
);


But dont have
fleet_name
, only
fleet_id
and the cars have the
customer_cars
rows without
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
}
}

Answer

Changing a bit your group by key, you will get that you want:

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()
                      });