Linq to Sql .Except not working if null in C#

4
votes

I have a simple query.

It checks for a list of rooms - prebookedRooms

It then checks for another list of rooms, but uses the .Except operator, to remove any prebookedRooms from the subsequent list:

        // Get list of rooms already booked
        var prebookedRooms = dbt.Rooms
            .Where(room => room.Rentals.Any(rental =>
                (dteFrom >= rental.check_in && dteFrom < rental.check_out)));

        // Get list of rooms
        var rooms = dbt.Rooms.Where(r => r.h_id == AccID)
            .Except(prebookedRooms)
            .GroupBy(p => p.RoomTypes).Select(g => new RatesViewModel
            {
                TypeName = g.Key.t_name,
                TypeID = g.Key.t_id,
                TypeCount = g.Count()
            })
            .ToList();

This works fine - with rooms returning a list of rooms, with prebookedRooms excluded.

However, if prebookedRooms doesn't return any records (ie. Empty "Enumeration yielded no results") - then I get an error when executing the second query (var rooms = ...):

The cast to value type 'Int64' failed because the materialized value is null. Either the result type's generic parameter or the query must use a nullable type.

Is there anyway of checking if prebookedRooms is empty, within the second query, so I can avoid this error?

prebookedRooms model:

public class Room
{
    [Key]
    public long room_id { get; set; }
    public long hotel_id { get; set; }
    public long type_id { get; set; }
}

The error as it appears in VS is: enter image description here

Thank you,

Mark

2
c#linqlinq-to-sql
Is there any reason you can't split the query and test if the result of dbt.Rooms.Where(r => r.h_id == AccID).Except(prebookedRooms) isn't null before trying the .GroupBy? – ChrisF
Hi - rooms will always return records - it's only when prebookedRooms has no records, that the rooms query fails. Thanks, Mark – Mark
That's my point - by splitting the query at the Except you can check it's not null before doing the GroupBy. – ChrisF
How do I split the query though? I'm not sure how to do it, and then continue with the GroupBy afterwards. – Mark

2 Answers

1
votes

Can't you just resign from Except and write:

    var rooms = dbt.Rooms.Where(r => r.h_id == AccID)
        .Where(room => !room.Rentals.Any(rental =>
            (dteFrom >= rental.check_in && dteFrom < rental.check_out))).
        .GroupBy(p => p.RoomTypes).Select(g => new RatesViewModel
        {
            TypeName = g.Key.t_name,
            TypeID = g.Key.t_id,
            TypeCount = g.Count()
        })
        .ToList();
0
votes

Try using the null coalescing operator: ?? and then getting the IQueryable<T> equivalent of Enumerable<T>.Empty, which is Enumerable<T>.Empty().AsQueryable() and DefaultIfEmpty with an invalid room, so it doesn't go crazy about it being null || empty:

    var rooms = dbt.Rooms.Where(r => r.h_id == AccID)
        .Except(prebookedRooms ?? Enumerable.Empty<T>().AsQueryable().DefualtIfEmpty(new Room() { room_id = -1, hotel_id = -1, type_id = -1}))
        .GroupBy(p => p.RoomTypes).Select(g => new RatesViewModel
        {
            TypeName = g.Key.t_name,
            TypeID = g.Key.t_id,
            TypeCount = g.Count()
        })
        .ToList();

I don't know your type, so I don't know what to put in the Enumerable.Empty<T> call's generic parameter. Add that yourself.

Also, remember to be using System.Linq!