内连接后防止多个实例 [英] Preventing multiple instance after inner join

问题描述

我有一个小问题，在连接到另一个表后，同一对象有多个实例.为了测试，我创建了一个 Store 和两个 Products(ManyToMany-Relation).以下代码段有望描述我的问题.

I have a small problem with multiple instances of the same object after a join to an other table. For testing I create one Store with two Products (ManyToMany-Relation). The following snippet hopefully describes my problem.

var preResult = _session.QueryOver<Store>().List(); // One store

Product productAlias = null;
var result = _session.QueryOver<Store>()
    .JoinAlias(s => s.Products, () => productAlias)
    .List();                                        // Two instances of the same store

我什至认为这种行为是正确的，但如何防止出现多个实例?是否可以在查询中使用?

I even think this behavior is correct but how can I prevent the multiple instances? Is it possible within the query?

只是为了了解为什么我需要进行这个不必要的连接:我想根据不同的标准扩展查询，类似于:

Just for information why I need to make this unnecessary join: I want to extend the query according to different critirias, similar to this:

Product productAlias = null;
var query = _session.QueryOver<Store>().JoinAlias(s => s.Products, () => productAlias);
if (!string.IsNullOrWhiteSpace(criteria.ProductName))
{
    query.Where(Restrictions.On(() => productAlias.Name).IsInsensitiveLike(criteria.ProductName));
}

if (criteria.ProductType != null)
{
    query.Where(s => productAlias.Type == criteria.ProductType);
}

var result = query.List();

在这里，我遇到了不同的问题，具体取决于标准.

Here I ran into different problems, depending on the criterias.

推荐答案

让我们将解决方案拆分为两个查询.

Let's split solution into two queries.

1. 最重要的 QueryOver() 将正确返回一个不同的列表.更重要的是，在设计上它将支持分页(Take(), Skip()).
2. 内部的，将只返回一个商店 ID 列表，它完全符合任何标准......

1. Top one QueryOver<Store>() will be correctly returning just a distinct list. And what's more, by design it will support paging (Take(), Skip()).
2. The inner one, will be returning just a list of Store IDs, which fully meet whatever criteria...

结果 SQL 将如下所示

The result SQL will look like this

SELECT ... // top one 
FROM Store
WHERE StoreID IN ( SELECT StoreID ...) // inner one

内部

让我们从内部选择开始，NHibernate 分离的QueryOver:

Store storeAlias = null;
Product productAlias = null;

// detached query, resulting in a set of searched StoreID 
var subQuery = QueryOver.Of<Store>(() => storeAlias)
    .JoinAlias((s) => s.Products, () => productAlias)
    .Select((s) => s.ID); // ID projection

if (!string.IsNullOrWhiteSpace(criteria.ProductName))
{
    subQuery.Where(Restrictions.On(() => productAlias.Code)
        .IsInsensitiveLike(criteria.ProductName));
}

顶部

一旦我们过滤了 Store，我们就可以在顶部使用这个子查询

Top

Once we have filtered the Store we can use this subquery in top one

var query = session.QueryOver<Store>()
   // IN clause
   .Where(Subqueries.PropertyIn("ID", subQuery.DetachedCriteria))
   .Skip(100) 
   .Take(50) // paging over already distinct resultset
   ;

var result = query.List<Store>();

现在我们可以将任何过滤器应用于内部查询，并获取符合过滤条件的 Store ID 列表......同时使用不同的顶级查询......

And now we can apply whatever filter to inner query, and get list of Store IDs which do meet filter criteria... while working with top query, which is distinct...

这篇关于内连接后防止多个实例的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！