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?

score 3 · Accepted Answer · edited May 23 '17 at 10:29

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 '17 at 10:29

Community

1
1

answered Jun 12 '13 at 14:03

Peadar Doyle

1,064
2
10
26

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 Jun 12 '13 at 14:38
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(); – Patrick Jun 12 '13 at 14:43
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. – Peadar Doyle Jun 12 '13 at 15:01
But that does not explain why the query I pasted in the comments is working.. weird.. – Patrick Jun 12 '13 at 16:35
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. – Peadar Doyle Jun 12 '13 at 18:19
Getall is a session.query. Ill take a look at the generated query. – Patrick Jun 12 '13 at 21:25
Your query does work. Though, ToFuture is not a standard linq thing so I'm unsure yet how happy I am. – Patrick Jun 13 '13 at 09:16
I haven't come across a better way to eager load multiple collections. It's a long winded approach but we end up getting exactly what we're looking for. – Peadar Doyle Jun 14 '13 at 15:43

Nhibernate Fetch/FetchMany/ThenFetch duplicate results

1 Answers1