LINQ-to-SQL Query Returning No Results?
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 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.
Awesome. Thanks for the assistance! – davemackey Mar 7 at 15:18.
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?
Legacy code. :P – davemackey Mar 5 at 16:36 Corrected. Didn't make a difference.
– davemackey Mar 5 at 16:47.
LINQ to SQL provides an implementation of the standard query operators for objects associated with tables in a relational database. This chapter describes the LINQ to SQL-specific aspects of queries. Whether you write a query as a high-level query expression or build one out of the individual operators, the query that you write is not an imperative statement executed immediately.
It is a description. For example, in the declaration below the local variable q refers to the description of the query not the result of executing it. The actual type of q in this instance is IQueryable.
It's not until the application attempts to enumerate the contents of the query that it actually executes. In this example the foreach statement causes the execution to occur. An IQueryable object is similar to an ADO.NET command object.
Having one in hand does not imply that a query was executed. A command object holds onto a string that describes a query. Likewise, an IQueryable object holds onto a description of a query encoded as a data structure known as an Expression.
A command object has an ExecuteReader() method that causes execution, returning results as a DataReader. An IQueryable object has a GetEnumerator() method that causes the execution, returning results as an IEnumerator. Therefore, it follows that if a query is enumerated twice it will be executed twice.
This behavior is known as deferred execution. Just like with an ADO.NET command object it is possible to hold onto a query and re-execute it. Of course, application writers often need to be very explicit about where and when a query is executed.
It would be unexpected if an application were to execute a query multiple times simply because it needed to examine the results more than once. For example, you may want to bind the results of a query to something like a DataGrid. The control may enumerate the results each time it paints on the screen.
One benefit of deferred execution is that queries may be piecewise constructed with execution only occurring when the construction is complete. You can start out composing a portion of a query, assigning it to a local variable and then sometime later continue applying more operators to it. In this example, q starts out as a query for all customers in London.
Later on it changes into an ordered query depending on application state. By deferring execution the query can be constructed to suit the exact needs of the application without requiring risky string manipulation. Objects in the runtime have unique identity.
If two variables refer to the same object, they are actually referring to the same object instance.
I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.
Related Questions
How to iterate over results of a linq-to-sql query result and append the results?
Linq query with a Join, not returning the correct results?
SQL Query Returning Duplicate Results?
Linq query: append column to query results?
Linq to SQL - How to sort results from query?
How to convert a LINQ query from query syntax to query method?