Databases
StackOverflow2013
Postsdbo
PONHibernate using wrong table alias
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PONHibernate using wrong table alias
    text
    copied!<p>I am trying to filter a collection based on a foreign key. I have two classes which are mapped with</p> <pre><code>public class GroupPriceOverrideMap:ClassMap&lt;GroupPriceOverride&gt; { public GroupPriceOverrideMap() { CompositeId() .KeyReference(x =&gt; x.Service,"ServiceCode") .KeyReference(x =&gt; x.CustomerAssetGroup, "GroupID"); Map(x =&gt; x.Price); Table("accGroupPriceOverride"); } } public class CustomerAssetGroupMap:ClassMap&lt;CustomerAssetGroup&gt; { public CustomerAssetGroupMap() { Id(x =&gt; x.GroupID).Unique(); Map(x =&gt; x.Description); References(x =&gt; x.Customer).Column("CustomerID"); HasMany&lt;GroupPriceOverride&gt;(x =&gt; x.PriceOverrides).KeyColumn("GroupID"); Table("accCustAssetGroup"); } } </code></pre> <p>I query it using</p> <pre><code>_session.Linq&lt;GroupPriceOverride&gt;.Where(x =&gt; x.CustomerAssetGroup.GroupID == groupID) </code></pre> <p>However this is generating</p> <pre><code>SELECT this_.ServiceCode as ServiceC1_9_0_, this_.GroupID as GroupID9_0_, this_.Price as Price9_0_ FROM accGroupPriceOverride this_ WHERE customeras1_.GroupID = @p0 </code></pre> <p>there where clause is referencing a table alias which doesn't exist(customeras1). This is probably an alias for crossing with customerassetgroup but there is no need to perform that cross. I'm sure that it is just something in my mapping with is wrong but I can't find it. I've tried various column renaming in case the presence of GroupID in both tables was causing problems but that didn't fix it. Any ideas?</p> <p><strong>Edit</strong> I found that if I queried doing </p> <pre><code>_session.Linq&lt;CustomerAssetGroup&gt;().Where(x =&gt; x.GroupID == groupID).FirstOrDefault().PriceOverrides; </code></pre> <p>then I got the correct result. I also found that if I saved a GroupPriceOverride and then queried for it using HQL then it wouldn't be found but I could still find the entity by loading the parent and looking at its collection of overrides.</p> <pre><code>_session.CreateQuery("FROM GroupPriceOverride i").List().Count;//returns 0 _session.CreateQuery("FROM CustomerAssetGroupi").List().FirstOrDefault().PriceOverrides.Count;//returns 1 </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