4

I believe this issue is caused by the ignoring of the FetchKind property.

I have an employee table:

Employee
{
    ID
    Name
    PositionCode
}

and a position table:

Position
{
    Code
    Description
}

I've joined these tables using Mapping.ByCode and set Fetch to Join, but it doesn't seem to be working.

I've looked over a dozen stack overflow posts asking the same question, but can't seem to get a clear answer.

public class EmployeeMapper: ClassMapping<Employee>
{
    public EmployeeMapper()
    {
        Lazy(false);
        Table("EMPLOYEE");
        Id(x => x.Id, m => m.Column("ID");
        MapToOne(c => c.PositionCode, posMap =>
        {
            posMap.Lazy(LazyRelation.NoLazy);
            posMap.Fetch(FetchKind.Join)
            posMap.Column("CODE");
        }
    }
}

I've used the NHibernate profiler and it behaved as follows:

Select all employees
foreach employee
    select position

Note that this isn't one sql select, if there are 1000 employees, this expands out to 1001 select statements (one for all employees, then one per position).

If anyone can help it would be greatly appreciated.

rogersillito
  • 869
  • 1
  • 10
  • 27
SamuelKDavis
  • 1,033
  • 1
  • 7
  • 14

1 Answers1

2

The general NHibernate way how to avoid 1 + N issue is to use the BatchSize() setting. This way should be more appropriate then using JOIN Fetching in the mapping (Let's use it for querying)

There are two of them:

  • entity level
  • collection level

See Mapping-by-Code - entity-level mappings, by Adam Bar, where we can see that mapping by code support:

BatchSize(25)

In general, NHibernate, will load more collections (Lists, Maps) at once, using the IN clause, with more parentIds. This will lead to cca 1+(N/25) ... which is, I would say, reasonable.

Until we won't need collection... it is loaded lazily. If we needed, all its parts are loaded in batches.

There are some similar links, related to BatchSize setting and 1+N issue:

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Radim Köhler
  • 122,561
  • 47
  • 239
  • 335
  • I'm joining a record with 1000 entries with 3 different tables (database is in another country closer to our clients). This takes half a minute to run the full select, but around 5 minutes if it does each of the selects. I don't think batching is a good idea in this case as the time delay is too large. – SamuelKDavis Apr 09 '14 at 05:21
  • The trick here, I would say, is: 1) **the mapping** should be as **light** as possible. Then you can take 100 or more parents... without any need of loading the related "Position" - if Positions collections is not needed. 2) The querying should solve the issue if we need 100 or much more Parents (employees) and Children (postions). We should create separated queries, load the "large" portion of data, convert them to some DTO and send.... – Radim Köhler Apr 09 '14 at 05:24
  • 1
    Samuel, please, take a look on these two answers http://stackoverflow.com/questions/22939302/. This is really very similar to what I say. Keep the mapping light, use queries to load DTO... I would say... – Radim Köhler Apr 09 '14 at 05:26