title: Linq: the query with Grouping, having and Max
body: <p>I'm trying to run the following MS SQL query:</p>
  
  <pre><code> select PolicyCode, OccupancyCode, premiumcode, VersionNumber, rate
   from COVERAGE_RATES as cov
   group by PolicyCode, OccupancyCode, PremiumCode,VersionNumber, rate
   having (VersionNumber = 25 or VersionNumber = 
   (
       select MAX(versionnumber) from COVERAGE_RATES where
       PolicyCode = 4 and OccupancyCode=2 and PremiumCode = cov.PremiumCode
   ) ) and PolicyCode = 4
   AND OccupancyCode = 2
  </code></pre>
  
  <p>The idea is to take records where VersionNumber is 25 or, if there is no such version in the group, take the maximum number. That is if we have the table:</p>
  
  <pre><code>policyCode Version ...
  ----------------------
  1          2
  1          10
  2          1
  2          25
  2          26
  </code></pre>
  
  <p>We should have the following result:</p>
  
  <pre><code>policyCode Version ...
  ----------------------
  1          10
  2          25
  </code></pre>
  
  <p>The Linq code is:</p>
  
  <pre><code>var res = (from c in CoverageRate
                         group c by c.PolicyCode
                             into rateGroup
                             where rateGroup.Any(r =&gt; r.VersionNumber == versionNumber ||
                             r.VersionNumber == 
                             CoverageRate.Where(c2 =&gt; c2.OccupancyCode == occupancyCode &amp;&amp; c2.PolicyCode == policyCode)
                                 .Max(c2 =&gt; c2.VersionNumber))
                             select rateGroup);
  </code></pre>
  
  <p>But I suppose I'm doing something wrong because when I try to take res.Count() I have the following error:</p>
  
  <p>Unable to create a constant value of type '..CoverageRate'. Only primitive types ('such as Int32, String, and Guid') are supported in this context.</p>
  
  <p>Could someone help me with this query?</p>