Search code examples
c#nhibernatefluent-nhibernatefluent

Full Outer Join NHibernate


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

Solution

  • 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; }
    }