title: EF Left joining a table on two properties combined with a case statement
body: <p>I'm trying to write a query for a database that will left join a table to a look up table and the results will be returned based on a case statement.</p>
  
  <p>In normal SQL the query would look like this:</p>
  
  <pre><code>SELECT chis_id, chis_detail, cilt.mhcatID, cilt.mhtID, 'TheFileName' =
  CASE  
      WHEN cilt.mhcatID IS NOT NULL AND cilt.mhtID IS NOT NULL THEN chis_linked_filename
      END
  FROM chis
      LEFT JOIN cilt on cilt.mhcatID = chis.mhcat_id AND cilt.mhtID = chis.mht_id 
  WHERE cch_id = 50
  </code></pre>
  
  <p><code>chis</code> is the table being queried, <code>cilt</code> is a look-up table and does not contain any foreign key relationships to chis as a result (chis has existing FK's to mht and mhcat tables by the mhtID and mhcatID respectively).</p>
  
  <p>The query will be used to return a list of history updates for a record. If the join to the cilt lookup table is successful this means that the caller of the query will have permission to view the filename of any associated files for the history updates.</p>
  
  <p>Whilst during my research I've found various posts on here relating on how to do case statements and left joins in Linq to Entity queries, I've not been able to work out how to join on two different fields. Is this possible?</p>