Linq-to-NHibernate issue with paging and counts

[Alastair]

I'm trying to perform this query on NHibernate 2.1.2 with
NHibernate.Linq (1.1.0.1001)

session.Linq<FieldStructure>().Skip(10).Take(10).Count()

This generates the following SQL

"NHibernate: SELECT TOP 10 y0_ FROM (SELECT count(*) as y0_,
ROW_NUMBER() OVER(ORDER BY CURRENT_TIMESTAMP) as __hibernate_sort_row
FROM FieldStructure this_) as query WHERE query.__hibernate_sort_row >
10 ORDER BY query.__hibernate_sort_row"

This query always returns 0 rows, I know I should be getting 10 rows.

If I perform the same query without the Count(), I will get 10 rows.
The SQL generated is...

"NHibernate: SELECT TOP 10 <list of fields>, ROW_NUMBER() OVER(ORDER
BY CURRENT_TIMESTAMP) as __hibernate_sort_row FROM FieldStructure
this_ left outer join Lookup lookup2_ on
this_.LookupId=lookup2_.LookupId) as query WHERE
query.__hibernate_sort_row > 10 ORDER BY query.__hibernate_sort_row"

Is this a bug?

Thanks

Alastair

[Fabio Maulo]

session.Linq<FieldStructure>().Skip(10).Take(10).ToList().Count()

--
Fabio Maulo

[Frans Bouma]

Looks like it. The link provider apparently sees the aggregate
expression as the outer query, but that shouldn't be done that way: the skip
and take expressions are consumed by an expression visitor but the values
they get as parameters should be applied to the query / sequence they work
on, and _that_ sequence is then the source of the aggregate expression,
which always works on a separate scope, so it could never be wrapped by a
take.

FB

[Fabio Maulo]

On Thu, Jul 22, 2010 at 2:26 PM, Ayende Rahien <aye...@ayende.com> wrote:

That product a very different query, though

--
Fabio Maulo

[Fabio Maulo]

For sure.

On Thu, Jul 22, 2010 at 2:26 PM, Ayende Rahien <aye...@ayende.com> wrote:

That product a very different query, though

On Thu, Jul 22, 2010 at 8:21 PM, Fabio Maulo <fabio...@gmail.com> wrote:

--
Fabio Maulo

[Fabio Maulo]

Nobody knows which is the real target of the query... well... nobody... perhaps the user knows.

Wherever you put the Count() I can't understand the sense.

--
Fabio Maulo

[Frans Bouma]

> Nobody knows which is the real target of the query... well... nobody...
> perhaps the user knows.
> Wherever you put the Count() I can't understand the sense.

of course the real target is known: it's the result of the Take()
method. Skip & Take are query modifiers, not real query returning methods
('query' in the sense of a select statement). The source of skip is a query,
so skip & take modify that query and it then becomes the source of count.

Same sort of query:
session.Linq<Foo>().Count(c=>c.SomeField=="Bar");

here no skip/take is present, but as these are query modifiers, it's
not really a different construct: you have a query and it's used as the
source of the aggregate.

FB

[Alastair]

Ok, I think I should explain a little further. I'm using a third-
party control (Trirand's jqgrid MVC control) which accepts an
IQueryable<T>, and using Reflector have worked out that this is the
kind of call they are making.

I agree that doing a ToList() then Count() would give the correct
result, and arguably this control should be rewritten to do this
(since they are subsequently calling ToList() anyway). However I
don't have access to their code and besides it seems to me that just
calling Count() should work as expected.

Should I log this as a bug somewhere??

Thanks very much for your replies.

Alastair

> > Fabio Maulo- Hide quoted text -
>
> - Show quoted text -

[Frans Bouma]

> Ok, I think I should explain a little further. I'm using a third- party
> control (Trirand's jqgrid MVC control) which accepts an IQueryable<T>, and
> using Reflector have worked out that this is the kind of call they are
> making.
>
> I agree that doing a ToList() then Count() would give the correct result,
> and arguably this control should be rewritten to do this (since they are
> subsequently calling ToList() anyway). However I don't have access to
their
> code and besides it seems to me that just calling Count() should work as
> expected.

knowing that the DevExpress' grid with their linqdatasource creates
complex linq queries with much more complex issues than the one you posted,
(like grouping on a constant :)), it's inevitable to simply support these
constructs, otherwise people will simply not use the linq provider (and that
would be a waste of effort).

the query you reported is a valid linq query, it should produce the
query you expected, so I'd opt for filing a bug report, but it's up to Steve
(where's Steve anyway? ;)) if he has time to fix this.

The 'ToList()' workaround is silly really, you don't want to fetch
all data to do an aggregate in-memory

> Should I log this as a bug somewhere??

you can log issues here: http://216.121.112.228

FB

[Fabio Maulo]

eh?!??!??

NH ha to workaround RDBMS issue.

NH have to work around to commercial companies visual components.

Something else ?

a coffee ?

--
Fabio Maulo

[Frans Bouma]

> eh?!??!??
> NH ha to workaround RDBMS issue.
> NH have to work around to commercial companies visual components.

hahaha :D. Trust me, I really had the same reaction initially :)
Especially when I found out my linq provider crashed always, with every
query created by DevExpress' linqdatasource because they were always
grouping on a constant value (e.g. 0).

but what can you do: the user wants to use the grid, the linq
datasource or whtever control the ui control vendor cooks up to produce linq
stuff, and if the o/r mapper doesn't work that way, the user will likely
look elsewhere to use something which does work with the grid. Sadly, o/r
mapper developers are in that tough spot where all the problems of other
people are supposed to be solved. ;).

In the end, it's not that hard really. It's just similar to the
VB.NET stuff: you'll find out along the way, which is of course a sucky
situation, but alas, our destiny as o/r mapper developers ;)

FB

[Roy Jacobs]

> > The 'ToList()' workaround is silly really, you don't want to fetch
> > all data to do an aggregate in-memory

> eh?!??!??
> NH ha to workaround RDBMS issue.
> NH have to work around to commercial companies visual components.

I think Frans' point is that even though the Count() seems redundant
after a Take(), it's still a completely valid LINQ query.

Certainly, when one is directly writing the LINQ query, it's not a
problem to simply add a ToList(), but when working with third-party
components like the original poster is, it's not always reasonable to
expect them to be able to modify the query.

Having said that, I think everyone is aware how complex writing a LINQ
provider is :)

--
Roy

[Fabio Maulo]

and everyone should think that NH is not the place where ask the solution of all evils.

--
Fabio Maulo

[Frans Bouma]

> and everyone should think that NH is not the place where ask the solution
of
> all evils.

I don't think that's the point. The point is:
1) if NH says it contains a working Linq provider, a user can only assume it
indeed works. If it doesn't, the user can only conclude: the linq provider
doesn't work or has a bug. If a feature hasn't been implemented, the linq
provider is thus incomplete. Unfortunately, an incomplete linq provider is
more a burden than a blessing.
2) if NH wants to be the best o/r mapper out there, a working linq provider
is essential. The main reason is that more and more people will learn about
o/r mapping and learn Linq, how it works etc. as there are many books,
articles written for EF and Linq to SQL and linq itself. If these people
can't use their knowledge with NH, the barrier to accept it as the best
there is is higher.

FB

[Fabio Maulo]

NH has and will have bugs as any others software.

That is what everybody should assume.

NH is not the best persistence framework in .NET ecosystem, it is "only" the most used, the most powerful, the most flexible so far.

If a user can find something else that fit his needs, there is no problem.

We can do our best but "make everybody happy" is not one of our target.

--
Fabio Maulo

[Rumen Stankov]

Hello Folks,

I'm working on the grid in question, Trirand's jqGrid for ASP.NET
WebForms/MVC:
http://www.trirand.net/demoaspnetmvc.aspx

Thanks to Alastair for starting this obviously very popular thread.
The reason the query is written that way Skip(x).Take(y) is that "y"
is the fixed page size of the grid, say, 10, but we can be on the last
page and the actual page size would be, say, 6 - hence we need the
count and it might be different than "y".

That said, we are definitely interested in making sure we work with
NHibernate (and in fact any other popular ORM engines out there like
LLBLGen), so we will probably use some of your ideas and modify the
LINQ query sequence so that count is correct.

Thanks a lot for the suggestions.

Regards,
Rumen Stankov
Trirand Inc.

> > > On Fri, Jul 23, 2010 at 10:05 AM, Roy Jacobs <roy.jac...@gmail.com>

[Rumen Stankov]

Just a quick update, if this helps - we are actually using the Dynamic
LINQ Library (we decided we need support for anonymous types)
http://msdn.microsoft.com/en-us/vcsharp/bb894665.aspx

and there is no ToList() there. Not a showstopper, I've already
implemented an Extension Method that does something similar, but just
decided to share since it might be interesting for you.

On Jul 23, 5:35 pm, Fabio Maulo <fabioma...@gmail.com> wrote:

> > > On Fri, Jul 23, 2010 at 10:05 AM, Roy Jacobs <roy.jac...@gmail.com>

[Fabio Maulo]

The Dynamic-LINQ is that using strings instead of strongly-typed ?

--
Fabio Maulo

[Rumen Stankov]

Hello,

Well, you can say it that way, yes. But it's a bit more complicated
than that. While typically most people will strongly type the query,
many others will prefer just anything that they don't know. Hence we
decided to use that. So as a UI provider that binds to anything (on
theory), we decided this would be good:

http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx

Just wanted to share, since it might help a bit. I believe you could
be interested in the details.

Cheers,
R.

On Jul 31, 5:21 pm, Fabio Maulo <fabioma...@gmail.com> wrote:
> The Dynamic-LINQ is that using strings instead of strongly-typed ?
>

[Fabio Maulo]

WOW!!! Create OO but not strongly-typed queries at runtime... that's coooool Man!!!

I never heard about it before.

--
Fabio Maulo

[Rumen Stankov]

Not really into political arguments. We have code that still binds to
DataTables and DataSets and have market for this too. Plus, it's not
us that make this decision, but our customers - we want to maximize
our potential market and it's working fine for us so far.

Anyway, I've changed our data code to work-around this NHibernate
understanding of LINQ. Thanks a lot for the suggestions.

If you guys encounter problems with out products with NHibernate,
please let us know - we want to support NHibernate as much as
possible.

PS. http://www.west-wind.com/weblog/posts/134706.aspx

Cheers,
R.

On Jul 31, 5:44 pm, Fabio Maulo <fabioma...@gmail.com> wrote:
> WOW!!! Create OO but not strongly-typed queries at runtime... that's coooool
> Man!!!
> I never heard about it before.
>

> On Sat, Jul 31, 2010 at 11:30 AM, Rumen Stankov <rumen.stan...@gmail.com>wrote:
>
>
>
>
>
> > Hello,
>
> > Well, you can say it that way, yes. But it's a bit more complicated
> > than that. While typically most people will strongly type the query,
> > many others will prefer just anything that they don't know. Hence we
> > decided to use that. So as a UI provider that binds to anything (on
> > theory), we decided this would be good:
>

> >http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1...

[Fabio Maulo]

When you implements such kind of system you should be aware that each query hit DB and you should avoid unnecessary hit (as to know the amount of items of the actual page)

--
Fabio Maulo

[Wenig, Stefan]

Just a reminder: this issue (skip/take/count) has only been reported for the old contrib-provider, not for NH3.

Gesendet von meinem HTC

----- Ursprüngliche Nachricht -----
Von: Rumen Stankov <rumen....@gmail.com>
Gesendet: Samstag, 31. Juli 2010 16:19
An: nhibernate-development <nhibernate-...@googlegroups.com>
Betreff: [nhibernate-development] Re: Linq-to-NHibernate issue with paging and counts

[Steve Strong]

Thanks Stefan, I'm way behind on my emails but was just about to reply with exactly that :)  Has anyone tried this query with the new provider?  I would expect it to work, and if it doesn't it should be fixed for sure.  If no one has, then I'll knock up a test.