Fluent NHibernate Constant Links

Question

I have a table displayed in Fluent NHibernate. This table should join another identifier table, but it should also filter the combined values ​​in this table by 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 current mapping for Table1 currently looks like this:

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

How can I implement constant expressions?

Answer 1

I noticed that your mapping is Nullable and does not require distraction (by default it will be lazily loaded). Therefore, if you want to create the sql that you indicated in your comment, you cannot do this with a simple session.Get<Table1>() . Even if you change the display to look like this:

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

Most likely you will get a left outer join in the sql output. The only way to force a fetch with an inner join (without downloading any additional NHibernate add-ons) is to use session.QueryOver (you can also do this using session.Query and linq extensions for NHibernate). If so, you can also specify your own 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 makes finding a model a lot easier and it is really good for reusing code (if you have other tables with categories and languages), but since the table link is a link, you still must use the request. Perhaps a combination of QueryOver with a filter would be better (assuming you can get a later version of free NHibernate)

Answer 2

It looks like you could use filters for this.

First you need to define 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: according to the comments there is no .ApplyFilter<TFilter>() on References() , therefore updated so that, in my opinion, the way to do this is with filters

Filters should be applied in smooth displays.

 public class Table2Map : ClassMap<Table2> { public Table2Map() { // Other mappings here... ApplyFilter<SpecificCategoryFilter>(); ApplyFilter<SpecificLanguageFilter>(); } } 

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

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

If you use the ICurrentSessionContext implementation, and filters should always be applied, you can enable filters in the session returned from the call to ICurrentSessionContext.CurrentSession() .

Now when you request Table1 to activate filters for Table2 , you need to specify NHibernate to join the reference Table2 ; you can do it using

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

Without NHibernate instructing to create a connection, the link will load by default and therefore filters will not be applied in the request. The downside is that Table2 will be impatient, but I don't know how to use filters otherwise. Next request

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

results in SQL similar

 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 you have in your question.

Answer 3

Perhaps you should take a look at Formula(string formula) , where you can provide simple SQL. If it is a good idea to filter data at the matching level, this is another IMHO question ... Look here for an example.