I am trying to do a full outer join in fluent nhibernate, but I can't seem to figure out the correct syntax to get this method to work.
I have tried join alias as well as projections, but neither seem to be working.
For example, if I have two objects (A and B), I need to get all of object A, and all of Object B, but they might not always have a full relation. Object A has a relation to object B, but I might have object B that is not related to object A at all, and I might have objects A that don't have any of object B.
public class objectA{
public virtual int? ID {get; set; }
public virtual string someData {get; set;}
public virtual ObjectB objB {get; set;}
}
public class objectB{
public virtual int ID_B {get; set;}
public virtual bool boolVale {get; get;}
public virtual int? someInt {get; set;}
}
My mappings for them would look like this:
public objectA_Map(){
Table("objectA_Table");
Id(x => x.Id).Column("ID");
Map(x=> x.someData).Column("someData");
References(x => x.objB).Column("fkToB");
}
public objectB_Map(){
Table("objectB_Table");
Id (x => x.ID_B).Column("ID");
Map(x =>x.boolValue).Column("Deleted");
Map(x => x.someInt).Column("Number");
}
Again, I need each row of the resulting query to contain my A_ID, someData, ID_B, boolValue, for all objects, even if B doesn't have an A and all A's even if there is no B.
Any insight would be much help.
The SQL I am trying to generate should look something like this:
SELECT A.ID, A.someData, B.ID AS BID, B.DELETED, B.Number
FROM objectA_Table A
FULL OUTER JOIN objectB_Table B ON A.ID = B.ID
You probably won't be able to select out full entities like this, but you could build a list of DTOs from your query using QueryOver:
IList<DTO> result = session.QueryOver<A>(() => aAlias)
.Full.JoinQueryOver(p => p.B, () => bAlias)
.SelectList(list => list
.Select(() => aAlias.Id).WithAlias(() => resultAlias.AId)
.Select(() => aAlias.SomeData).WithAlias(() => resultAlias.SomeData)
.Select(() => bAlias.Id).WithAlias(() => resultAlias.BId)
.Select(() => bAlias.Deleted).WithAlias(() => resultAlias.Deleted)
.Select(() => bAlias.Number).WithAlias(() => resultAlias.Number))
.TransformUsing(Transformers.AliasToBean<DTO>())
.List<DTO>();
This will generate the following SQL:
SELECT
this_.Id as y0_,
this_.SomeData as y1_,
bAlia1_.Id as y2_,
bAlia1_.Deleted as y3_,
bAlia1_.Number as y4_
FROM
[A] this_
full outer join [B] bAlia1_ on this_.Id = bAlia1_.aId
With DTO looking like this:
public class DTO
{
public Guid AId { get; set; }
public string SomeData { get; set; }
public Guid BId { get; set; }
public bool Deleted { get; set; }
public int Number { get; set; }
}