I'm receiving the error:
Parameters as arguments to a TOP sub-clause and a LIMIT sub-clause in a query, or a LimitExpression in a command tree, are not supported in versions of SQL Server earlier than SQL Server 2005.
When using WebAPI + oData when doing Take()
in LinqPad or via http:
http://localhost:8080/odata/sample()?$top=10
However if I run a Take() against the DbContext directly it works fine. So I presume that the oData magic is creating some linq / sql which isn't supported in my setup.
The complications I have is that the EDMX needs to be setup to run in compatibility mode 80 (sql 2000).
And that I am using Composite Key in ASP.NET Web API OData (but I understand now that's the recommended approach from another post so probably isn't the problem).
And it's coming from a view, not a table.
And I'm using WebApi 1 (not 2).
Is there a way to override the SQL that is being generated so I can avoid the incompatible SQL?
EDIT:
After stepping through the webapi (and then entity framework code) - an enlightening experience, I have found the line that is throwing the exception:
In \entityframework\src\EntityFramework.SqlServer\SqlGen\Sql8ConformanceChecker.cs
/// <summary>
/// Walks the structure
/// </summary>
/// <exception cref="NotSupportedException">expression.Limit is DbParameterReferenceExpression</exception>
public override bool Visit(DbLimitExpression expression)
{
Check.NotNull(expression, "expression");
if (expression.Limit is DbParameterReferenceExpression)
{
throw new NotSupportedException(Strings.SqlGen_ParameterForLimitNotSupportedOnSql8);
}
return VisitExpression(expression.Argument);
}
But I'm still not sure why I can easily do a OrderBy().Take() against the context but the oData version is failing.
I've managed to compare the DbQueryCommandTree generated by oData vs the equivalent generated when querying the dbContext.
The oData version:
{DbQueryCommandTree
|_Parameters
| |_p__linq__0 : Edm.Int32
|_Query : Collection{Record['UserID'=Edm.Int32, 'RolledupReviewPeriodID'=Edm.Int32, 'UserEmployeeNumber'=Edm.String, 'FirstName'=Edm.String, 'LastName'=Edm.String, 'JobTitle'=Edm.String, 'CostCentre'=Edm.String, 'CostCentreNo'=Edm.String, 'Department'=Edm.String, 'Division'=Edm.String, 'Directorate'=Edm.String, 'AppraiserName'=Edm.String, 'ReviewDate'=Edm.DateTime, 'Status'=Edm.String, 'InterimRating'=Edm.Int32, 'IndicativeRating'=Edm.Int32, 'PreModerated'=Edm.Int32, 'ModeratedRating'=Edm.Int32, 'FinalRating'=Edm.Int32, 'ReviewPeriodName'=Edm.String]}
|_Project
|_Input : 'Limit1'
| |_Limit
| |_Sort
| | |_Input : 'Extent1'
| | | |_Scan : PMM_ModelStoreContainer.vwReports_ReviewRatings
| | |_SortOrder
| | |_Asc
| | | |_Var(Extent1).RolledupReviewPeriodID
| | |_Asc
| | |_Var(Extent1).UserID
| |_@p__linq__0
|_Projection
|_NewInstance : Record['UserID'=Edm.Int32, 'RolledupReviewPeriodID'=Edm.Int32, 'UserEmployeeNumber'=Edm.String, 'FirstName'=Edm.String, 'LastName'=Edm.String, 'JobTitle'=Edm.String, 'CostCentre'=Edm.String, 'CostCentreNo'=Edm.String, 'Department'=Edm.String, 'Division'=Edm.String, 'Directorate'=Edm.String, 'AppraiserName'=Edm.String, 'ReviewDate'=Edm.DateTime, 'Status'=Edm.String, 'InterimRating'=Edm.Int32, 'IndicativeRating'=Edm.Int32, 'PreModerated'=Edm.Int32, 'ModeratedRating'=Edm.Int32, 'FinalRating'=Edm.Int32, 'ReviewPeriodName'=Edm.String]
|_Column : 'UserID'
| |_Var(Limit1).UserID
|_Column : 'RolledupReviewPeriodID'
| |_Var(Limit1).RolledupReviewPeriodID
|_Column : 'UserEmployeeNumber'
| |_Var(Limit1).UserEmployeeNumber
|_Column : 'FirstName'
| |_Var(Limit1).FirstName
|_Column : 'LastName'
| |_Var(Limit1).LastName
|_Column : 'JobTitle'
| |_Var(Limit1).JobTitle
|_Column : 'CostCentre'
| |_Var(Limit1).CostCentre
|_Column : 'CostCentreNo'
| |_Var(Limit1).CostCentreNo
|_Column : 'Department'
| |_Var(Limit1).Department
|_Column : 'Division'
| |_Var(Limit1).Division
|_Column : 'Directorate'
| |_Var(Limit1).Directorate
|_Column : 'AppraiserName'
| |_Var(Limit1).AppraiserName
|_Column : 'ReviewDate'
| |_Var(Limit1).ReviewDate
|_Column : 'Status'
| |_Var(Limit1).Status
|_Column : 'InterimRating'
| |_Var(Limit1).InterimRating
|_Column : 'IndicativeRating'
| |_Var(Limit1).IndicativeRating
|_Column : 'PreModerated'
| |_Var(Limit1).PreModerated
|_Column : 'ModeratedRating'
| |_Var(Limit1).ModeratedRating
|_Column : 'FinalRating'
| |_Var(Limit1).FinalRating
|_Column : 'ReviewPeriodName'
|_Var(Limit1).ReviewPeriodName}
The version when hitting the dbContext directly
{DbQueryCommandTree
|_Parameters
|_Query : Collection{Record['UserID'=Edm.Int32, 'RolledupReviewPeriodID'=Edm.Int32, 'UserEmployeeNumber'=Edm.String, 'FirstName'=Edm.String, 'LastName'=Edm.String, 'JobTitle'=Edm.String, 'CostCentre'=Edm.String, 'CostCentreNo'=Edm.String, 'Department'=Edm.String, 'Division'=Edm.String, 'Directorate'=Edm.String, 'AppraiserName'=Edm.String, 'ReviewDate'=Edm.DateTime, 'Status'=Edm.String, 'InterimRating'=Edm.Int32, 'IndicativeRating'=Edm.Int32, 'PreModerated'=Edm.Int32, 'ModeratedRating'=Edm.Int32, 'FinalRating'=Edm.Int32, 'ReviewPeriodName'=Edm.String]}
|_Project
|_Input : 'Limit1'
| |_Limit
| |_Sort
| | |_Input : 'Extent1'
| | | |_Scan : PMM_ModelStoreContainer.vwReports_ReviewRatings
| | |_SortOrder
| | |_Asc
| | | |_Var(Extent1).RolledupReviewPeriodID
| | |_Asc
| | |_Var(Extent1).UserID
| |_10
|_Projection
|_NewInstance : Record['UserID'=Edm.Int32, 'RolledupReviewPeriodID'=Edm.Int32, 'UserEmployeeNumber'=Edm.String, 'FirstName'=Edm.String, 'LastName'=Edm.String, 'JobTitle'=Edm.String, 'CostCentre'=Edm.String, 'CostCentreNo'=Edm.String, 'Department'=Edm.String, 'Division'=Edm.String, 'Directorate'=Edm.String, 'AppraiserName'=Edm.String, 'ReviewDate'=Edm.DateTime, 'Status'=Edm.String, 'InterimRating'=Edm.Int32, 'IndicativeRating'=Edm.Int32, 'PreModerated'=Edm.Int32, 'ModeratedRating'=Edm.Int32, 'FinalRating'=Edm.Int32, 'ReviewPeriodName'=Edm.String]
|_Column : 'UserID'
| |_Var(Limit1).UserID
|_Column : 'RolledupReviewPeriodID'
| |_Var(Limit1).RolledupReviewPeriodID
|_Column : 'UserEmployeeNumber'
| |_Var(Limit1).UserEmployeeNumber
|_Column : 'FirstName'
| |_Var(Limit1).FirstName
|_Column : 'LastName'
| |_Var(Limit1).LastName
|_Column : 'JobTitle'
| |_Var(Limit1).JobTitle
|_Column : 'CostCentre'
| |_Var(Limit1).CostCentre
|_Column : 'CostCentreNo'
| |_Var(Limit1).CostCentreNo
|_Column : 'Department'
| |_Var(Limit1).Department
|_Column : 'Division'
| |_Var(Limit1).Division
|_Column : 'Directorate'
| |_Var(Limit1).Directorate
|_Column : 'AppraiserName'
| |_Var(Limit1).AppraiserName
|_Column : 'ReviewDate'
| |_Var(Limit1).ReviewDate
|_Column : 'Status'
| |_Var(Limit1).Status
|_Column : 'InterimRating'
| |_Var(Limit1).InterimRating
|_Column : 'IndicativeRating'
| |_Var(Limit1).IndicativeRating
|_Column : 'PreModerated'
| |_Var(Limit1).PreModerated
|_Column : 'ModeratedRating'
| |_Var(Limit1).ModeratedRating
|_Column : 'FinalRating'
| |_Var(Limit1).FinalRating
|_Column : 'ReviewPeriodName'
|_Var(Limit1).ReviewPeriodName}
The big different is in the oData version it's passing in the paramaters:
|_Parameters | |_p__linq__0 : Edm.Int32
Whereas the dbcontext version the parameters are just passed in directtly i.e. "10"