title: NHibernate / QueryOver: How to left join with parameter
body: <p>Is there a way in NHibernate / QueryOver API to pass a parameter already in the mapping (so it uses the parameter as a fixed value for all queries on this particular instance).</p>
  
  <p>I need this (or a workaround) because I have a view like this in the Database:</p>
  
  <pre><code>CREATE VIEW ProblematicView
  AS
  SELECT
      v.*,
  -- lots of data
  FROM someView v
  LEFT JOIN someTable t ON v.ForeignKey = t.ForeignKey
  </code></pre>
  
  <p>Now additionally to the ForeignKey match I need an additional check for a property like this:</p>
  
  <pre><code>AND t.SomeOtherValue = @myParameter
  </code></pre>
  
  <p>which is not possible as there is no way to pass parameters to a view directly. With a table valued function this would be possible but then I don´t know how to map it to NHibernate / QueryOver.</p>
  
  <p>Also the function approach would be hard to realize as a huge QueryOver statement is used to filter all the remaining properties (as the view is used for searching business entities)</p>
  
  <p>Currently I am applying the SomeOtherValue / @myParameter filter to the the entire view as a part of my QueryOver.</p>
  
  <p>This is my main problem: </p>
  
  <p>Using for example:</p>
  
  <pre><code>SELECT
      v.*,
  -- lots of data
  FROM someView v
  LEFT JOIN someTable t ON v.ForeignKey = t.ForeignKey AND t.SomeOtherValue = 123
   (followed by alot of other property checks...)
  </code></pre>
  
  <p>will return a different result (NULL entries for t.SomeOtherValue included on intent due to left join)</p>
  
  <p>than using:</p>
  
  <pre><code>SELECT * FROM ProblematicView where SomeOtherValue = 123
   (followed by alot of other property checks)
  </code></pre>
  
  <p>As now the left join happens inside the view without checking for SomeOtherValue and as the SomeOthervalue check is applied independent on the left join, all the NULL values will be excluded (which is wrong business logic).</p>
  
  <p>Also using:</p>
  
  <pre><code>SELECT * FROM ProblematicView where SomeOtherValue = 123 OR SomeOtherValue = NULL
   (followed by alot of other property checks)
  </code></pre>
  
  <p>does not seem to help, as NULL values are still ignored...</p>
  
  <p>So the only way I can imagine to solve this problem would be to find a way to pass my SomeOtherValue property somehow to the view so that it can use it as a parameter in the view itself (instead of in the where clause) or maybe somehow use an sql table based function with parameter for the model...</p>
  
  <p>EDIT:</p>
  
  <p>After some more research I managed to hopefully simplify the problem:</p>
  
  <p>I am trying to convert this SQL:</p>
  
  <pre><code>Select v.*, ... from (someView v LEFT JOIN someTable t ON v.ForeignKey = t.ForeignKey) 
  WHERE SomeOtherValue = 123
  </code></pre>
  
  <p>(where SomeOtherValue comes from someOtherTable)</p>
  
  <p>to this:</p>
  
  <pre><code>Select v.*, ... from someView v LEFT JOIN someTable t on v.ForeignKey = t.ForeignKey
  AND t.SomeOtherValue = 123
  </code></pre>
  
  <p>using NHibernate / QueryOver. Note that in the second version the property SomeOthervalue is checked against directly within the left join, where in the first version it is incorrectly applied only after the left join.</p>
  
  <p>I need to find a way to write latter SQL statement in a way that I can put it inside a view while still being able to pass 123 as parameter for SomeOtherValue.</p>