LINQ Include with a Group By and new Object-CodePudding

Given the below query

var query = from vehicle in _context.Vehicle.Where(x => x.ClientId == clientId && x.IsActive)

        //Left Outer to BookingVehicle. Used for subsequent Left Outers to Bookings
        join bookingVehicle in _context.BookingVehicle
            on vehicle.Id equals bookingVehicle.VehicleId
            into vehicle_bookingVehicle
        from bv in vehicle_bookingVehicle.DefaultIfEmpty()

        //Left Outer to Booking, for specific statuses. To get count of work-in-progress bookings
        join bookingActive in _context.Booking.Where(x => new[] { 1, 3, 4, 6, 7, 11, 13 }.Contains(x.StatusId))
            on bv.BookingId equals bookingActive.Id
            into bookingVehicle_bookingActive
        from ba in bookingVehicle_bookingActive.DefaultIfEmpty()

        //Left Outer to Booking. To get count of all bookings (irrespective of status), and date of latest booking
        join bookingHistory in _context.Booking
            on bv.BookingId equals bookingHistory.Id
            into bookingVehicle_bookingHistory
        from bh in bookingVehicle_bookingHistory.DefaultIfEmpty()

        group new { vehicle, ba, bh }
        by new { 
            Vehicle = vehicle
        }
        into grp

        select new VehicleItemListDTO
        {
            Vehicle = grp.Key.Vehicle,

            HasBookings = (grp.Count(g => g.ba.Id != null) > 0),
            HasHadBookings = (grp.Count(g => g.bh.Id != null) > 0),
            LastBooking = (grp.Max(g => g.bh.ScheduleStartUtc) != null) ? grp.Max(g => g.bh.ScheduleStartUtc) : grp.Max(g => g.bh.ActualStartUtc),
        };


var results = query.OrderBy(x => x.Vehicle.Id).ToList();

return results;

I have a custom object, VehicleItemListDTO, that includes a Domain Object of Vehicle, and some other parameters on how that vehicle is being used.

This is working fine. However, I would like to include additional data about the Vehicle. Some Vehicles have an assigned VehicleModel.

If I use an Include(y => y.VehicleModel) on the initial FROM statement, then the VehicleModel is not included in the data returned to the caller. (With SQL Profiler, you can see the Left Join, but it is in a nested query, and subsequently lost).

I also tried:

var results = query.Include(z => z.Vehicle.VehicleMake).AsEnumerable().OrderBy(x => x.Id).ToList();

But that then errors at runtime with:

The result type of the query is neither an EntityType nor a CollectionType with an entity element type. An Include path can only be specified for a query with one of these result types.

How can I get my Vehicle's VehicleModel Included to the caller?

CodePudding user response：

try to use include before the query

var query = from vehicle in _context.Vehicle.Include(z => z.Vehicle.VehicleMake).AsEnumerable().OrderBy(x => x.Id).Where(x => x.ClientId == clientId && x.IsActive)

...

CodePudding user response：

Try the following query. I have changed grouping because it is not natural for the SQL to group by whole record

var groupingQuery = 
    from vehicle in _context.Vehicle.Where(x => x.ClientId == clientId && x.IsActive)

    //Left Outer to BookingVehicle. Used for subsequent Left Outers to Bookings
    join bookingVehicle in _context.BookingVehicle
        on vehicle.Id equals bookingVehicle.VehicleId
        into vehicle_bookingVehicle
    from bv in vehicle_bookingVehicle.DefaultIfEmpty()

    //Left Outer to Booking, for specific statuses. To get count of work-in-progress bookings
    join bookingActive in _context.Booking.Where(x => new[] { 1, 3, 4, 6, 7, 11, 13 }.Contains(x.StatusId))
        on bv.BookingId equals bookingActive.Id
        into bookingVehicle_bookingActive
    from ba in bookingVehicle_bookingActive.DefaultIfEmpty()

    //Left Outer to Booking. To get count of all bookings (irrespective of status), and date of latest booking
    join bookingHistory in _context.Booking
        on bv.BookingId equals bookingHistory.Id
        into bookingVehicle_bookingHistory
    from bh in bookingVehicle_bookingHistory.DefaultIfEmpty()

    group new { ba, bh } by new { VehicleId = vehicle.Id } into grp

    select new 
    {
        VehicleId = grp.Key.VehicleId,

        HasBookings = (grp.Count(g => g.ba.Id != null) > 0),
        HasHadBookings = (grp.Count(g => g.bh.Id != null) > 0),
        LastBooking = grp.Max(g => g.bh.ScheduleStartUtc) ?? grp.Max(g => g.bh.ActualStartUtc),
    };

var query = 
    from g in groupingQuery
    join vehicle in _context.Vehicle on g.VehicleId equals vehicle.Id
    select new VehicleItemListDTO
    {
        Vehicle = new Vehicle
        {
            Id = vehicle.Id,
            ... // other fields
            VehicleMake = vehicle.VehicleMake
        },

        HasBookings = g.HasBookings,
        HasHadBookings = g.HasHadBookings,
        LastBooking = g.LastBooking,
    };

var results = query.OrderBy(x => x.Vehicle.Id).ToList();

return results;

We have to construct Vehicle object again to make VehicleMake initialized.