NHibernate -使用Junction/Joiner表的多对多查询

实现这一目标的一种方法是创建尽可能多的由AND连接的子查询，因为必须找到与搜索文件相关的许多属性

查找名称/值

第一个解决方案使用来自上层的名称/值对。即用户选择模式为只读…(第二个比较简单，因为我们已经有了搜索属性的ID)

// Below I am using C# properties, which I guess are correct
// based on the mapping. Naming convention is more Java (camel)
// but this should work with above mapping 
// (also - class name Contact, not File)
Files file = null; // this is an alias used below
// here the attributes collection represents search filter
// ... settings for which is user looking for
var attributes = new List<Attrs>
{
    new Attrs{ name = "mode", value = "read-only" },
    new Attrs{ name = "view", value = "visible" }
};
// Let's start with definition of the outer/top query
// which will return all files, which do meet all filter requirements
var query = session.QueryOver<Files>(() => file);

// here we will take each attribute and create a subquery
// all these subqueries, will be joined with AND
// so only these files, which do have all attributes, will be selected
foreach (var attr in attributes)
{
    // create the subquery, returning the FileId
    Attrs attribute = null;
    var subQueryForAttribute = QueryOver.Of<Files_Attrs>()
            .JoinQueryOver(fa => fa.attr, () => attribute)
            .Select(x => x.file.id)
            ;
    // now, take name and value
    var name = attr.name;
    var value = attr.value;
    // and convert them into where condition
    subQueryForAttribute.Where(() => attribute.name == name);
    subQueryForAttribute.Where(() => attribute.value == value);
    // finally, add this subquery as a restriction to the top level query
    query.WithSubquery
        .WhereProperty(() => file.id)
        .In(subQueryForAttribute);
}

现在我们有了一个查询，它已经准备好支持分页了——因为我们正在处理一个平面的文件结构。因此，如果需要的话，我们可以使用Take和skip，然后获得搜索文件列表

// query.Take(25);
// query.Skip(100);
var list = query.List<Files>();

这是一个查询，将导致如下的SELECT

SELECT ...
FROM files
WHERE id IN (SELECT file_Id FROM files_attrs 
                              INNER JOIN attrs ON attrs.id = file_attrs.attr_id
                            WHERE name = 'mode' AND value = 'read-only' )
  AND id IN (SELECT file_Id FROM files_attrs 
                              INNER JOIN attrs ON attrs.id = file_attrs.attr_id
                            WHERE name = 'view' AND value = 'visible' )

通过属性ID

第二个解决方案，有更简单的开始条件，而不是属性(名称和值)，我们已经有了它们的id(引用自一个问题:)

给定一组属性id，我希望运行一个查询，为我提供所有这些匹配属性的文件。

// Below I am using C# properties, which I guess are correct
// based on the mapping. Naming convention is more Java (camel)
// but this should work with above mapping 
// (also - class name Files, not File)
Files file = null; // this is an alias used below
// here the attributeIds collection represents attributes to be found
var attributeIds = new List<int> { 1, 4, 5 };
// Let's again start with definition of the outer/top query
// which will return all files, which do meet all filter requirements
var query = session.QueryOver<Files>(() => file);

下一步是迭代已知id集合，这些id必须作为关系存在(全部)

// here we will take each attribute and create a subquery
// all these subqueries, will be joined with AND
// so only these files, which do have all attributes, will be selected
foreach (var attrId in attributeIds)
{
    // create the subquery, returning the Files.id
    var subQueryForAttribute = QueryOver.Of<Files_Attrs>()
            // no need to join, all the stuff is in the pairing table
            .Select(x => x.file.id)
            ;
    var id = attrId; // local variable
    // and convert them into where condition
    subQueryForAttribute.Where(pair => pair.attr.id == id);
    // finally, add this subquery as a restriction to the top level query
    query.WithSubquery
        .WhereProperty(() => file.id)
        .In(subQueryForAttribute);
}
var list = query.List<Files>();

已知IDS的解决方案更简单一些(SQL语句中需要的表更少)

注意:不得不说:很高兴看到你引入了many-to-one和one-to-many，而不是多对多。我个人认为，正是这个例子表明，它能带来多大的利润……即使使用复杂的过滤器也能进行搜索

一些链接，以显示QueryOver:查询在HasMany引用上的强大功能，以及为什么不使用many-to-many映射的一些很好的理由:多对多和额外的列nhibernate

我不确定这是否是你需要的:

<bag name="files_attrs" table="files_attrs" lazy="true" where="something like '%mode = read-only%' and something like '%view = visible%'">
    <key column="attr_id" />
    <one-to-many class="Files_Attrs" />
</bag>

其中something为属性或为要过滤的数据所在的列。

试试这个查询:

Files fAlias = null;
Attrs aAlias = null;
var disjunction = new Disjunction();
disjunction.Add(Restrictions.On(() => aAlias.value)
    .IsLike("mode = read-only", MatchMode.Anywhere));
disjunction.Add(Restrictions.On(() => aAlias.value)
    .IsLike("view = visible", MatchMode.Anywhere));
var subquery = QueryOver.Of<Files_Attrs>
    .Inner.JoinAlias(x => x.file, () => fAlias)
    .Inner.JoinAlias(x => x.attr, () => aAlias)
    .Where(disjunction)
    .Select(() => fAlias);
var files = session.QueryOver<Files>
    .WithSubquery.WhereExists(subquery)
    .List();