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

It sounds like you could use filters to do this.

First, you need to define the filter types

public class SpecificCategoryFilter : FilterDefinition
{
    public SpecificCategoryFilter()
    {
        WithName("SpecificCategory").WithCondition("Category = 'A constant expression'");
    }
}

public class SpecificLanguageFilter : FilterDefinition
{
    public SpecificLanguageFilter()
    {
        WithName("SpecificLanguage").WithCondition("Language = 'A constant expression'");
    }
}

EDITED: As per comments, there is no .ApplyFilter<TFilter>() on References(), so have updated with what I believe is the way to do it with filters

The filters need to be applied in the fluent mappings

public class Table2Map : ClassMap<Table2>
{
    public Table2Map()
    {
        // Other mappings here...

        ApplyFilter<SpecificCategoryFilter>();
        ApplyFilter<SpecificLanguageFilter>();
    }
}

Finally, when you open a session, you need to enable the filters

using (var session = sessionFactory.OpenSession())
{
    session.EnableFilter("SpecificCategory");
    session.EnableFilter("SpecificLanguage");
}

If you're using an implementation of ICurrentSessionContext and the filters should always apply then you can enable the filters in the session returned from the call to ICurrentSessionContext.CurrentSession().

Now, when querying Table1, in order to activate the filters for Table2, you need to indicate to NHibernate to join to the referenced Table2; you can do this using

1. Fetch(t => t.Table2).Eager
2. JoinQueryOver(t => t.Table2) (and similar join strategies)

Without indicating to NHibernate to make the join, the reference will be lazily-loaded by default and hence the filters will not be applied in the query. The downside is that Table2 will be eager fetched but I don't know of a way to have the filters applied otherwise. The following query

session.QueryOver<Table1>().Inner.JoinQueryOver(t => t.Table2).List();

results in SQL similar to

SELECT
    this_.Id as Id0_1_,
    this_.Table2Id as Table3_0_1_,
    table2_.Id as Id1_0_,
    table2_.Category as Category1_0_,
    table2_.Language as Language1_0_ 
FROM
    Table1 this_ 
inner join
    Table2 table2_ 
        on this_.Table2Id=table2_.Id 
WHERE
    table2_.Category = 'A constant expression' 
    and table2_.Language = 'A constant expression'

which is akin to the SQL that you have in your question.

Answer 2

You might want have a look into Formula(string formula) where you could provide plain SQL. If it is a good idea to filter data on the mapping level is another question IMHO... As an example have a look here.

Answer 3

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)