title: FluentNhibernate - boolean flag based on count from another table
body: <p>I have seen a few questions like this on SO, but I still cannot get this to work in my code, despite my code looking like the accepted answers.</p>
  
  <p>First, my mapping (condensed)</p>
  
  <pre><code>public SiteMap()
  {
      Table("SiteDetail");
  
      Id(x =&gt; x.SiteId, "Id").GeneratedBy.Identity();
  }
  </code></pre>
  
  <p>Then, the other table's structure</p>
  
  <pre><code>CREATE TABLE [dbo].[EmailConfig](
      [SiteID] [int] NOT NULL,
      [Active] [int] NULL,
      [HeaderText] [int] NULL,
      [TrailerText] [int] NULL,
      [BodyText] [int] NULL,
      [CopyEmailTo] [varchar](255) NULL,
  CONSTRAINT [PK_EmailConfig] PRIMARY KEY CLUSTERED ([SiteID] ASC)) 
  </code></pre>
  
  <p>And here is my property in my object</p>
  
  <pre><code>public virtual bool EmailReceiptsEnabled { get; set; }
  </code></pre>
  
  <p>And here is the mapping to set this property</p>
  
  <pre><code>Map(x =&gt; x.EmailReceiptsEnabled)
      .Formula("(SELECT COUNT(e.SiteId) FROM EmailConfig e WHERE e.SiteId = SiteId AND e.Active = 1)");
  </code></pre>
  
  <p>When I comment out that property mapping, everything works fine.  When I leave it in, my repository returns null when trying to select a single site.  No error is thrown, other than the null reference exception on the page trying to access a property of the site object.</p>
  
  <p><strong>EDIT:</strong></p>
  
  <p>I have modified my solution to pull back an object like so:</p>
  
  <pre><code>public class EmailConfiguration : CoreObjectBase
  {
      public virtual int SiteId { get; set; }
      public virtual int Active { get; set; }
  }
  </code></pre>
  
  <p>With a property in my <code>Site</code> class</p>
  
  <pre><code>public virtual EmailConfiguration EmailConfiguration { get; set; }
  </code></pre>
  
  <p>And the mapping for the object</p>
  
  <pre><code>public class EmailConfigurationMap : ClassMap&lt;EmailConfiguration&gt;
  {
      public EmailConfigurationMap()
      {
          Table("EmailConfig");
  
          Id(x =&gt; x.SiteId);
  
          Map(x =&gt; x.Active);
      }
  }
  </code></pre>
  
  <p>And then the mapping in my site</p>
  
  <pre><code>References(x =&gt; x.EmailConfiguration, "Id");
  </code></pre>
  
  <p>And it produces an error</p>
  
  <blockquote>
    <p>NHibernate.ObjectNotFoundException: No row with the given identifier exists</p>
  </blockquote>
  
  <p>The issue is that EmailConfig contains no record until the client goes in to the app to configure their Email.  That can't be changed, but I need my application to be able to use this data.  Any ideas on how to map this thing?</p>
  
  <p><strong>Yet another edit</strong></p>
  
  <p>I was able to get it to work by changing the property to this</p>
  
  <pre><code>public virtual IList&lt;EmailConfiguration&gt; EmailConfigurations { get; set; }
  </code></pre>
  
  <p>And then implementing this complete disaster</p>
  
  <pre><code>HasMany&lt;EmailConfiguration&gt;(x =&gt; x.EmailConfigurations)
      .KeyColumn("SiteId")
      .Inverse();
  </code></pre>
  
  <p>I think I'll go cry quietly until I can figure out how to make this mess work the right way.  Since the <code>EmailConfig</code> table uses the PK of my site table as its primary key, the only possibilities are to have 1 or no records.  <code>References</code> fails on null with a hard error.  HasOne doesn't work either (causes the program to not pull back my <code>Site</code> objects at all without any errors).</p>