LINQ-to-SQL Query Returning No Results

I have a query using LINQ-to-SQL. It queries an underlying database table Rooms. It uses Where conditions to narrow down the results, namely:

1. Gender.
2. Current Occupancy < Max Occupancy
3. Available Flag is Checked

I know this should return results but it keeps returning an empty set. Any ideas? Code is below

    Dim selectedHalls = (From sh In dbHalls.Rooms _
                         Where sh.gender = Session("gender").ToString _
                         Where sh.max_occupancy > sh.cu开发者_StackOverflow社区rrent_occupancy _
                         Where sh.is_available = 1 _
                         Select sh.building_name).Distinct()

UPDATE: I've verified that the issue is with the statement where sh.is_available = 1, which doesn't make sense since this is a bit field.

I think the best way to find out what the problem is to generate the SQL string and test your assumption that "it should return results" by executing it directly against the data source.

To do this:

      Dim sqlQuery As String =  dbHalls.GetCommand(
                    (From sh In dbHalls.Rooms _
                     Where sh.gender = Session("gender").ToString _
                     Where sh.max_occupancy > sh.current_occupancy _
                     Where sh.is_available = 1 _
                     Select sh.building_name).Distinct()
              ).CommandText

(I usually use C#, but I think that's how you declare a string in VB, right?)

Anyway, this will give you an SQL statement that will be more informative than anything we can give you without being able to look at your underlying database.

The only thing that jumps out at me as being potentially problematic is the Session("gender"). You are basically relying on your Session object to be populated, have a value for the case-sensitive string key "gender" that matches the case-sensitive string field gender in your database. This sounds like quite a few assumptions, that may or may not be tested and could be the reason for receiving empty results.

EDIT I just saw your update. Linq-to-sql, interprets a bit field as a boolean value, not an integer value. Try changing it to just where sh.is_available

I don't see anything obviously wrong with your LINQ, so try removing the Where clauses and bringing them back one at a time. That should let you know what's causing the issue, though I agree with Andrew Vogel that the gender clause is a likely suspect.

If the query comes back empty after all the Where clauses are gone, then you'll know there's something else going on here.

Do you really record their genders as "Female" and "Male" in dbHalls.Rooms.sh?