Setting value based on different table in NHibernate query

Question

I am using NHibernate, and have problems with this query... I have a class Item which I want to fetch using its Id. All fine. However, I also want a bool property on the Item class to be set to true if some other condition is set. Specifically this property is named IsMarked, telling if the Item is marked/stared/flagged for the user that requested it, and this information is set on a table giving the relation between Item and User.

Currently I'm fetching the Item, and then finding the reference - updating the property to true if the reference could be found. Can I do this in one query instead?

var item = Session.Get<Item>(itemId);

var flaggedResult = Session.CreateCriteria<ItemWithUserFlag>()
    .Add(Restrictions.Eq("User.Id", userId))
    .Add(Restrictions.Eq("Item", item))
    .List<ItemWithUserFlag>();

if (flaggedResult.Count > 0)
    item.IsMarked = true; 

return item; 

Answer 1

How about using formula along with filter in your property mapping:

<property name="IsMarked" formula="(select count(*) from ItemWithUserFlag where ItemWithUserFlag.ItemId = ItemId and ItemWithUserFlag.UserId = :UserFilter.userId)" />

And filter def:

<filter-def name="UserFilter">
    <filter-param name="userId" type="Int32"/>
</filter-def>

That will result in something like

SELECT Item.*, (select count(*) from ItemWithUserFlag where ItemWithUserFlag.ItemId = Item.ItemId and ItemWithUserFlag.UserId = ?) AS IsMarked FROM Item

As long as IsMarked is defined as bool, if count(*) returns 0 it will be converted to false and if anything > 0 it will be converted to true.

EDIT: Fluent representation

public class ItemMap : ClassMap<Item>
{
    public ItemMap()
    {
        /// ... whatever
        Map(x => x.IsMarked).Formula("(select count(*) from ItemWithUserFlag where ItemWithUserFlag.ItemId = ItemId and ItemWithUserFlag.UserId = :UserFilter.userId)");
    }
}

public class UserFilter : FilterDefinition
{
    public UserFilter()
    {
        WithName("UserFilter")
            .AddParameter("userId", NHibernate.NHibernateUtil.Int32);
    }
}