Linq Nhibernate left join

Question

A Theft has an action property

This is the query i'm trying to get NHibernate.Linq to produce:

SELECT * FROM `thefts`
LEFT JOIN memberThefts
ON thefts.id = memberThefts.theftId AND memberThefts.memberId = 1

I want to get a list of all the thefts where action.memberId == some number or just null if it doesn't find a row, simple as a query yet it's been giving me a nightmare all day!

        thefts = session.Query<Theft>()
            .Fetch(x => x.action)
            .Where(x => x.action.memberId == member.id)
            .ToList();

This executes the following SQL:

select theft0_.id                 as id9_0_,
       memberthef1_.memberId      as memberId7_1_,
       theft0_.name               as name9_0_,
       theft0_.chance             as chance9_0_,
       memberthef1_.theftId       as theftId7_1_,
       memberthef1_.availableTime as availabl3_7_1_
from   thefts theft0_
       left outer join memberThefts memberthef1_
         on theft0_.id = memberthef1_.theftId,
       memberThefts memberthef2_
where  theft0_.id = memberthef2_.theftId
       and memberthef2_.memberId =1 /* ?p0 */

The theft class:

    public class Theft
    {
        public virtual byte id { get; set; }
        public virtual string name { get; set; }
        public virtual byte rank { get; set; }
        public virtual byte chance { get; set; }
        public virtual MemberTheft action { get; set; }
...

And it's mapping:

public TheftMap()
{
    Table("thefts");
    Id(x => x.id);
    Map(x => x.name);
    Map(x => x.id);
    Map(x => x.chance);
    References(x => x.action)
        .Nullable()
        .PropertyRef(x => x.theftId)
        .Column("id");
}

Any solution will do HQL, QueryOver etc

Phill · Accepted Answer · 2013-03-23T18:09:32.927

5

It can't be done using the LINQ provider, but you can do it with QueryOver. Something along the lines of:

MemberTheft memberAlias = null;
var result = Session.QueryOver<Theft>()
                    .Left.JoinQueryOver(x => x.action, () => memberAlias)
                    .Where(() => memberAlias.memberId == member.id);

Edit: Updated Query.

edited Mar 23 '13 at 18:09

answered Mar 23 '13 at 17:51

Phill

18,398
7
62
102

I'm getting: The best overloaded method match for 'NHibernate.IQueryOver.Where(System.Linq.Expressions.Expression>)' has some invalid arguments – Juddling Mar 23 '13 at 18:00
@Juddling I just updated the query, tested it and it's working. If it still doesn't work can you please update your question with your mappings/models so I can use that. – Phill Mar 23 '13 at 18:10
Ahh, I think* you still need to do `.Fetch(x => x.action).Eager`, I would need to setup a bunch of test data, don't have time, its 2-30am need to go sleep. – Phill Mar 23 '13 at 18:29

Linq Nhibernate left join

1 Answers1

Linked