Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Linq: the query with Grouping, having and Max

Tags:

linq

group-by

max

I'm trying to run the following MS SQL query:

 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

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:

policyCode Version ...
----------------------
1          2
1          10
2          1
2          25
2          26

We should have the following result:

policyCode Version ...
----------------------
1          10
2          25

The Linq code is:

var res = (from c in CoverageRate
                       group c by c.PolicyCode
                           into rateGroup
                           where rateGroup.Any(r => r.VersionNumber == versionNumber ||
                           r.VersionNumber == 
                           CoverageRate.Where(c2 => c2.OccupancyCode == occupancyCode && c2.PolicyCode == policyCode)
                               .Max(c2 => c2.VersionNumber))
                           select rateGroup);

But I suppose I'm doing something wrong because when I try to take res.Count() I have the following error:

Unable to create a constant value of type '..CoverageRate'. Only primitive types ('such as Int32, String, and Guid') are supported in this context.

Could someone help me with this query?

like image 838
mimic Avatar asked Dec 14 '11 18:12

mimic


2 Answers

Update - I think you are looking for this:

var res =  from c in CoverageRate
           group c by c.PolicyCode into rateGroup
           select rateGroup.OrderByDescending(x => x.VersionNumber)
                           .FirstOrDefault();
like image 153
Aducci Avatar answered Sep 25 '22 14:09

Aducci


from c in CoverageRate
                       group c by c.PolicyCode
                           into g
                           select new
                        {
                            policyCode = g.Key,
                            Version = (from t2 in g select t2.VersionNumber).Max()
                        }
like image 21
Tassadaque Avatar answered Sep 23 '22 14:09

Tassadaque