How to create Detachedcriteria criteria for a field from a Join table?

Question

First time I move with this and I’m having trouble fitting the query properly.

Here’s my code done the wrong way, so you’re not obeying the conditions:

public IList<Ficha> ObterFichas()
{        
    DetachedCriteria criteria = DetachedCriteria.For<Ficha>();
    criteria.SetMaxResults(1);

    DetachedCriteria criteriaExportacao = DetachedCriteria.For<Exportacao>();       
    criteriaExportacao.CreateAlias("exportacao","e", NHibernate.SqlCommand.JoinType.LeftOuterJoin).Add(Restrictions.Eq("exportacao.Exportado", null)).Add(Property.ForName("e.Exportado").In(criteria));

    var ok = DomainService.FichaRepository().LoadAll(criteria);

    return ok;
}

The first record is returned, but the first record does not have the field Exported null.

I have the table record sheet and the table export in my bank. It is a relationship mapped from 1 to 1. In the table record sheet and in the table export i have Idficha as PK and PK/FK respectively.

Given this information what I want to do is mount a query that brings only 1 result line with left Join between table record sheet and table export. Which should only bring the null results as I tried to do in the condition of my code, but which is wrong.

Answer 1

Got it, got it that way:

public IList<Ficha> ObterFichas()
{        
    DetachedCriteria criteria = DetachedCriteria.For<Ficha>();
    criteria.SetMaxResults(1);

    criteria.CreateAlias("exportacao", "e",NHibernate.SqlCommand.JoinType.LeftOuterJoin).Add(Restrictions.IsNull("e.Exportado"));

    var ok = DomainService.FichaRepository().LoadAll(criteria);

    return ok;
}