nhibernatefluent-nhibernatenhibernate-mappingfilteringnhibernate-criteria

Fluent NHibernate and filtering one-to-many relationship on query requiring multiple joins?


I recently got started with NHibernate and am having some trouble implementing the domain model outlined further down.

What I'm looking for is a way to filter the relationship between an Item and it's ItemData collection on specific DataStores. DataStores are either global, in which case they are always returned, or specific to a user identity (based on application instance).

In SQL this can be accomplished using a simple query:

SELECT * FROM Items i
INNER JOIN ItemData id ON (i.ItemId=id.ItemId)
LEFT OUTER JOIN Users u ON (id.UserId=u.UserId)
LEFT OUTER JOIN DataStore ds ON (id.DataStoreId=ds.DataStoreId)
WHERE ds.IsGlobal = 1 OR ds.UserId = @userId

Database structure:

DataStore:
- DataStoreId (PK)
- Name
- Weight
- UserId
- IsGlobal

Item:
- ItemId (PK)
- ... (non-nullable fields)

ItemData:
- ItemDataId (PK)
- ItemId
- DataStoreId
- ... (nullable fields)

Domain model:

public class ItemMap : ClassMap<Item>
{
    public ItemMap()
    {
        Id(x => x.Id, "ItemId");
        HasMany(x => x.Data)
            .KeyColumn("ItemId")
            .ApplyFilter<ItemDataFilter>(..?)
            .Cascade.AllDeleteOrphan();
    }
}

The basic theory is to fetch one ItemData row per DataStore and join each column on the weight field of the respective DataStore (first non-null value ordered by weight).

Insight as to if and how this could be accomplished in NHibernate would be much appreciated.


Solution

  • Heres what I've found myself in case anyone else is looking for this information.

    1.Create a custom filter:

    public class ItemDataFilter : FilterDefinition
    {
        public ItemDataFilter()
        {
            WithName("ItemDataFilter").WithCondition("Data.DataStoreId == :DataStoreId").AddParameter("DataStoreId", NHibernate.NHibernateUtil.Int32);
        }
    }
    

    2.Modify your Fluent NHibernate property mapping (with .ApplyFilter<>()):

    HasMany(x => x.Data)
        .KeyColumn("ItemId")
        .ApplyFilter<ItemDataFilter>()
        .Cascade.AllDeleteOrphan();
    

    3.In your repository enable the filter and set it's property for the current session:

    public IList<Item> GetItemsByDataStore(int DataStoreId)
        {
        using (var session = NHibernateHelper.OpenSession())
        {
            session.EnableFilter("ItemDataFilter").SetParameter("DataStoreId", DataStoreId);
            return session.CreateCriteria(typeof(Item)).List<Item>();
        }
    }
    

    Another way to do this would be to fetch all ItemData for each Item and add another non-mapped property that does this filtering.