Fluent NHibernate References with constants

Question

I have a table mapped in Fluent NHibernate. This table must join to another table on an ID, but must also filter the joined values on that table against a set of constant values. Consider the following SQL:

SELECT * 
FROM 
    Table1 
INNER JOIN 
    Table2 ON 
    Table1.Table2Id = Table2.Id 
    AND Table2.Category = 'A constant expression' 
    AND Table2.Language = 'A constant expression'

My fluent mapping for Table1 currently looks like this:

References(a => a.Table2).Nullable().Columns("Table2Id").ReadOnly();

How can I implement the constant expressions?

Answer 1

I have noticed that your mapping specifies Nullable and no eager fetching (by default it will be lazy loaded). So if you did want to generate the sql you have shown in your comment, you would not be able to do it with a simple session.Get<Table1>(). Even if you changed the mapping so that it was like this :

References(a => a.Table2).Nullable().Columns("Table2Id").ReadOnly().Fetch.Join;

You would most likely end up with a left outer join in the outputted sql. The only way you would be able to force a fetch with inner join (without downloading any extra NHibernate addons) would be to use a session.QueryOver (you may also be able to do this with session.Query and the NHibernate linq extensions). If this is the case, then you may as well specify your set of constants inside the QueryOver query.

public class GetTableQuery
{
    private readonly string _category;
    private readonly string _language;

    public GetTableQuery(string category, string language)
    {
        _category = category;
        _language = language;
    }

    public IEnumerable<Table1> Execute(ISession session)
    {
        var returnList = session.QueryOver<Table1>()
            .Inner.JoinQueryOver(t1 => t1.Table2)
            .Where(t2 => t2.Category == _category && t2.Language == _language)
            .List();

        return returnList;
    }
}

I think the ApplyFilter method shown by Russ does make the model retrieval much simpler, and its really good for code re-usability (if you have other tables with categories and languages), but since your table reference is a nullable reference, you have to use a query anyway. Maybe a combination of QueryOver with the filter would be the best (assuming you can get the later version of fluent NHibernate)