CODEWITHSUNDEEP
linqnhibernate
Patrick
Patrick

Reputation: 2790

Nhibernate Fetch/FetchMany/ThenFetch duplicate results

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?

Upvotes: 2

Views: 6088

Answers (1)

Peadar Doyle
Peadar Doyle

Reputation: 1112

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

Upvotes: 3

Related Questions