内部联接后防止多个实例 [英] 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<Store>()将正确地返回一个不同的列表.而且，根据设计，它将支持分页(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));
}

顶部

一旦我们对商店进行了过滤，就可以在前一个中使用此子查询

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>();

现在，我们可以将任何过滤器应用于内部查询，并获取符合过滤条件的商店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屋！