NHibernate 4 Left Outer Join with one single query on database

Question

I am new to NHibernate and I am using the v4.0.30319 which doesn't even seem to appear in the official website (I don't understand why?) so I'm having troubles finding good documentation.

What I want is in theory simple. I have 3 tables:

• Client
• ClientAnswer
• Question

So in table Questions I have questions, in Clients I have Clients and in the join table ClientAnswer I have a Client's answer to each question (if any). It could be that a Client has not responded yet a Question so it wouldn't have a row in ClientAnswer.

I'm using FluentNHibernate for mapping and it's as follows:

public ClientMap()
    {
        Id(x => x.Id);
        Map(x => x.Name).Not.Nullable();
        Map(x => x.Notes).Nullable();
        Map(x => x.IsDeleted).Not.Nullable();
    }


public QuestionMap()
    {
        Id(x => x.Id);
        Map(x => x.Description).Not.Nullable();
        Map(x => x.IsDeleted).Not.Nullable();
    }

public ClientAnswerMap()
    {
        Id(x => x.Id);
        Map(x => x.Value).Not.Nullable();
        Map(x => x.IsDeleted).Not.Nullable();

        References<Client>(x => x.Client, "ClientId");
        References<Driver>(x => x.Question, "QuestionId");
    }

I would like to implement a function that returns a list of CustomThing, so each CustomThing would contain a question and that specific client's answer (if any, null otherwise).

IEnumerable<CustomThing> GetQuestionsAndAnswers(int clientId)

being my custom object as simple as

public class CustomThing
{
    public Question Question { get; set; }
    public ClientAnswer ClientAnswer { get; set; } //it could be null
}

In SQL I could do something like:

select * from Question q left outer join ClientAnswer ca on ca.QuestionId = q.Id

and then somehow filter out the ClientAnswers whose ClientId is not the client I'm looking for and put the results in the enumerable of CustomThing. (I hope I explained properly)

I am open to any suggestion, I'm not good with SQL :)

Thank you,

EDITED: I have at the moment this. But it looks very inefficient and has multiple accesses to the database. But in case it shows the idea of what I would like to have:

public IEnumerable<CustomThing> GetQuestionsAndAnswers(int clientId)
    {
        IList<Question> allQuestions = _session.QueryOver<Question>()
            .WhereNot(x => x.IsDeleted).List();

        IList<CustomThing> customThings= new List<CustomThing>();

        foreach (Question q in allQuestions)
        {
            CustomThing customThing= new CustomThing();
            customThing.Question = q;
            customThing.ClientAnswer= GetClientAnswer(clientId, q.Id);

            customThings.Add(customThing);
        }

        return customThings;
    }

    private ClientAnswer GetClientAnswer(int clientId, int questionId)
    {
        var clientAnswer = _session.QueryOver<ClientAnswer>()
            .Where(x => x.Client.Id == clientId)
            .Where(x => x.Question.Id == questionId)
            .WhereNot(x => x.IsDeleted).SingleOrDefault();

        return clientAnswer; //can be null if the client did not answer this question
    }

Answer 1

If you add a Bag to your Question class linking back to your ClientAnwsers class you can left join ClientAnwsers in your allQuestions Query, like you would in your SQL query above.

As i don't really use FluentNHibernate i can't show you exactly how. But maybe these three links help:

NHibernate Mapping - Set

Fluent mapping - HasMany / one-to-many

NOtherDev: Mapping-by-Code - Set and Bag