Linq's unexpected 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 query returns 0 elements, although it should return one. The following is a ToList() latter, but with a call to the ToList() method. This query works and returns the element 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 common Linq to Nhibernate method with a boolean attribute that determines whether the request should be executed in the cache (true) or in the database (false). There is apparently nothing wrong with this method, since it works fine in many other parts of the solution. The element mapping has nothing to do: no packages and the following parameters: lazy="false" schema="dbo" mutable="false" polymorphism="explicit"

Why is this so?

Edit

The generated sql query requestNoWorking ends with:

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

The generated sql requestWorking query is approximately equal to select * from dbo.Items

Answer 1

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

The queries I posted did indeed occur in the methods for checking for updates / deletes. My conclusion is that somehow the Item immutable element has distorted the results. But I do not understand why jobWorking worked then!

Answer 2

I assume the nhibernate session you have is returning a request. if so, the evaluation of the first request is delayed until .ToList() called, and the entire request is executed on the server. I suggest you run the trace on the sql server, if possible, or perhaps load NHProf to find out what the actual query is being executed.

the second query is evaluated as soon as you click the first .ToList() , so you retrieve the entire table from db and then filter using .net. I honestly cannot tell you why they will evaluate differently, but I assume that there is something with a display / configuration, which is why the db request will be written a little incorrectly.

Answer 3

All I see is that in the second version, your Where executes LINQ for objects, not LINQ to NHibernate. Therefore, the first version should do what LINQ to NHibernate does not digest very well.

I think this is i.Group != null , with which LINQ To NHibernate has a problem, given that using null is CLR dependent. You may need to use a different construct in LINQ to NHibernate to check for empty field values.