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

Lidia

If you're using Sql Server, have you tried running the SQL Profiler while running both of these queries? – Of

i.Group differs between the two. Identically named, but different altogether when on different types of collections. – Gretagretal

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. – Shaft

Also, you know that on requestWorking you are reading EVERY Item in the database and doing the where query in memory on the results... – Jaquelinejaquelyn

Try removing "i.Group != null &&" – Accusatory

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! – Lidia

@keysharpener, the question isn't if they generated sql, but what sql did they generate? – Batts

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. – Jamajamaal

I agree with @Jacob, for the Linq to SQL query the i.Group != null is not needed. (in linq to objects it is however) – Marlyn

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) – Marlyn

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. – Of

@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. – Gretagretal

Final question (maybe)... what is the SQL data type for Group_Id? – Of

Answer 1

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!

Lidia

Answer 2

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.

Batts

My guess would be something around null comparison, SQL NULL comparisons are always PITA. – Conakry

Answer 3

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.

Journalism