QueryOver和ProjectionLists

Question

我们正在尝试使用QueryOver和项目。基本上它很好用。但是，我们目前正在努力实现一个应该包含多个项目的预测。问题是，我们有托盘，可以包含托盘项目。托盘项是各种子类的基类。我们需要能够只对每个子类MagazinePlaceLoadingOverviewData的一个属性进行项目。我们有什么办法做到这一点吗？

信息:杂志包含层次，层次包含地方，地方可以有一个托盘分配，一个托盘可以有多个托盘项目在上面。托盘项目可以是各种子类型。我们试图通过在托盘项目上实现一个访问者模式来进入子类型，但是我们甚至无法让下面的查询正常工作。

        MagazinePlaceLoadingEntity magazineLoadingAlias = null;
        MagazinePlaceSettingsEntity magazinePlaceAlias = null;
        MagazineLevelSettingsEntity magazineLevelAlias = null;
        MagazineSettingsEntity magazineAlias = null;
        MagazinePlaceLoadingOverviewData overviewAlias = null;

        PalletEntity palletAlias = null;
        PalletTypeSettingsEntity palletTypeAlias = null;
        PalletItemEntity palletItemsAlias = null;

        return session.QueryOver(() => magazineLoadingAlias)
                        .JoinAlias(x => x.Pallet, () => palletAlias)
                        .JoinAlias(() => palletAlias.PalletType, () => palletTypeAlias)
                        .JoinQueryOver(x => x.Place, () => magazinePlaceAlias)
                        .JoinQueryOver(x => x.Level, () => magazineLevelAlias)
                        .JoinQueryOver(x => x.Magazine, () => magazineAlias)
                        .Where(x => x.Id == this.magazineId)
                        .Select(
                            Projections.Property(() => magazinePlaceAlias.Id).WithAlias(() => overviewAlias.MagazinePlaceId),
                            Projections.Property(() => magazineLoadingAlias.PalletInProgress).WithAlias(() => overviewAlias.PalletInProgress),
                            Projections.ProjectionList().Add(Projections.Property(() => palletAlias.PalletItems), () => overviewAlias.Items),
                            Projections.Property(() => palletTypeAlias.Classification).WithAlias(() => overviewAlias.PalletTypeClassification))
                        .TransformUsing(Transformers.AliasToBean<MagazinePlaceLoadingOverviewData>())
                        .List<MagazinePlaceLoadingOverviewData>();

这将导致以下异常

System.Data.SqlServerCe.SqlCeException: The column aliases must be unique. [ Name of duplicate alias = y2_ ]
at System.Data.SqlServerCe.SqlCeCommand.ProcessResults(Int32 hr)
at System.Data.SqlServerCe.SqlCeCommand.CompileQueryPlan()
at System.Data.SqlServerCe.SqlCeCommand.ExecuteCommand(CommandBehavior behavior, String method, ResultSetOptions options)
at System.Data.SqlServerCe.SqlCeCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
at NHibernate.AdoNet.AbstractBatcher.ExecuteReader(IDbCommand cmd)
at NHibernate.Loader.Loader.GetResultSet(IDbCommand st, Boolean autoDiscoverTypes, Boolean callable, RowSelection selection, ISessionImplementor session)
at NHibernate.Loader.Loader.DoQuery(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies)
at NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies)
at NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters) 
NHibernate.Exceptions.GenericADOException: could not execute query
[ SELECT magazinepl3_.Id as y0_, this_.PalletInProgress as y1_, palletalia1_.Id as y2_, pallettype2_.Classification as y2_ FROM "MagazinePlaceLoading" this_ inner join "Pallet" palletalia1_ on this_.PalletId=palletalia1_.Id inner join "PalletTypeSettings" pallettype2_ on palletalia1_.PalletTypeSettingsId=pallettype2_.Id inner join "MagazinePlaceSettings" magazinepl3_ on this_.MagazinePlaceSettingsId=magazinepl3_.Id inner join "MagazineLevelSettings" magazinele4_ on magazinepl3_.MagazineLevelSettingsId=magazinele4_.Id inner join "MagazineSettings" magazineal5_ on magazinele4_.MagazineSettingsId=magazineal5_.Id WHERE magazineal5_.Id = @p0 ]
Positional parameters:  #0>cb9ff95a-58ca-4b2b-9aa6-9f6c008fc0b3
[SQL: SELECT magazinepl3_.Id as y0_, this_.PalletInProgress as y1_, palletalia1_.Id as y2_, pallettype2_.Classification as y2_ FROM "MagazinePlaceLoading" this_ inner join "Pallet" palletalia1_ on this_.PalletId=palletalia1_.Id inner join "PalletTypeSettings" pallettype2_ on palletalia1_.PalletTypeSettingsId=pallettype2_.Id inner join "MagazinePlaceSettings" magazinepl3_ on this_.MagazinePlaceSettingsId=magazinepl3_.Id inner join "MagazineLevelSettings" magazinele4_ on magazinepl3_.MagazineLevelSettingsId=magazinele4_.Id inner join "MagazineSettings" magazineal5_ on magazinele4_.MagazineSettingsId=magazineal5_.Id WHERE magazineal5_.Id = @p0]
at NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters)
at NHibernate.Loader.Loader.ListIgnoreQueryCache(ISessionImplementor session, QueryParameters queryParameters)
at NHibernate.Loader.Loader.List(ISessionImplementor session, QueryParameters queryParameters, ISet`1 querySpaces, IType[] resultTypes)
at NHibernate.Impl.SessionImpl.List(CriteriaImpl criteria, IList results)
at NHibernate.Impl.CriteriaImpl.List(IList results)
at NHibernate.Impl.CriteriaImpl.List()
at NHibernate.Impl.CriteriaImpl.Subcriteria.List()
at NHibernate.Criterion.QueryOver`1.List()
at NHibernate.Criterion.QueryOver`1.NHibernate.IQueryOver<TRoot>.List<U>()
at .Domain.Queries.Magazines.GetLoadingOverviewByMagazineQuery.Find(ISession session) in GetLoadingOverviewByMagazineQuery.cs: line 41
at .Domain.Queries.QueryTestBase.Execute(IEnumerableQuery`1 query) in QueryTestBase.cs: line 39
at .Domain.Queries.Magazines.GetLoadingOverviewByMagazineQueryTest.Find_ShouldReturnOverviewData() in GetLoadingOverviewByMagazineQueryTest.cs: line 39 

Answer 1

你不能这么做：

Projections.ProjectionList().Add(Projections.Property(() => palletAlias.PalletItems), () => overviewAlias.Items),

AliasToBean转换器意味着您正在扁平您的结果集。您不能在您的MagazinePlaceLoadingOverviewData中有一个列表。

您将需要将项目列表作为第二个查询，并使用Linq将它们连接到对象(如果您希望它们具有这种格式)。