NHibernate Left Outer Join

criterialeft-joinnhibernate

I'm looking to create a Left outer join Nhibernate query with multiple on statements akin to this:

SELECT 
    * 
FROM [Database].[dbo].[Posts] p
LEFT JOIN 
    [Database].[dbo].[PostInteractions] i
ON 
   p.PostId = i.PostID_TargetPost And i.UserID_ActingUser = 202

I've been fooling around with the critera and aliases, but I haven't had any luck figuring out how do to this. Any suggestions?

Best Answer

I actually figured it out. You need to do a check for null

.CreateCriteria("Interactions", "i", NHibernate.SqlCommand.JoinType.LeftOuterJoin)
            .Add(Expression.Or(Expression.Eq("i.ActingUser", user), Expression.IsNull("i.ActingUser")))

this creates a left join on targetpost id and then eliminates all non null/non user interactions.

Related Solutions

Sql-server – LEFT JOIN vs. LEFT OUTER JOIN in SQL Server

As per the documentation: FROM (Transact-SQL):

<join_type> ::= 
    [ { INNER | { { LEFT | RIGHT | FULL } [ OUTER ] } } [ <join_hint> ] ]
    JOIN

The keyword OUTER is marked as optional (enclosed in square brackets). In this specific case, whether you specify OUTER or not makes no difference. Note that while the other elements of the join clause is also marked as optional, leaving them out will make a difference.

For instance, the entire type-part of the JOIN clause is optional, in which case the default is INNER if you just specify JOIN. In other words, this is legal:

SELECT *
FROM A JOIN B ON A.X = B.Y

Here's a list of equivalent syntaxes:

A LEFT JOIN B            A LEFT OUTER JOIN B
A RIGHT JOIN B           A RIGHT OUTER JOIN B
A FULL JOIN B            A FULL OUTER JOIN B
A INNER JOIN B           A JOIN B

Also take a look at the answer I left on this other SO question: SQL left join vs multiple tables on FROM line?.

enter image description here

Nhibernate: left outer join on subquery

I'm not sure I entirly follow your issue but it sounds very similar to an issue I had. I seems that when you have the nested (sub) criteira with NHibernate you loose some of the control. I was able to get around my issue but aliasing my tables instead of using the nested criteira.

Possibly try...

criteria.CreateAlias("Cart", "Cart", JoinType.LeftOuterJoin);

and then your filter on the cart will have to be an OR condition

ICriterion cartCriterion = Restrictions.Eq("Cart.User", username);
customerCriterion = Restrictions.Or(customerCriterion, Restrictions.IsNull("Cart.User"));
criteria.Add(customerCriterion);

Let me know if that helps you out... if not... maybe post the SQL that your criteria above is generating and where it needs to change.

Good Luck

Best Answer

Related Solutions

Sql-server – LEFT JOIN vs. LEFT OUTER JOIN in SQL Server

Nhibernate: left outer join on subquery

Related Topic