LINQ generating SQL not supported by SQL Server 2008R2

Question

I am using EF 6.1.3 in an ASP.NET MVC 5 project. Although my development environment is SQL Server 2012, QA and Production are on SQL Server 2008 R2.

I have a report that is to show the top 5 procedure codes utilized by a physician, so I have a simple entity with id, name, code1, code2,... code5.

There is a fair amount of logic in building the LINQ query, essentially I get a list of physician and a list of codes and join them. Here is the code that joins them and picks the top 5 codes:

var report =
    from pr in providerRiskLevels
    join nc in newCodes on pr.Provider.ProviderId equals nc.ProviderId
    where pr.RiskCategoryId == RIskScoreIds.VisibleRisk
        && (filterRiskLevelNums.Contains(pr.RiskLevelNum))
        && (filterSpecialtyId == 0 || pr.Provider.Specialty.SpecialtyId == filterSpecialtyId)
    select new ReportNewCodesEntity
    {
        ProviderId = pr.Provider.ProviderId,
        ProviderName = pr.Provider.Name,
        ProviderCode = pr.Provider.ProviderCode,
        SpecialtyName = pr.Provider.Specialty.Name,
        SpecialtyCode = pr.Provider.Specialty.SpecialtyCode,
        RiskScore = pr.RiskScore,
        RiskLevelName = pr.RiskLevelName,
        RiskLevelNum = pr.RiskLevelNum,
        NewCode1 = nc.Codes.OrderByDescending(c => c.Volume).FirstOrDefault().ProcedureCode,
        NewCode2 = nc.Codes.OrderByDescending(c => c.Volume).Skip(1).FirstOrDefault().ProcedureCode,
        NewCode3 = nc.Codes.OrderByDescending(c => c.Volume).Skip(2).FirstOrDefault().ProcedureCode,
        NewCode4 = nc.Codes.OrderByDescending(c => c.Volume).Skip(3).FirstOrDefault().ProcedureCode,
        NewCode5 = nc.Codes.OrderByDescending(c => c.Volume).Skip(4).FirstOrDefault().ProcedureCode
    };
return report;

The problem is that the four Skip().FirstOrDefault() generate SQL with syntax not available on SQL Server 2008 R2:

 OFFSET x ROWS FETCH NEXT 1 ROWS ONLY 

where x = 1 to 4

I'm sure I can move this to a stored procedure (much better at T-SQL than LINQ), but since I have been trying to get a grip on LINQ, I was wondering if there was either:

1. A directive to tell LINQ the version of the target database so it would generate compliant code
2. Another method to extract these top 5 codes.

I do already have the codes ordered correctly in newCodes (see below) but was unable to pick them without the SKIP (which required it's own sort clause).

var newCodes =
    from cc in codeCounts
    group cc by
        new {cc.ProviderId}
        into g
    select new ProviderNewCodesEntity
    {
        ProviderId = g.Key.ProviderId,
        Codes = g.OrderByDescending (x => x.Volume).Take(5).ToList()
    };

Best, Scott

Answer 1

In order to get LINQ to generate compatible code, we simply edited the generated EDMX file (don't shriek yet , keep reading).

We found (on line 7)

<Schema Namespace="ComplianceRiskModel.Store" ProviderManifestToken="2012" Provider="System.Data.SqlClient"  ...>

and changed it to

<Schema Namespace="ComplianceRiskModel.Store" ProviderManifestToken="2008" Provider="System.Data.SqlClient" ...>

To prevent losing this setting every time we refresh the model from the database, we are adding a build task to automatically update the EDMX file in a BeforeBuild target, as described at http://www.programmingmotility.com/2011/05/setting-providermanifesttoken-for-sql.html

Best, Scott