Setting a value based on another table in an NHibernate query

Question

I am using NHibernate and have problems with this request ... I have a class element that I want to get using my identifier. Everything is fine. However, I also want the bool property in the Item class to be set to true if another condition is specified. In particular, this property is called IsMarked, saying that the item is marked / saved / marked for the user who requested it, and this information is specified in the table that gives the relationship between Item and User.

I am currently retrieving an Item and then finding a link - updating the property to true if the link can be found. Can I do this in one request?

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

formula filter :

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

def:

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

-

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

IsMarked bool, count(*) 0, false, - > 0 true.

EDIT:

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);
    }
}