即使最简单的连接也会在NHibernate中变得复杂 [英] Even easiest join gets complicated in NHibernate

问题描述

好，所以我有以下数据库架构

Ok, So I have the following database schema

该关系是一对多关系(一个相册可以包含许多图像)

And the relation is a one-to-many relation (one Album can have many Images)

映射看起来像这样:

<property name="NumID"...>
  <.../>
</property>
<set name="Images" inverse="true" cascade="delete" generic="true">
  <key>
    <column name="AlbumID" />
  </key>
  <one-to-many class="Images" />
</set>

并且默认获取设置为惰性

And the default fetching is set to lazy

现在出现问题: 我想加载保存图片的相册(我知道 NumID )

Now to the problem: I want to load the album that holds a picture (to which I know the NumID)

我将使用以下SQL做到这一点:

And I would do this with the following SQL:

select * from Images img
join Albums alb
on alb.NumId = img.AlbumId
where img.NumID = 1026

但是我将如何在NHibernate中做到这一点?当然，我以多种不同的方式尝试了不同程度的成功，但是没有什么比它拥有的SQL更简单了.

But how would I do it in NHibernate? Of course I have tried in a bunch of different ways with different level of success, but nothing that is as simple as the SQL it selves.

作为一个加重因素，我确实需要整个对象(相册)(或者至少需要 NumID 和 Name ，而不是延迟加载)

As a aggravating factor I do need the object (Album) as a whole (or at least I need both the NumID and the Name, not lazy loaded)

我已经尝试过了:

/* 1 */
var tmp1 = session.QueryOver<Images>()
   .Where(i => i.NumID == ImageID) //The ImageID is for example 1026
   .JoinQueryOver(i => i.Album)
   .Select(x => x.Album)
   .List<Album>();
// Decent to read, but this only loads the NumID from the album

/* 2 */
var tmp2 = session.Query<Images>()
    .Join(DBContext.GetQuery<Albums>(), i => i.Albums.NumID, a => a.NumID,
        (imgAlias, albAlias) => new {imgAlias, albAlias})
    .Where(x => x.imgAlias.NumID == ImageID)  //ImageID = 1026
    .Select(x => x.albAlias).ToList();
// This one actually works, but it's almost totally unreadable!

应该怎么做?

推荐答案

(我经常使用，如果不是，不仅如此)-使用 subquery (内部SELECT)

 // subquery returning the ALBUM ID
 var sq = QueryOver.Of<Image>()
      .Where(i => i.NumID == ImageID) //The ImageID is for example 1026
      .Select(i => i.Album.Id);       // here we return the Album.ID (column AlbumID)

 // just Albums with searched Image
 var query = QueryOver.Of<Album>()
            .WithSubquery
               .WhereProperty(a => a.Id)
               .In(sq) 
            ...
            .List<Album>();

最大的优点是，Album结果(SELECT)是 flat (not joined with multi Images)-因此我们可以轻松地使用分页:

The biggest advantage is, that the Album result (SELECT) is flat (not joined with multi Images) - so we can easily use paging:

...
.Take(50)
.Skip(50)
.List<Album>();

检查其他一些子查询 QueryOver:从子查询中选择列

这篇关于即使最简单的连接也会在NHibernate中变得复杂的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！