WebApi oData генерирует несовместимый SQL
Я получаю сообщение об ошибке:
Параметры в качестве аргументов для подпункта TOP и вложенного предложения LIMIT в запросе или LimitExpression в дереве команд не поддерживаются в версиях SQL Server ранее, чем SQL Server 2005.
При использовании WebAPI + oData при выполнении Take() в LinqPad или через http:
http://localhost:8080/odata/sample()?$top=10
Однако, если я запускаю Take() против DbContext напрямую, он работает нормально. Поэтому я предполагаю, что магия oData создает некоторый linq / sql, который не поддерживается в моей настройке.
У меня есть сложности с тем, что EDMX необходимо настроить для работы в режиме совместимости 80 (SQL 2000).
И что я использую составной ключ в ASP.NET Web API OData (но теперь я понимаю, что это рекомендуемый подход из другого поста, поэтому, вероятно, это не проблема).
И это исходит из вида, а не из таблицы.
И я использую WebApi 1 (не 2).
Есть ли способ переопределить генерируемый SQL, чтобы я мог избежать несовместимого SQL?
РЕДАКТИРОВАТЬ:
Пройдя через webapi (а затем и код структуры сущностей) - полезный опыт, я нашел строку, которая выдает исключение:
В \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);
}
Но я все еще не уверен, почему я могу легко сделать OrderBy().Take() против контекста, но версия oData терпит неудачу.
Мне удалось сравнить DbQueryCommandTree, сгенерированный oData, с эквивалентом, сгенерированным при запросе dbContext.
Версия oData:
{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}
Версия при прямом нажатии на dbContext
{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}
Большая разница в версии oData, которую он передает в параметрах:
|_Parameters | |_p__linq__0 : Edm.Int32
В то время как в версии dbcontext параметры просто передаются напрямую, т. Е. "10"
2 ответа
После того, как копать так глубоко, как родинка в яме (немного сошел с ума).
Я наткнулся на этот драгоценный камень:
public static IQueryable Take(IQueryable query, int count, Type type, bool parameterize)
{
MethodInfo takeMethod = ExpressionHelperMethods.QueryableTakeGeneric.MakeGenericMethod(type);
Expression takeValueExpression = parameterize ? LinqParameterContainer.Parameterize(typeof(int), count) : Expression.Constant(count);
Expression takeQuery = Expression.Call(null, takeMethod, new[] { query.Expression, takeValueExpression });
return query.Provider.CreateQuery(takeQuery);
}
Внутри ExpressionHelpers.cs
Я хотел знать, каков был эффект изменения paramterize:
Expression takeValueExpression = parameterize ? LinqParameterContainer.Parameterize(typeof(int), count) : Expression.Constant(count);
Просматривая стек обратно, я мог видеть, что он был передан путем смешивания запроса и контекста для создания ODataQuerySettings.EnableConstantParameterization
Какое чтение Invoking Query Options Прямо я могу переопределить в контроллере (отлично!)
Таким образом, исправление просто следующее в контроллере:
public IQueryable<T> Get(ODataQueryOptions<T> options)
{
ODataQuerySettings settings = new ODataQuerySettings()
{
EnableConstantParameterization = false
};
IQueryable results = options.ApplyTo(db.T.AsQueryable(), settings);
return results as IQueryable<T>;
}
Алекс, здорово, что ты уже нашел решение этой проблемы. Мы добавили этот параметр конфигурации, EnableConstantParameterization`, для повышения производительности кэша EF и SQL-запросов. Мы включили его по умолчанию, так как мы увидели действительно хорошие улучшения производительности с этим включенным. К сожалению, как вы уже заметили, он может не работать со старыми версиями SQL-сервера.
Вы можете отключить этот параметр, используя ODataQuerySettings.EnableConstantParameterization если вы используете ODataQueryOptions, Если вы используете QueryableAttributeВы можете отключить это, установив [QueryableAttribute(EnableConstantParameterization = false)],