CODEWITHSUNDEEP
linqnhibernateleft-join
James
James

Reputation: 12806

nHibernate 3 - Left Join re-Linq solution

I am trying to to run this Linq query below with nHibernate 3. 

var items = from c in session.Query<tbla>()
       join t in session.Query<tblb>() on c.Id equals t.SomeId into t1 // use left join on trades.
       from t2 in t1.DefaultIfEmpty()
select new {item = c, desc = t2.Description};

This is the stock way to perform a left join in linq to my knowledge. However it's giving me an unsupported exception message. How can I achieve a basic left join without resorting back to HQL? This seems somewhat silly that an ORM as prevalent as nHibernate cannot support something as pedestrian as a left join.

[edit]

I've put the real answer to my own question below.

Upvotes: 11

Views: 7733

Answers (2)

James
James

Reputation: 12806

After further research on this; this is possible (although not obvious) to achieve in a strongly typed fashion using QueryOver. The trick is to use outer Query alias variables in conjunction with WithAlias, and TransformUsing. Here is an example that does left join with filtering and sorting.

// Query alias variables 
entityTypeA anchorType = null;
entityTypeB joinedType = null;

var items = session.Query<entityTypeA>( ()=>anchorType )
            .Left.JoinAlias(() => anchorType.FieldName, () => joinedType)
            .WithSubquery.WhereProperty(e => e.FieldD).In(myFilterList)
            // bind property mappings using WithAlias
            .SelectList(list => list
                        .Select(e => e.FieldNameA).WithAlias( ()=> anchorType.FieldNameA )
                        .Select(e => e.FieldNameB).WithAlias( ()=> anchorType.FieldNameB )
                        )
           .OrderBy(e => joinedType.FieldNameC).Desc
           .TransformUsing(Transformers.AliasToBean<entityTypeA>()) // transform result to desired type.
           .List<entityTypeA>();

Upvotes: 8

Diego Mijelshon
Diego Mijelshon

Reputation: 52753

It's not supported yet. HQL is your only choice at the moment.

Upvotes: 1

Related Questions