current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 2 down vote favorite

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

share|improve this question
    
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♦ Jun 1 '13 at 15:59
    
Hi - rooms will always return records - it's only when prebookedRooms has no records, that the rooms query fails. Thanks, Mark –  Mark Tait Jun 1 '13 at 16:03
    
That's my point - by splitting the query at the Except you can check it's not null before doing the GroupBy. –  ChrisF♦ Jun 1 '13 at 16:05
    
How do I split the query though? I'm not sure how to do it, and then continue with the GroupBy afterwards. –  Mark Tait Jun 1 '13 at 16:17
add comment

2 Answers

up vote 1 down vote accepted

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();
share|improve this answer
add comment
up vote 0 down vote

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!

share|improve this answer
    
Thank you for the suggestion. Unfortunately it had no effect (I did add the correct type - but prebookedRooms still "yielded no results" and when the second query was run, the error remained. –  Mark Tait Jun 1 '13 at 16:15
    
That's pretty weird... means it treats empty collections as null. –  It'sNotALie. Jun 1 '13 at 16:20
    
I tried adding the ?? in the second query, as per your update above - but still I get the same error. –  Mark Tait Jun 1 '13 at 16:20
    
@fixit Is there one value that will never be in the DB and you could therefore safely pass it to DefaultIfEmpty, so then it won't throw that weird error? –  It'sNotALie. Jun 1 '13 at 16:25
    
Hi - I've been checking that out - preBookedRooms would hold a collection of long, long, long - eg. 1245,56,3 - how would I enter that into DefaultIfEmpty() - DefaultIfEmpty(1245,56,3) isn't allowed. Thank you again. –  Mark Tait Jun 1 '13 at 16:33
show 10 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.