title: How to ask Automapper to grab related record by inner join on Id field which is not a foreign key?
body: <p>I’ve been using Automapper for a while now, and so far it all works great. But recently I came across some “limitation” (or lack of my knowledge).
  Let me give you a simplified example with two classes:</p>
  
  <pre><code>public class Consumable 
  {
      public int ConsumableId { get; set; }
      public string Description { get; set; }
      public int SaleDepartmentId { get; set; }
  }
  
  public class SaleDepartment 
  {
      public int SaleDepartmentId { get; set; }
      public string Description { get; set; }
  }
  </code></pre>
  
  <p>These two entities store the Id of SaleDepartment, but there is not foreign key linking SaleDepartment to Consumable (and I don’t want it as a key), however SaleDepartment has PrimaryKey on SaleDepartmentId</p>
  
  <p>Now my DTO looks very similar</p>
  
  <pre><code>public class ConsumableDTO 
  {
      public int ConsumableId { get; set; }
      public string Description { get; set; }
      public int SaleDepartmentId { get; set; }
  }
  </code></pre>
  
  <p>Here is the mapping </p>
  
  <pre><code>Mapper.CreateMap&lt;Consumable, ConsumableDTO&gt;().ReverseMap();
  </code></pre>
  
  <p>So anytime I bring a Collection of ConsumableDTO’s I also want to bring the related SaleDepartment’s descriptions,
  If there was a navigation property I would do something like this </p>
  
  <pre><code>Mapper.Map&lt;ObservableCollection&lt;Consumable&gt;&gt;
  (context.Consumable.Project().To&lt;ConsumableDTO&gt;()); 
  </code></pre>
  
  <p>But because such a key does not exist, how would I tell the automapper to do inner join based on these Ids I have?
  I have spent two days and I found a way of doing it, but I am not convinced that this the right way, and I am wondering whether I am missing a trick here and there is an easier or better way of achieving this with the automapper. </p>
  
  <p>This is how I achieved getting the related record </p>
  
  <pre><code>var foo = new ObservableCollection&lt;Consumable&gt;(
            (from c in context.Consumable.Project().To&lt;ConsumableDTO&gt;()
             join sd in context.SaleDepartment on c.SaleDepartmentId equals sd.SaleDepartmentId
             select new
             {
                 consumable = c,
                 SaleDepartmentDescription = sd.Description
             }).ToList()
             .Select(p =&gt; Mapper.Map&lt;ConsumableDTO, Consumable&gt;(p.consumable, new Consumable() 
             {
                 SaleDepartmentDescription = p.SaleDepartmentDescription
             })));
  </code></pre>
  
  <p>So, this will grab or consumable and then inner join saledeparments and select description form that inner join, but it seems like quite few steps, is there an easier way of telling the automapper, grab that related record based on this matching Id?</p>
  
  <p>Thank you for your attention and time.</p>