在NHibernate查询中根据不同的表设置值。

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.

我正在使用NHibernate，这个查询有问题……我有一个类项，我想用它的Id来获取它。但是,我也想要一个bool属性设置为true的条目类如果专门设置一些条件。此属性命名IsMarked,告诉如果项目被标记/盯着/标记为用户请求,这个信息是设置在一个表给项目和用户之间的关系。

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?

目前，我正在获取项，然后查找引用——如果可以找到引用，则将属性更新为true。我可以在一个查询中完成吗?

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;

1 个解决方案

#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:

和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.

只要ismark定义为bool，如果count(*)返回0，它将被转换为false，如果任何> 0，它将被转换为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);
    }
}

#1