Databases
StackOverflow2013
Postsdbo
POFluent NHibernate mapping a CompositeId and Querying with SetProjection
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POFluent NHibernate mapping a CompositeId and Querying with SetProjection
    text
    copied!<p>I have two tables (Section and SectionList) that are related by a many to many table (Membership). Since there is an extra property in the many to many table, i have to break it out into its own entity:</p> <pre><code> public MembershipMap() { UseCompositeId() .WithKeyReference(x =&gt; x.Section, "SectionId") .WithKeyReference(x =&gt; x.SectionList, "SectionList"); Map(x =&gt; x.Status); } </code></pre> <p>And SectionList is mapped as follows:</p> <pre><code> public SectionListMap() { Id(x =&gt; x.Id) .WithUnsavedValue(0); HasMany(x =&gt; x.Memberships) .Inverse() .Cascade.AllDeleteOrphan(); } </code></pre> <p>The relationship seems to work fine, except for when I try to run advanced queries on it. For instance, here is a query that grabs only certain fields and transforms to a DTO:</p> <pre><code> var criteria = DetachedCriteria.For&lt;CustomSectionListMembership&gt;() .CreateAlias("SectionList", "sl") .CreateAlias("Section", "s") .CreateAlias("s.Website", "w") .CreateAlias("w.Publisher", "p") .SetProjection(Projections.ProjectionList() .Add(Projections.Property("s.Id"), "SectionId") //add projections for every propery you want returned .Add(Projections.Property("s.Name"), "SectionName") // mapping entity name -&gt; DTO name .Add(Projections.Property("w.Id"), "WebsiteId") .Add(Projections.Property("w.Name"), "WebsiteName") .Add(Projections.Property("p.Id"), "PublisherId") .Add(Projections.Property("p.Name"), "PublisherName") .Add(Projections.Property("Status"))) .Add(Expression.Eq("sl.Id", listId)) .SetResultTransformer(Transformers.AliasToBean(typeof(MembershipDTO))); //transform to the DTO var membership = repository.FindAll(criteria); </code></pre> <p>This query errors out with "could not execute query", because the query being generated is completely missing the inner joins that should have been generated by the CreateAlias calls:</p> <pre><code>SELECT s2_.SectionId as y0_, s2_.Name as y1_, w3_.WebsiteId as y2_, w3_.Name as y3_, p4_.PublisherId as y4_, p4_.Name as y5_, this_.Status as y6_ FROM Membership this_ WHERE csl1_.ListId = 6923 /* @p0 */ </code></pre> <p>What could possibly be the problem?</p>
 

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