0

I'm measuring database calls on a slow site using NHibernate profiler, and have noticed immediately that the following is causing a Select N+1 issue.

I've never used NHibernate so was hoping someone could help me out?

 public virtual IQueryable<Employee> Employees()
 {
    return Session.Query<Employee>();
 }

 public IList<Employee> GetEmployeesByClientId(int clientId)
 {
     return Employees()
         .Where(e => e.ClientId == clientId && e.Deleted == false)
         .ToList();
 }

At the point of calling ToList() a select statement is ran for every related record of EmployeeDetail, and I'm not sure why.

public virtual EmployeeDetail EmployeeDetail { get; set; }
DavidB
  • 2,566
  • 3
  • 33
  • 63
  • I just tried to show how to solve 1 + n [here](http://stackoverflow.com/q/34138989/1679310) – Radim Köhler Dec 08 '15 at 09:14
  • 1
    Well, that is not, what I would use. You have to explicitly keep to use fetching whenever you query something. And it sooner or later will reach limit (e.g. more collections). So, once you will play with that solution, please, keep in mind that there is a common, general and easy to use (just a mapping) solution I showed you. Good luck with NHibernate... – Radim Köhler Dec 08 '15 at 17:37

1 Answers1

1

You can use Fetch method to force a join sql statement to fill the property, for sample:

   return Employees()
           .Fetch(x => x.EmployeeDetail)
           .Where(e => e.ClientId == clientId && e.Deleted == false)
           .ToList();
Felipe Oriani
  • 37,948
  • 19
  • 131
  • 194