We are trying to create a query (via LINQ) that will return all rows in a table that has some column’s value set to null, e.g.:
var list = from o in orders
where o.Customer == null
The above example (an extension of the example at http://www.devexpress.com/kb=K18051) does not even do a query on the server side (viewed by a SQL profiler) and thus return no result (not even an exception) even if there are rows that have the “Customer” column set to null.
How do you suggest we write the above LINQ query so that it will actually return some results (i.e. producing a SQL where-clause that will contain “Customer is null”)?
The LINQ query doesn't access the database itself. The database is actually accessed when the query is evaluated either directly using the foreach statement or indirectly using aggregates, ToList method etc.
It is unclear exactly how you're using this query. Please provide us with a sample project reproducing this issue.
I'd say that if you do not know that about LINQ, you should not even being using it (or at least go read more on it). So yes, I do know LINQ does not access the database directly, nor does it evaluate the query immediately after the LINQ statement, but only when it is accessed in methods you just mentioned (and via the XPO LINQ provider). However, when the time comes that the query needs to be evaluated (while using a relational database like SQL as the data provider for XPO), the statement propagated to SQL would/should closely resemble the LINQ query, given that you only used persistent properties (and with the exception of the GCRecord used for deferred deletion). Should non-persistent properties be used, it is by assumption that the rest of the query is evaluated on the client side (i.e. in the XPO ‘engine’).
Now, I’ve prepared you an example. In it you’ll find that I use 2 methods of querying the data. The first is by LINQ to XPO, the second is by the old XPCollection+Criteria method, using the same logical criteria in both (with the exception of the “== null” in LINQ and the “is null” on XPCollection, since the latter does not allow the “== null” statement as answered in http://www.devexpress.com/issue=Q95536). You’ll notice that the LINQ method produces no results.
Is your intention to post an answer to your own question?
- If so, then proceed.
- If you simply wanted to post additional information, ask for further clarification, or to just say "Thanks!", please click Leave a Comment.
- If you wish to edit your original question, please use the Edit button in the Toolbox at the top right corner of that entry.