nhibernate join criteria help

Question

I have a table DriverScans that joins DriverImages. I want to return all DriverScans where any DriverImage has it's Sent field equal to false.

Essentially

SELECT DriverScan.* 
FROM DriverScan
INNER JOIN DriverImages
ON DriverImages.DriverScanId = DriverScan.DriverScanId
WHERE DriverImages.Sent = 0

The code below only DriverScans but the SQL query created pulls back an Inner Join of DriverScan and DriverImages, which includes an image field. How do I write the code so SQL only returns DriverScan info?

public IEnumerable<DriverScan> GetNewScans()
{
    var session = GetCleanSession();
    var query = session.CreateCriteria(typeof(DriverScan));

    query.CreateCriteria("DriverImages", JoinType.InnerJoin)
        .Add(Cr.Restrictions.Eq("Sent", false));

    return query.List<DriverScan>();
}

If relevant my mapping for DriverImages is

HasMany<DriverDoc>(x => x.DriverDocs)
   .WithKeyColumn("DriverScanId").IsInverse()
   .Cascade.AllDeleteOrphan().LazyLoad();

Answer 1

Do you have to use a CreateCriteria? You can do this pretty easily with HQL. Something along these lines should do it for you.

SELECT d
FROM DriverScan d
JOIN d.DriverImages i
WHERE i.Sent = 0

Answer 2

Not tested and I don't know how you are mapping DriverScan to DriverImage but I think you want something like this:

var sentImagesQuery = DetachedCriteria.For<DriverImages>()
    .Add(Expression.Eq("Sent",false))
    .SetProjection(Projections.Property("DriverScan.Id"));

var results = session.CreateCriteria<DriverScan>()
    .Add(Subqueries.PropertyIn("Id",sentImagesQuery))
    .List<DriverScan>();

Should produce:

select *
from DriverScan d
where d.Id in (select DriverScanId from DriverImages where sent=0)

Now you don't load the image column which would slow down your result set. Optionally in NH3 you could look at "Lazy Properties" which would lazy load the Image property until needed.