Here's two days of my life solved that was solved with by adding a single
line to my project. I'm posting it here for the benefit of others. It's an
odd enough case that I don't know that it should go into a FAQ.
It appears as though for some queries involving sql server 2005 and
ROW_NUMBER() the sort order is not always deterministic. I have the
following pieces which all lined up against me:
* One to Many relationship mapped with lazy=false, fetch=subselect
* Guid IDs for both entities
* Query with limits for selecting 10 out of the middle
* Filters affecting the mapped relationship
* Sorting on a non-unique field such as quantity ordered
* Using Criteria query
NH does this:
1. Grab results using the highly convoluted ROW_NUMBER() OVER(...
2. Determine that filters are in place and relation needs to be grabbed
3. Grab the relation with a sub-select as specified in mapping via
ROW_NUMBER() ...
4. Error!
What was occuring is that the results of the subselect for the second query
are in a different order than for the first select. This is NOT an NH bug
and is a bug with Sql Server. (I'm using 2005 express.)
More details can be provided on the bug if anyone wants them. Luckly the
solution is simple:
Always sort by ID at the end of your list of "Order By"s. Note that if
you're not using filters and fetch=subselect this probably won't affect you.
if(!string.IsNullOrEmpty(sort))
{
criteria.AddOrder(new Order(sort, dir));
}
criteria.AddOrder(new Order("Id", true));
-Will
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"nhusers" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/nhusers?hl=en
-~----------~----~----~----~------~----~------~--~---
