NHibernate中的项目集合 [英] Project Collection in NHibernate

问题描述

是否可以在NHibernate中进行项目收集? 例如:

Is it possible that I can project collection in NHibernate? For Example:

User
{
    UserGuid,
    IList<Groups> UserGroups,
}

User userEntity = null;

_session
    .StatefulSession.QueryOver(() => userEntity)
    .SelectList(list => list
         .Select(x => x.UserGuid).WithAlias(() => userEntity.UserGuid)

         //How can I project group collection here?
         .Select(x => x.Groups).WithAlias(() => userEntity.Groups)
    )
    .TransformUsing(Transformers.AliasToBean<UserEntity>())
    .List();

推荐答案

我们必须考虑将投影作为单个SELECT子句.我们可以在数据库方面使用它，我们可以使用QueryOver进行模仿.因此，在这种情况下，我们可以联接User和Group，并从User投影很少的属性，从Group投影很少的属性.最好的目标是一些DTO ...

We have to thing about projection as a single SELECT clause. What we would be able to do with it on a DB side, we can mimic with QueryOver. So, in this case, we can join User and Group, and project few properties from User and few from Group. The best target would be some DTO...

语法可能是这样的:

ResultDTO resultDTO = null; // the target to be returned
Group groupEntity = null;
User userEntity = null;

_session.StatefulSession
    .QueryOver(() => userEntity)
    .JoinAlias(() => userEntity.Groups, () => groupEntity)
    .SelectList(list => list
        .Select(x => x.UserGuid).WithAlias(() => resultDTO.UserGuid)
        // more about User - targeting the ResultDTO
        ...
        // almost the same for Group properties
        .Select(x => groupEntity.Name).WithAlias(() => resultDTO.GroupName)
        ..
    )
    .TransformUsing(Transformers.AliasToBean<ResultDTO >())
    .List();

但是有一些问题，实际上实际上是不允许使用这种方法的.我们将达到Cartesian product，因为用户的每一行将被乘以其所拥有的组的次数.

But there are some issues arround, which in fact effectively disallow this approach. We will reach the Cartesian product, because each row of a User will be multiplied as many times, as many Groups it does have.

Row 1 - User A - Group A
Row 2 - User A - Group B // two rows for a user A
Row 3 - User B - Group B
Row 4 - User B - Group C

一种解决方法是跳过SelectList() (不使用投影)，并要求NHiberante使用不同的变换:

One way to solve it, is to skip the SelectList() (do not use projections) and ask NHiberante to use different transformation:

_session.StatefulSession
    .QueryOver(() => userEntity)
    .JoinAlias(() => userEntity.Groups, () => groupEntity)
    .TransformUsing(Transformers.DistinctRootEntity)
    .List();

很不错，有人会说，但这又是错误的.无法进行有效的分页，因为仍然保留每个用户更多行的效果.因此，如果我们说Take(2)，我们将仅获得用户A .原因实际上是隐藏的，因为缩小是在内存中在.NET/应用程序层侧而不是在数据库中进行的事.

Nice, one would say, but it is wrong again. No effective paging is possible, because the effect of more rows for each User still remains. So, if we would say Take(2), we will get only User A. The reason is hidden in fact, that the narrowing is done expost, in memory, on .NET/Application tier side - not in DB

建议:请勿在一对多关系中使用任何联接.结合使用单独的查询和强大的NHibernate功能: 19.1.5 .使用批量提取

Suggestion: Do not use any joins among one-to-many relations. Use the separated queries with conjunction of powerful NHibernate feature: 19.1.5. Using batch fetching

请，另请参阅:

• NHibernate QueryOver，可提取多个SQL查询和数据库匹配
• 这是渴望在NHibernate中加载子集合的正确方法
• https://stackoverflow.com/questions/18419988/

• NHibernate QueryOver with Fetch resulting multiple sql queries and db hits
• Is this the right way to eager load child collections in NHibernate
• https://stackoverflow.com/questions/18419988/

这篇关于NHibernate中的项目集合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！