LINQ Get full list of objects, include only the properties that match certain condition

.net .net-core entity-framework entity-framework-core linq

Question

I have these two models:

    public class Seat
    {
        public int Id { get; set; }
        public ICollection<Reservation> Reservations { get; set; }
    //..
    }

    public class Reservation
    {
        public int Id { get; set; }
        public int SeatId { get; set; }
        public Seat Seat { get; set; }
        public DateTime DateTime { get; set; }
        //..
    }

Say a seat has a reservations for every day of the week. I want to make a query that takes the seats and filters the reservations for the seats for a requested day. I want to display the seats and show which ones are free/reserved for the day X. The ways I tried to achieve it:

        var seats = _dbContext.Seats
             .Include(s => s.Reservations.Where(r => r.DateTime.Date == dateParam.Date));
        // InvalidOperationException: 'Lambda expression used 
        // inside Include is not valid.'

Sample

       var seats = _dbContext.Seats
             .Include(s => s.Reservations)
             .Where(r => r.Reservations.Any(r => r.DateTime.Date == dateParam.Date));   
        // Gets the seats which meet the condition. I want these, but also I want the free seats

Sample

        var seatsFiltered = _dbContext.Seats
             .Include(t => t.Reservations)
             .Where(r => r.Reservations.Any(r => r.DateTime.Date == dateParam.Date))
             .ToList();

        var seats = _dbContext.Seats
             .ToList()
             .Select(x => { x.Reservations = new List<Reservation>(); return x; })
             .Concat(seatsFiltered);
        // Not optimal since I obviously ToList twice
        // In the end I get an empty list

        return (ICollection<Seat>)seats;

EDIT: Add sample data as requested:

Seats

Id|AreaId|
--|------|
 1|3     | 
 2|3     |
 3|3     |
 4|3     |
 5|3     |
 6|3     |
 7|3     |
 8|3     |

Reservations

Id|SeatId|Name|Phone|DateTime           |
--|------|----|-----|-------------------|
 7|     1|    |     |2020-02-15 20:30:00|
 8|     1|    |     |2020-02-21 12:00:00|
 9|     1|    |     |2020-02-23 09:00:00|
10|     2|    |     |2020-02-15 20:30:00|
11|     2|    |     |2020-02-21 12:00:00|
12|     2|    |     |2020-02-23 09:00:00|
13|     4|    |     |2020-02-21 12:00:00|
15|     7|    |     |2020-02-15 20:30:00|
16|     7|    |     |2020-02-21 12:00:00|
17|     8|    |     |2020-02-21 12:00:00|
14|     8|    |     |2020-02-23 09:00:00|
18|     3|    |     |2020-02-15 20:30:00|
19|     3|    |     |2020-02-21 12:00:00|
20|     3|    |     |2020-02-23 09:00:00|

When I tried the code by Phong, I got the following error:

System.InvalidOperationException: 'The LINQ expression '(GroupByShaperExpression: KeySelector: (r.SeatId), ElementSelector:(EntityShaperExpression: EntityType: Reservation ValueBufferExpression: (ProjectionBindingExpression: EmptyProjectionMember) IsNullable: False ) ) .ToList()' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.'

1
0
2/15/2020 1:59:57 PM

Accepted Answer

Phong's solution might be absolutely valid and useful but since I couldn't understand it myself and apply it, I used this method

var seats = _dbContext.Seats.ToList();
var reservations = _dbContext.Reservations.Where(r => r.DateTime.Day == dateTime.Day).ToList();

foreach (var seat in seats)
{
    seat.Reservations = reservations.Where(r => r.SeatId == seat.Id).ToList();
}

I still believe there's also a LINQ one-liner that can do this, without the performance hit of .ToList()-ing every collection but I couldn't figure it out :)

0
2/16/2020 7:55:07 PM

Popular Answer

You should GroupBy() like below.

var seatsFiltered = _dbContext.Reservations
                          .Where(r => r.DateTime.Date == dateParam.Date))
                          .GroupBy(r => r.SeatId)
                          .Select(g => new 
{
  SeatId = g.Key,
  ListOfReservations = g // No need to .ToList() here
}) .ToList();


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow