Open nhibernate-bot opened 6 years ago
Any work being done on this bug?
Paging have always worked differently in NHibernate when fetching was also used. The behaviour displayed by LINQ mirrors that of the underlying HQL.
It would be useful of course if the fetching part was "ignored" for the purposes of paging - or possibly even ignored completely if paging is also used. The latter might have implications for performance, but would perhaps make the LINQ query return a more naturally expected result. Would be a breaking change because code that closes the session before accessing the feched elements would no longer work.
Given that this is not something NHibernate has ever done it could be argued that this is not a bug but a feature request.
Yes, NHibernate does not support proper paging with fetching of collections. From a Linq standpoint, being used to what does EF causes this to look as a bug. But the reference for Linq behavior is more what does Linq-to-objects, and in Linq-to-objects, fetching simply does not even exist, there are no equivalents for it (nor any need for it indeed).
Anyway, irrespective of whether it should be called a bug or a feature request, the point of interest for you is that, as far as I know, no one works on this subject. NHibernate development is volunteers based, so it can be you who start working on it.
As already mentioned in this issue, there are a number of alternate ways, which is maybe the reason for not having anyone currently sufficiently interested in this for working on it:
Ceiling(n / batch-size) +1
one).in (previously queried entities ids)
. (So for LINQ, a .Where(e => fetchedIds.Contains(e.Id))
.). This results in 2 queries instead of n + 1.About using a subquery, which would mean combining the previous two queries into one, it also works. There are two ways of writing it:
.Where(e => s.Query<Entity>().Select(e2 => e2.Id).Skip(10).Take(5).Contains(e.Id))
. This generates an in
clause with an uncorrelated subquery..Where(c => s.Query<Entity>().Skip(10).Take(5).Any(e2 => e2.Id == e.Id))
. This generates an exists
clause with a correlated subquery. To be avoided in my opinion, unless the first way is not supported by the database or the entity (composite id cases).But if you have many fetches to do and want to avoid Cartesian products, it is better to use 2. and split the fetches in future queries.
This said, a more straightforward reason for having no one working on this subject is also that I think it will be quite hard to do that change. Currently paging is not handled at the HQL level, but is done by tweaking the SQL generated without paging, for injecting paging in it. And this is implemented in each dialect supported by NHibernate. Implementing paging on the root entity instead of the whole query would require a quite heavy rewrite of the SQL. Doing this on the full SQL generated without paging seems quite unpractical.
it could be argued that this is not a bug but a feature request.
For me this is a feature request as Fetch
is designed to be a "lower level" method which just adds a left join to include the desired relation.
Any work being done on this bug?
As already explained in NHibernate nothing was done on this matter but as I also needed this feature I've made an extension library NHibernate.Extensions quite a long ago that I still use and maintain. The library provides an Include
method that works similar to the EF Include
method. The difference is in how the query is made, in EF UNION ALL
is used where with this extensions one or multiple queries with a left join for each relation will be created that uses a subquery where the paging is applied. In short the Include
method uses the Fetch
method, future query and a subquery when paging is required. About performance, in my experience the UNION ALL
works faster up to ~5 relations (depends on the number of columns) if you have more than that the approach that this library use becomes faster.
Also from my experience the Include
method is faster that the lazy loading approach when you know in advance which relations you need to have (example: validating a very complex entity graph) and also easier to use/configure. But at the end mixing both is the best approach in my opinion where using lazy load/batching on relations that point to an entity that very frequently referenced and its data does not change often (e.g. entity that represent an user) and Include
for other relations.
I have checked the subquery solution, it indeed works, and I have updated my comment above. (I was wrongly thinking the paging was injected on the generated SQL after having already combined queries and sub-queries, but it is in fact injected in the subquery SQL before using it for generating the whole SQL query.)
@maca88 extension is quite another approach than globally changing the current paging implementation, and it handles only LINQ, not the other APIs like HQL or Criteria/QueryOver.
But it is nonetheless an interesting solution, especially since it also avoids the Cartesian product issue incurred by multiple collection fetches.
It could be viewed as a kind of LINQ query automatic rewriting. But as it may also split them into many queries, still executed in one round-trip to DB for those supporting multiqueries, it is a bit heavier than rewritings already existing in NHibernate.
Maybe should the NHibernate LINQ provider be changed for working like this extension.
It may be a breaking change for databases with insufficient subquery support, but I do not think there are many applications using a NHibernate paginated query with collection fetches, due to the rather unexpected results it currently yields.
Unrelated note about the .Take(3).Skip(2)
example of Lee Timmins: it is unsupported by NHibernate LINQ provider. It is handled like Skip(2).Take(3)
, which is not the same thing. If willing this to be fixed, I think a dedicated issue should be opened. But what is the point of writing .Take(3).Skip(2)
? Better rewrite it .Skip(2).Take(1)
.
rosieks created an issue — :