Hello,
following sample was tested on NH 4.0.4.4000, 5.1.2, 5.1.3.
I have following data model: Person can have one CurrentSpouse and this
current spouse can have a car. Data model is only for demonstration.
Person
public class BDOPerson
{
public virtual long? OID { get; set; }
public virtual string FirstName { get; set; }
public virtual string SureName { get; set; }
public virtual BDOCurrentSpouse CurrentSpouse { get; set; }
}
public class PersonMap : ClassMap<BDOPerson>
{
public PersonMap()
{
Table("T0001_Person");
Id(x => x.OID).Column("T0001OID").GeneratedBy.Increment();
Map(x => x.FirstName).Not.Nullable().Column("T0001Firstname");
Map(x => x.SureName).Not.Nullable().Column("T0001Surename");
References(x => x.CurrentSpouse).Nullable().Column(
"T0002_CurrentSpouseOID").Unique(); //HasOne opossite
}
}
CurrentSpouse
public class BDOCurrentSpouse
{
public virtual long? OID { get; set; }
public virtual string FirstName { get; set; }
public virtual string SureName { get; set; }
public virtual BDOPerson Person { get; set; }
public virtual BDOCar Car { get; set; }
}
public class CurrentSpouseMap : ClassMap<BDOCurrentSpouse>
{
public CurrentSpouseMap()
{
Table("T0002_CurrentSpouse");
Id(x => x.OID).Column("T0002OID").GeneratedBy.Increment();
Map(x => x.FirstName).Not.Nullable().Column("T0002Firstname");
Map(x => x.SureName).Not.Nullable().Column("T0002Surename");
HasOne(x => x.Person).PropertyRef(x => x.CurrentSpouse);
References(x => x.Car).Nullable().Column("T0003_CarOID");
}
}
Car
public class BDOCar
{
public virtual long? OID { get; set; }
public virtual string PlateId { get; set; }
}
public class CarMap : ClassMap<BDOCar>
{
public CarMap()
{
Table("T0003_Car");
Id(x => x.OID).Column("T0003OID").GeneratedBy.Increment();
Map(x => x.PlateId).Not.Nullable().Column("T0003PlateId");
}
}
Data fill:
using (var session = _sessionFactory.OpenSession())
{
var isolationLevel = IsolationLevel.ReadCommitted;
using (var transaction = session.BeginTransaction(isolationLevel))
{
var currentSpouse = new BDOCurrentSpouse { FirstName = "Ema",
SureName = "NiceLooking" };
var person = new BDOPerson { FirstName = "John", SureName =
"Doe", CurrentSpouse = currentSpouse};
var car = new BDOCar { PlateId = "1234567" };
session.Save(car);
car = new BDOCar { PlateId = "336699" };
session.Save(car);
currentSpouse.Car = car;
session.Save(currentSpouse);
session.Save(person);
transaction.Commit();
}
}
Selecting data - Issue 1
using (var session = _sessionFactory.OpenSession())
{
var isolationLevel = IsolationLevel.ReadCommitted;
using (var transaction = session.BeginTransaction(isolationLevel))
{
var v = session.Query<BDOPerson>().Fetch(x => x.CurrentSpouse).
ToList();
transaction.Commit();
}
}
Resulting queries of select
select bdoperson0_.T0001OID as t1_2_0_, bdocurrent1_.T0002OID as
t1_1_1_, bdoperson0_.T0001Firstname as t2_2_0_,
bdoperson0_.T0001Surename as t3_2_0_, bdoperson0_.T0002_CurrentSpouseOID
as t4_2_0_, bdocurrent1_.T0002Firstname as t2_1_1_,
bdocurrent1_.T0002Surename as t3_1_1_, bdocurrent1_.T0003_CarOID as
t4_1_1_ from T0001_Person bdoperson0_ left outer join
T0002_CurrentSpouse bdocurrent1_ on
bdoperson0_.T0002_CurrentSpouseOID=bdocurrent1_.T0002OID
SELECT bdoperson0_.T0001OID as t1_2_0_, bdoperson0_.T0001Firstname as
t2_2_0_, bdoperson0_.T0001Surename as t3_2_0_,
bdoperson0_.T0002_CurrentSpouseOID as t4_2_0_ FROM T0001_Person
bdoperson0_ WHERE bdoperson0_.T0002_CurrentSpouseOID=?
What is the reason for getting second select? This second select creates
N+1 issue - for each row from DB as result of first select, special second
select is done.
For testing purposes I tried to Fetch Person
using (var session = _sessionFactory.OpenSession())
{
var isolationLevel = IsolationLevel.ReadCommitted;
using (var transaction = session.BeginTransaction(isolationLevel))
{
var v = session.Query<BDOPerson>().Fetch(x => x.CurrentSpouse).
ThenFetch(x => x.Person).ToList();
transaction.Commit();
}
}
But this did not solved an issue:
select bdoperson0_.T0001OID as t1_2_0_, bdocurrent1_.T0002OID as
t1_1_1_, bdoperson2_.T0001OID as t1_2_2_, bdoperson0_.T0001Firstname as
t2_2_0_, bdoperson0_.T0001Surename as t3_2_0_,
bdoperson0_.T0002_CurrentSpouseOID as t4_2_0_,
bdocurrent1_.T0002Firstname as t2_1_1_, bdocurrent1_.T0002Surename as
t3_1_1_, bdocurrent1_.T0003_CarOID as t4_1_1_,
bdoperson2_.T0001Firstname as t2_2_2_, bdoperson2_.T0001Surename as
t3_2_2_, bdoperson2_.T0002_CurrentSpouseOID as t4_2_2_ from T0001_Person
bdoperson0_ left outer join T0002_CurrentSpouse bdocurrent1_ on
bdoperson0_.T0002_CurrentSpouseOID=bdocurrent1_.T0002OID left outer join
T0001_Person bdoperson2_ on
bdocurrent1_.T0002OID=bdoperson2_.T0002_CurrentSpouseOID
SELECT
bdoperson0_.T0001OID as t1_2_0_, bdoperson0_.T0001Firstname as t2_2_0_,
bdoperson0_.T0001Surename as t3_2_0_, bdoperson0_.T0002_CurrentSpouseOID
as t4_2_0_ FROM T0001_Person bdoperson0_ WHERE
bdoperson0_.T0002_CurrentSpouseOID=?
What should I do to prevent extra select for an object which is set as
HasOne in mapping?
Please note that I need PropertyRef in real application and OIDs in tables
in HasOne relation may not be same.
Thanks
--
You received this message because you are subscribed to the Google Groups
"nhusers" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/nhusers.
For more options, visit https://groups.google.com/d/optout.
