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 ?