Unexpected Linq Behavior - ToList()

Question

I have two similar queries theoretically returning the same results:

var requestNotWorking = SessionManagement.Db.Linq<Item>(false).Where(i => 
                        i.Group != null && i.Group.Id == methodParameter)
                       .ToList();

This request returns 0 items, even though it is supposed to return one. The following is a rewrite of the latter but with a call to the ToList() method. This request works and returns the item expected in the first query!

var requestWorking = SessionManagement.Db.Linq<Item>(false).ToList().Where(i => 
                     i.Group != null && i.Group.Id == methodParameter).ToList();

Note: SessionManagement.Db.Linq<Item>(false)is a generic Linq to Nhibernate method with the boolean attribute determining if the request must be executed in the cache (true) or the database (false). There is supposedly nothing wrong in this method as it works normally in many other parts of the solution. The mapping of Item is nothing fancy: no bags and the following parameters: lazy="false" schema="dbo" mutable="false" polymorphism="explicit"

Why is this so?

Edit:

The generated sql request of requestNoWorking ends with :

(Item.Group_ID is not null) and Item.Group_ID=@p0',N'@p0 int',@p0=11768

The generated sql request of requestWorking is roughly a select * from dbo.Items

If you're using Sql Server, have you tried running the SQL Profiler while running both of these queries? — nerdybeardo, Jan 03 '13 at 15:56
i.Group differs between the two. Identically named, but different altogether when on different types of collections. — , Jan 03 '13 at 15:57
Please post the generated SQL. There is a semantic difference between what the SQL does and what the C# semantics dictate. LINQ does not promise identical semantics. — usr, Jan 03 '13 at 15:57
Also, you know that on `requestWorking` you are reading _EVERY_ `Item` in the database and doing the `where` query in memory on the results... — Trevor Pilley, Jan 03 '13 at 15:57
Moe: both request generate different sql requests. @DeeMac please elaborate, i don't understand the differences between the two Where clauses. TrevorPilley: i'm aware of that and clearly trying to avoid this! — Keysharpener, Jan 03 '13 at 16:01
@keysharpener, the question isn't if they generated sql, but what sql did they generate? — nathan gonzalez, Jan 03 '13 at 16:03
LINQ providers' subtly different semantics are probably the biggest gotcha LINQ has. A really nasty design flaw for such an otherwise easy-to-use tech. — Eamon Nerbonne, Jan 03 '13 at 16:04
I agree with @Jacob, for the Linq to SQL query the `i.Group != null` is not needed. (in linq to objects it is however) — Magnus, Jan 03 '13 at 16:04
Note that `i.Group.Id == methodParameter` will use whatever collation is set on the server while the in memory query will use `Equals` on the object. (might not be the same at all) — Magnus, Jan 03 '13 at 16:06
Can you update your question to add the SQL requests that SQL profiler returned? It will help to see how SQL is evaluating the i.Group != null. — nerdybeardo, Jan 03 '13 at 16:11
@keysharpener - What I meant was that you're pointing at i.Group of a List in one instance and a completely different collection type in the first instance. Think of the 'Group' as "coincidentally" the same name, but completely different members. I'll think of an equivalent example and post here. — , Jan 03 '13 at 16:18
Final question (maybe)... what is the SQL data type for Group_Id? — nerdybeardo, Jan 03 '13 at 16:51

score 4 · Answer 1 · answered Jan 03 '13 at 16:00

i'm assuming the nhibernate session thing you've got going on there returns a queryable. if so, the evaluation of the first query is delayed until the .ToList() call, and the entire query is run on the server. I would suggest that you run a trace on the sql server if possible, or perhaps download NHProf to see what the actual query being executed is.

the second query is evaluated as soon as you hit the first .ToList(), so you're pulling the whole table back from the db, and then filtering using .net. i honestly can't tell you why they would be evaluating differently, but i assume there is something with the mapping/configuration that is causing the db query to be written slightly wrong.

My guess would be something around null comparison, SQL NULL comparisons are always PITA. — anydot, Jan 03 '13 at 16:27

score 1 · Accepted Answer · answered Jan 03 '13 at 17:10

I was very interested by your theory on c.Group.Id != null, which I found logical even though it contradicted other pieces of code in my solution. However, removing it did not change anything. I found that removing mutable="false"property solved the problem. It seems a bit magical but it worked.

The requests I posted were in fact happening in methods checking the possibility of update/deletion. My conclusion is that somehow making Item immutable falted the results. But what I don't understand is why requestWorking worked then!

score 0 · Answer 3 · answered Jan 03 '13 at 15:59

All I can see is that in the 2nd version, your Where is executed by LINQ to Objects rather than LINQ to NHibernate. So the first version must do something that LINQ to NHibernate doesn't digest very well.

I'm thinking it's the i.Group != null that LINQ To NHibernate has a problem with, seeing that the use of null is CLR-specific. You may need to use another construct in LINQ to NHibernate to test for empty field values.

Unexpected Linq Behavior - ToList()

3 Answers3