Nhibernate Fetch/FetchMany/ThenFetch duplicate results

Question

I'm quering my database. The structure looks like below

Country 1..M CountryLocales

1 .. M

Cities 1..M CityLocales

So, each Country has multiple locales, each City has multiple locales and a Country has multiple cities.

I try to retrieve a city from the database. I want to prefetch the Citylocales, the country and the country locales.

To do this I perform this query:

        City city = Session.Query<City>()
                          .Where(x => x.Id == id)
                          .Fetch(c => c.Country)
                          .ThenFetch(c => c.CountryLocales)
                          .FetchMany(x => x.CityLocales)
                          .AsEnumerable()
                          .FirstOrDefault();

For some reason I now get both duplicate records for the CountryLocales and for the CityLocales (both twice)

How can I fix this?

Community · Accepted Answer · 2017-05-23 10:29:30Z

3

You should look into the Future method. This allows you to perform many feteches without bumping into this issue. Your current query is returning a Cartesian Product which you don't want. Using the Future method you can perform multiple queries each using one Fetch whose results are then aggregated together thus resulting in the desired result.

It might go something like this:

var result = Session.Query<City>()
       .Where(x => x.Id == id)
       .Fetch(c => c.Country)
       .ToFuture();

Session.Query<City>()
       .Where(x => x.Id == id)
       .Fetch(c => c.CountryLocales)
       .ToFuture();

Session.Query<City>()
       .Where(x => x.Id == id)
       .Fetch(c => c.CityLocales)
       .ToFuture();

// execute query
City city = result.AsEnumerable().FirstOrDefault();

Take a look at this answer for more information: Multiple Fetches in linq to nhibernate

edited May 23, 2017 at 10:29

CommunityBot

11 silver badge

answered Jun 12, 2013 at 14:03

Peadar Doyle

1,1102 gold badges11 silver badges29 bronze badges

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

8 Comments

Patrick Over a year ago

I'm quite confused.... Do you have any idea why my sample is not working? It sounds very logical, while your sample cost a lot more code..

Patrick Over a year ago

And now even more. The below code does exactly what I want (and I found this by accident) City city = cityRepository.GetAll() .Where(x => x.Id == id) .Fetch(c => c.Country) .FetchMany(x => x.CityLocales) .AsEnumerable() .FirstOrDefault();

Peadar Doyle Over a year ago

See the link at the bottom of my answer to see why you are getting duplicate records. It is due to you getting a Cartesian Product as explained if you click through the link.

Patrick Over a year ago

But that does not explain why the query I pasted in the comments is working.. weird..

Peadar Doyle Over a year ago

I suspect cityRepository.GetAll() is just pulling evey City from the database and then the Where and Fetch methods are subsequently called. A profiler such as NHProf would show you exactly what SQL was being generated.

|

Collectives™ on Stack Overflow

Nhibernate Fetch/FetchMany/ThenFetch duplicate results

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related