4

The two core entities in the model are:

  • Account
  • Registration

Here is the fluentNH mapping between Account and Registration:

.Override<Account>(m =>
 {
   m.References(x =>
     x.Registration).Cascade.All().Not.Nullable().Unique(); 
     // unidirectional one-to-one association, unique will not allow multiple nulls
 }

We have a query to find the top 10 (accountid, registrationid) that match a certain filter criteria (expression on account) and are ordered by two fields on the registration

Example:

 public class AccountRegistrationId
{
  public Guid AccountId { get; set; }
  public Guid RegistrationId { get; set; }
}

THE QUERY:

The query:

(from r in _session.Query<Account>() select r)
.OrderBy(a => a.Registration.PatientVisit)
.OrderBy(a => a.Registration.AccountNumber)
.Where(a=>a.Registration.Mrn == "Mrn 1234")
.Select(a =>  new AccountRegistrationId{     AccountId = a.Id,
                                                                RegistrationId = a.Registration.Id
                                                          }
).ToArray();

the resulting sql has 4 outer joins between account and registration:

select   TOP ( 10 /* @p0 */ ) account0_.Id    as col_0_0_,
                     registrati3_.Id as col_1_0_
from     [Account] account0_
         left outer join [Registration] registrati1_
           on account0_.Registration_id = registrati1_.Id
         left outer join [Registration] registrati2_
           on account0_.Registration_id = registrati2_.Id
         left outer join [Registration] registrati3_
           on account0_.Registration_id = registrati3_.Id,
         [Registration] registrati4_

The problem: multiple left outer joining with Registration table

  • One join for the projection
  • One join for the Where Criteria
  • Two joins for OrderBy criteria (one for each order by)

Is there anyway to get rid of these multiple joins on registration ?

vikram nayak
  • 591
  • 5
  • 14

0 Answers0