Specification for Query() method question

[aay]

Hello,

Ritesh, thanks a lot for your awesome framework! I was very surprised
when I first found it - you implemented most of the patterns and ideas
I've been researching and thinking about applying and learning better.
So it was a very pleasant surprise to find that someone smart has
already transformed most of the ideas into code. And boy, what a great
code!

I have a question for you or anyone who can help me with my issue (is
this an appropriate place to ask?). Unfortunately I haven't been
working much with .NET past v2, so I didn't have much chance to play
with Linq. Now, implementing data access with nHibernate and nCommon,
I'm trying to use IRepository<T>.Query(ISpecification<T>), but for a
little more complex case rather than simple "customer's name should be
Bob or customer's city should be LA". In my case I've got a repository
for Order which contains list of items and I want to load only those
items that were sold via Internet store (discriminator column in the
table). So, my specification's Linq expression is as follows:

new Specification<Order>(o => o.Items.Any(i => i.Store ==
"Internet"));

and I'm not 100% sure that it is a correct Linq expression that can be
transformed into something that nHibernate understands and will work
properly as my unit test hangs and then, after a delay, throws an
NHibernate.ADOException with a message "could not execute query" with
an underlying exception of System.Data.SqlClient.SqlException:
"Timeout expired. The timeout period elapsed prior to completion of
the operation or the server is not responding." SQL Server is running
normal and any other test case that doesn't utilize a specification
completes normal.

After looking on the internet for anything related to collections and
nHibernate.Linq I kind of confirmed that "Any" extension method is
used in this sort of case

The test code is as follows:

Order result;
using (new UnitOfWorkScope())
{
NHRepository<Order> repository = new NHRepository<Order>();
result = (from o in repository.Query(new Specification<Order>(o =>
o.Items.Any(i => i.Store == "Internet")))
where o.Id == 50
select o).FirstOrDefault();

Assert.AreEqual(7, result.Items.Count);
}

The thing is, when I extract the query that nHibernate is attempting
to execute and run it manually, i get the result (well, the original
query that I see being unsuccessfully run by nHibernate gets only an
order, since Items should be lazy loaded) - a row with details of an
order. I can only assume, that there is something that makes
nHibernate unhappy and it stalls and throws the exception.

Up to now I implemented this functionality as nHibernate filter and I
modified NHRepository a little to apply this filter to an ISession.
However I would like to use nCommon functionality as much as I can
before going to the lower level.

Any sort of direction would be greatly appreciated.

Alex

[aay]

Well, a have a little more details on this issue. It looks to me that
in it's current implementation of nCommon (nHibernate.Linq?) this
particular task cannot be performed. I am not sure as to why - will
need to get into debugging nCommon, but here is what's happening.

1. As soon as I use method Query() with a specification, linq
statement "from .. where ... select" gets crippled and "where" part is
not being taken into account - generated SQL doesn't have a value for
Id in my case - SQL is trying to return every single Order I've got in
my DB. I think that's when nHibernate fails with an exception - it
loads too many instances and cannot deal with them? I think the reason
original 'where' part is not being taken into account is due to the
implementation of Query() method - "where" statement is being applied
based on a specification only (overwriting anything that was there in
the first place?):

RepositoryQuery.Where(specification.Predicate).AsQueryable();

2. I managed to work around issue in #1 by adding another
specification to the one I've got already /* new Specification<Order>
(o => o.Id == 50) */, but I really don't like this approach.

3. The problem with the SQL is that it is being modified according to
my original specification /* new Specification<Order>(o => o.Items.Any
(i => i.Store ==
"Internet")) */ in the first SQL call ONLY when an instance of an
order is being loaded, not when items are being accessed. This is for
a lazy loaded collection.

4. For eager loaded collections (introducing a fetching strategy that
instructs loading of items along with an order) I again use 2
specifications to narrow down the result for orders, but see the same
results - specification for Order.Id discriminator works, while
specification for Item.Store == "internet" is not being applied
correctly and I get all of the items, not only those that were sold
over Internet store.

So, in conclusion, it looks to me that IRepository.Query() method can
be used to narrow down the search only for the entity that is being
loaded initially by it's correspondent repository. Any attempt to
filter collections that this entity can have is not going to work -
you will have to use nHibernate filters. Furthermore, in case of using
Query() method, "where" part of linq statement is ignored, so the only
"filtering" applied is that which is defined by a specification.

Based on all of this, what is the purpose of Query() method if all it
does is mimics functionality of the "where" clause? Or am I missing
something here? Is "where" clause is not flexible enough and that's
why you introduced Query() method?

I would really appreciate if Ritesh could take a look at this and
confirm this behavior. Thanks!

[Ritesh Rao]

Alex,

I'll take a look at this in detail in a bit. Just a note though, the
current implementation of NHibernate.Linq does not do well with
Subqueries and inner queries. It's because it is not capable of
translating sub-queries to valid SQL queries. This limitation is
because the current implementation is using the Criteria API
underneath the covers, and in cases where sub-queries / inner-queries
are involved it cannot translate the linq expression to a criteria
query expression.

This limitation is being addressed with the new implmentation of linq
to nhibernate that will be part of NH 3.0 hopefully. Since its using
the new AST parser that Steve has worked on, it has much better
fidelity in converting linq expressions to NH query represnetation.

In any case I'll run some tests on this scenario in a bit and if this
is an NCommon issue, i'll get it fixed.

Thanks,
Ritesh

[aay]

Ritesh,

Thanks a lot for your quick reply. Hopefully there is something that
you can do, but something tells me that it's a little deeper than
nCommon.

Also, could you please expand on the reason for Query() method? Am I
wrong in my assumption that it performs pretty much the same function
as "where" clause of a linq statement? Maybe linq statement is
somewhat limited and that's when you need extra flexibility? I just
don't want to misuse something and then realize that I did something
wrong.

Thank you, Ritesh!
Alex

[Ritesh Rao]

Yes, when a Specification<T> is applied to a repository instance via Query, it appends the specification as a Where predicate to the IQuerable. 

The intent behind the Query method is to be able to take existing compiled ISpecification<T> instances that represent business queries or filters that can be used to filter data being retrieved. If you have a simple filter that is not really a business specification, then there's no advantage over calling Where. 

- Ritesh

[Ritesh Rao]

Alex,

I just ran a couple of tests to replicate your issue and I am not able
to generate the same error. Could you please let me know the version
of FluentNHibernate, NHibernate and NHibernate.Linq you are using?

I added a test in NHRepositoryTests [in project
NCommon.NHibernate.Tests project] that tries to replicate your issue:
testing_specification_with_sub_query. Take a look and let me know if
your scenario is different.

Code checked into source.

Thanks.

[aay]

Ritesh,

No, I still have the issue. I took a look at your test and made my
test exactly like yours with no success.

1. Versions:

NHibernate - 2.1.0.4000
NHibernate.Linq - 1.0.0.4000
I do not use FluentNHibernate at all.

I do use your latest SVN copy of code and not the released stable
version.

2. I am trying to run your tests, but keep getting assert failure - as
if it doesn't get the order at all (fails on not null check). I cannot
figure out how to display SQL generated by nHIbernate when using
Fluent, so I cannot check what exactly is being run against the DB. I
checked the DB itself (took me a little to set it up and figure out
that you build actual schema from code every time you run tests, but
data is in a separate sql file) and it seems to have all data in
there, so I'm not sure what is going on at the moment. All other tests
in that file seem to run ok and I get green.

3. My tests behave the same way as before - they fail in two different
ways.

a. When I run either one of the following two variations of code,
which specify "where" clause in 2 different notations:

using (new UnitOfWorkScope())
{
NHRepository<Order> repository = new NHRepository<Order>();

Order result = (from o in repository.Query(new

Specification<Order>(o => o.Items.Any(i => i.Store == "Internet")))
where o.Id == 50
select o).FirstOrDefault();

Assert.AreEqual(7, result.Items.Count);

}

OR

using (new UnitOfWorkScope())
{
NHRepository<Order> repository = new NHRepository<Order>();

Order result = repository

.Query(new Specification<Order>(o => o.Items.Any(i => i.Store ==
"Internet")))

.Where(o => o.Id == 50)
.FirstOrDefault();

Assert.AreEqual(7, result.Items.Count);
}

my unit test hang and I eventually get NHibernate exception that I was
referring to in my original post. After I check the SQL I see that
"where" clause is being ignored and the test is trying to pull all of
the orders from the DB (I've got millions of rows in this legacy DB,
so this might be something that you cannot simulate with your test
DB).

b. The only time I get SQL that doesn't hang is when I provide 2
specifications into Query method (and leave "where" out completely):

.Query(
new Specification<Order>(o => o.Items.Any(i => i.Store ==
"Internet"))
&

new Specification<Order>(o => o.Id == 50)

)

but this code results in an incorrect SQL being generated (remember,
that I try to lazy load items): SQL for loading an Order has both
discriminators (order.Id and item.Store, even though there is no use
for item.Store at this point), but subsequent SQL for loading actual
items has only order.Id discriminator (while it should have item.Store
discriminator as well as this point). So, running this code results in
me getting ALL of the items, not only those that belong to Internet
store.

4. May I suggest not to check for item collection size against being
over 0, but to check that it is actually of an expected size? This way
you will see whether it pulls all of the items or only those that
belong to "StoreC" and I'm pretty sure that you will see that instead
of 2 elements you will end up with 6.

Well, I think I answered all of your questions and I tried to provide
as much details and input as I could think about.

Thanks a lot!

Alex

[aay]

Hi Ritesh,

Sorry to bug you, but I was just wondering whether you had a chance to
see my reply above. Nothing urgent, but I wanted to know your final
thoughts on this issue. Whenever you have time.

Thank you,
Alex

[Ritesh Rao]

Hi Alex, sorry for the late response. Looking a little deeper in your scenario, I don't think it will be possible to do what you are looking for without NH filters. Basically the specification o.Items.Any(x => x.Store == "Internet" is a filter that is applied ON the order entity, so it acts as a sub query to only retrieve orders that contain Internet store items. Since the Items association is lazy loaded, the only way to filter the lazy loaded items is to filter them at the time they are fetched. That is only possible by using NH filters.

The only other way is to use an inner join to load along with eager loading. Unfortunately the current implementation of NHibernate.Linq doesn't support inner joins so you're stuck with using filters I'm afraid.

Hope this helps.

Ritesh

[aay]

Hi Ritesh! I understand that you are a busy man and there are a lot of
other things to do besides answering these questions, so no worries
and thank you for taking time to look into this issue.

Yes, I'm using filters for now as a work around, since I really want
to use nCommon - it is a great framework. Hopefully you will not
abandon it and keep touching it every now and then with new patterns,
approaches and ideas.

Thank you,
Alex

On Sep 29, 8:45 pm, Ritesh Rao <rao.rit...@gmail.com> wrote:
> Hi Alex, sorry for the late response. Looking a little deeper in your
> scenario, I don't think it will be possible to do what you are looking for
> without NH filters. Basically the specification o.Items.Any(x => x.Store ==
> "Internet" is a filter that is applied ON the order entity, so it acts as a
> sub query to only retrieve orders that contain Internet store items. Since
> the Items association is lazy loaded, the only way to filter the lazy loaded
> items is to filter them at the time they are fetched. That is only possible
> by using NH filters.
> The only other way is to use an inner join to load along with eager loading.
> Unfortunately the current implementation of NHibernate.Linq doesn't support
> inner joins so you're stuck with using filters I'm afraid.
>
> Hope this helps.
> Ritesh
>

> ...
>
> read more »