Fluent NHibernate and one-to-many filtering in a query requiring multiple connections?

Question

I recently started working with NHibernate, and I had problems implementing the domain model described below.

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

In SQL, this can be done with 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 extract one row of ItemData on a DataStore and combine each column in the weight field of the corresponding DataStore (the first non-zero value ordered by weight).

, NHibernate, .

Answer 1

, - .

1. :

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

2. Fluent NHibernate ( .ApplyFilter < > ()):

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

3. :

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

- ItemData , .

Answer 2

, HQL, . HQL :

 Session.CreateQuery(hqlQuery).List();

hqlQuery :

 var hqlQuery= string.Format("select i from Items as i inner join i.ItemData left join Users u left join DataStire ds where u.UserId=i.UserId and ds.DataStoreId=i.DataStoreId and (ds.IsGlobal=1 or ds.UserId='{0}')",userId);

 Session.CreateQuery(hqlQuery).List<Item>();

, .