NHibernate query with FetchMany returns incorrect results

Question

I have two related entities: Job and Group, with a Many-To-Many relationship.

I am performing a simple query to retrieve a specific Job and it's associated Group (through the GroupRecipients property):

var job = jobsRepo.Get()
                .Where(j => j.Id == jobKey.Id)
                .FirstOrDefault();

var countA = job.GroupRecipients.Count;

The result of which is countA==2, which corresponds to the state in the database.

The first weirdness occurs when I add a FetchMany:

var job = jobsRepo.Get()
                .FetchMany(x => x.GroupRecipients)
                .Where(j => j.Id == jobKey.Id)
                .FirstOrDefault();

var countB = job.GroupRecipients.Count;

This results in countB==1. Only one item appears in the job.GroupRecipients collection, which contradicts the state in the database.

But it get's even more interesting. If I run the following in succession:

var job = jobsRepo.Get()
                .Where(j => j.Id == jobKey.Id)
                .FirstOrDefault();

var countA = job.GroupRecipients.Count;

var jobB = jobsRepo.Get()
                .FetchMany(x => x.GroupRecipients)
                .Where(j => j.Id == jobKey.Id)
                .FirstOrDefault();

var countB = jobB.GroupRecipients.Count;

Then I get countB==2, the expected result. Removing the line with countA again causes countB==1.

Some more info: I perform the queries in a transaction in a stateless session. The version of NHibernate is 3.3.1

The two issues can thus be summarized as following:

FetchMany returns partial results
One query is dependent on another query in an unexpected way.

Any explanation of this behavior is very welcome.

i thought stateless session can not lazyload collections. could you please post the jobsrepo.Get() Method plz? — Firo
– Firo, Commented Aug 30, 2012 at 19:06
Here are some more explanations: stackoverflow.com/a/6392490 — Guru Stron
– Guru Stron, Commented May 7, 2015 at 14:04

misnomer · Accepted Answer · 2013-05-20 11:41:36Z

3

This appears to be a bug or mismatch in the way the LINQ provider handles FirstOrDefault - on mine, it appears to add a blanket LIMIT 1 to the query, which obviously doesn't work with the eager loading (which results in an OUTER JOIN).

I've solved it by explicitly converting to an enumerable in the middle:

var job = jobsRepo.Get()
                  .FetchMany(x => x.GroupRecipients)
                  .AsEnumerable()
                  .FirstOrDefault(j => j.Id == jobKey.Id);

Which is a bit of a hack, but solves the problem.

Likely, your second example works because the first loads the entity into the cache, and so nHibernate doesn't feel the need to go to the database (and encounter the faulty logic in the LINQ provider).

answered May 20, 2013 at 11:41

misnomer

2,7142 gold badges16 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

JasonCoder Over a year ago

In this example you are pulling back every job (and associated GroupRecipient) and doing the FirstOrDefault in memory. I believe you could throw a .Where just prior to the FetchMany to at least restrict that a bit. charlass.wordpress.com/2012/03/11/…

Robert Over a year ago

Do you know if this bug was solved in any version of nhibernate?

wezzix Over a year ago

.SingleOrDefault() also works. See stackoverflow.com/questions/6380494/…

Collectives™ on Stack Overflow

NHibernate query with FetchMany returns incorrect results

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related