Newbie to NHibernate...hopefully someone can help me out here?

I'm trying to generate some results using the criteria API instead of HQL.

Basically, I'm trying to generate this SQL:
SELECT t1.something, t1.something2
FROM Table1 t1 LEFT OUTER JOIN Table2 t2 ON t1.SomeID = t2.SomeID
WHERE t2.something IS NULL

Using this...

IList results = null;

ISession session = factory.OpenSession();
results = session.CreateCriteria(typeof(Contact))
.SetFetchMode("ContactDetails", FetchMode.Join)
.CreateCriteria("ContactDetails", "CD")
.Add(new NullExpression("ContactDetailName"))
.SetResultTransformer(CriteriaUtil.AliasToEntityMap)
.List();

It produces the query I want, but with an INNER JOIN instead of a LEFT JOIN (which does not help me). I've tried a couple of different things but with no success. Help?

Try SetFetchMode("CD", FetchMode.Join) - you have to use the same alias that you use in CreateCriteria.

Hi Sergey,

Thanks for the reply, but unfortunately it still doesn't work using the alias. I also tried using the overload of CreateCriteria that doesn't set an alias, and using the full association path in SetFetchMode, and that didn't work either - any other ideas?

Thanks

Hey there,

Bellow are my comments on your problem:

1. My opinion is that there is now way you can apply fetch join to an association that you also intend to filter, in your case:

.SetFetchMode("ContactDetails", FetchMode.Join)

and

.CreateCriteria("ContactDetails", "CD")
.Add(new NullExpression("ContactDetailName"))

(although I maybe mistaken on this one, but I found this by trial and error)

2. With criteria there is now way you can generate left join's

3. You can do it either via HQL or qith a custom SQL expression in criteria:

session.CreateCriteria(typeof(Contact))
.SetFetchMode("ContactDetails", FetchMode.Join)
.Add( Expression.Sql("(select count(details.id) from details where details.contactid = {alias}.contactid) = 0 ") )

Thanks Dragos.

I got it to work, not exactly as you have shown but along the same lines...

I will have to admit that I'm a bit disappointed that I had to use a SQL expression to accomplish what I was trying to do, as my original intent was to abstract away the db details. With this solution, intimate knowledge of the db is still required. Hum. Hopefully as I get into NHibernate more...other solutions will present themselves to me.

CC

You're right and wrong at the same time.

First of all this could have been done in HQL (I like it better than criteria).

Second, with a little bit of work you can still abstract the database. Use the NH configuration and the factory objects you can get all the needed info for building the SQL (tables, dialect) and you can wrap the needed logic for some widely used types of sql expressions in classes that inherit from SQLExpression or Expression or create a SQLExpression factory :).

When there's a will, there's a way...