Databases
StackOverflow2013
Postsdbo
POQueryOver and ProjectionLists
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POQueryOver and ProjectionLists
    text
    copied!<p>We are trying to use QueryOver and projects. Basically it works fine. But we are currently struggling with a projection which should contain multiple items. The problem is that we have pallets which can contain pallet items. Pallet items are the base classes for various subclasses. We need to be able to projects only one property of each subclass <em>MagazinePlaceLoadingOverviewData</em>. Any ideas how we could achieve this?</p> <p>Information: Magazine contains Levels, Levels contains Places, Places can have one Pallet assigned and a pallet can have multiple pallet items on it. Pallet items can be of various subtypes. We tried to get into the subtypes by implementing a visitor pattern on the pallet item but we can't even get the query below working.</p> <pre><code> 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(() =&gt; magazineLoadingAlias) .JoinAlias(x =&gt; x.Pallet, () =&gt; palletAlias) .JoinAlias(() =&gt; palletAlias.PalletType, () =&gt; palletTypeAlias) .JoinQueryOver(x =&gt; x.Place, () =&gt; magazinePlaceAlias) .JoinQueryOver(x =&gt; x.Level, () =&gt; magazineLevelAlias) .JoinQueryOver(x =&gt; x.Magazine, () =&gt; magazineAlias) .Where(x =&gt; x.Id == this.magazineId) .Select( Projections.Property(() =&gt; magazinePlaceAlias.Id).WithAlias(() =&gt; overviewAlias.MagazinePlaceId), Projections.Property(() =&gt; magazineLoadingAlias.PalletInProgress).WithAlias(() =&gt; overviewAlias.PalletInProgress), Projections.ProjectionList().Add(Projections.Property(() =&gt; palletAlias.PalletItems), () =&gt; overviewAlias.Items), Projections.Property(() =&gt; palletTypeAlias.Classification).WithAlias(() =&gt; overviewAlias.PalletTypeClassification)) .TransformUsing(Transformers.AliasToBean&lt;MagazinePlaceLoadingOverviewData&gt;()) .List&lt;MagazinePlaceLoadingOverviewData&gt;(); </code></pre> <p>This leads to the following exception</p> <pre><code>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&gt;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&lt;TRoot&gt;.List&lt;U&gt;() 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 </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload