为什么在使用OData选项与Entity Framework DBContext时缺少客户端结果? [英] Why are there missing client-side results when using OData options with Entity Framework DBContext?
问题描述
给定以下URL路径:
$ b$ b
/ api / Users?$ top = 10& $ skip = 10
给定以下控制器操作:
[Queryable(AllowedQueryOptions = AllowedQueryOptions.All )]
public IEnumerable< USER> Get(ODataQueryOptions< USER> options)
{
var dbContext = new ATMS.DAL.AtmsContext();
// var ret = options.ApplyTo(dbContext.USERS).Cast< USER>()。ToArray(); //返回10行,但客户端看到0
var ret = dbContext.USERS.ToArray(); //返回所有记录,过滤结果到客户端
return ret;
}
当使用 ret = options.ApplyTo(dbContext。 USERS).Cast< USER>()。ToArray();
从SQL Server Profiler我看到执行适当的查询,并通过Entity Framework从数据库返回10个结果。
ret
返回一个10个数组,但是问题是客户端的响应中没有包含任何数据: / p>
{
odata.metadata:http:// localhost:59337 / api / $ metadata# ,value:[
]
}
当使用 ret = dbContext.USERS.ToArray()时, ;
前10个结果和跳过正确应用,但只是因为从数据库返回了所有结果,并且此后应用了过滤,这不是我想要的
在我的WebApiConfig.cs中有 config.EnableQuerySupport();
。
添加 PageSize
属性无效:
[Queryable(AllowedQueryOptions = AllowedQueryOptions.All,PageSize = 10)]
code> IQueryable< User> 也没有效果。
返回 PageResult< USER>
- 无变化:
IQueryable results = options.ApplyTo(dbContext.USERS.AsQueryable(),settings);
return new PageResult< USER>(
results as IEnumerable< USER>,
Request.GetNextPageLink(),
Request.GetInlineCount());
- 更多信息 -
使用 / api / Users?$ top = 10& $ skip = 0
,如果我在action方法的开头设置一个断点, Skip.RawValue,并继续执行,我得到预期的10个结果:
看起来不知何故,结果将受到额外的跳过,这可能是为什么不显示结果。 p>
当 http:// localhost:59337 / api / Users?$ top = 10& $ skip = 9
,则显示第2页的倒数第二个结果 - 只有一个结果。
我缺少什么才能使此工作正常, c >和
ODataQueryOptions< T>
。根据是否要手动控制应用查询选项( ODataQueryOptions
)或自动进行( QueryableAttribute
)。
您有两个选项:
IEnumerable< USER> Get(ODataQueryOptions< USER> options)
{
var dbContext = new ATMS.DAL.AtmsContext();
var ret = options.ApplyTo(dbContext.USERS).Cast< USER>();
return ret;
}
或
[Queryable]
public IEnumerable< USER> Get(ODataQueryOptions< USER> options)
{
var dbContext = new ATMS.DAL.AtmsContext();
var ret = dbContext.USERS;
return ret;
}
你看到这种行为的原因是你应用查询两次,一次使用 ODataQueryOptions< T> .ApplyTo
,然后再次通过 QueryableAttribute
。
OData and Entity Framework are suppose to work well together, in that OData options will be passed to the EF db context to filter queries - ie. return only the number of records requested from the server as not to inflate the payload rather than all the records then filter.
Given the following URL path:
/api/Users?$top=10&$skip=10
Given the following controller action:
[Queryable(AllowedQueryOptions = AllowedQueryOptions.All)]
public IEnumerable<USER> Get(ODataQueryOptions<USER> options)
{
var dbContext = new ATMS.DAL.AtmsContext();
//var ret = options.ApplyTo(dbContext.USERS).Cast<USER>().ToArray(); // returns 10 rows but client sees 0
var ret = dbContext.USERS.ToArray(); // returns all records, filtered results to client
return ret;
}
When using ret = options.ApplyTo(dbContext.USERS).Cast<USER>().ToArray();
, from SQL Server Profiler I see that the appropriate query is executed and 10 results are returned from the db through Entity Framework.
An array of 10 items is returned by ret
however the problem is that no data is included in the response to the client:
{
"odata.metadata":"http://localhost:59337/api/$metadata#Users","value":[
]
}
When no skip is specified, the top 10 results are returned - obviously not useful for paging.
When using ret = dbContext.USERS.ToArray();
the top 10 results and skip are properly applied, but only because ALL of the results are returned from the database, and filtering applied thereafter, which is not what I am trying to achieve.
In my WebApiConfig.cs I have config.EnableQuerySupport();
.
Adding a PageSize
attribute has no effect:
[Queryable(AllowedQueryOptions = AllowedQueryOptions.All, PageSize = 10)]
Returning IQueryable<User>
also has no effect.
Returning PageResult<USER>
- no change:
IQueryable results = options.ApplyTo(dbContext.USERS.AsQueryable(), settings);
return new PageResult<USER>(
results as IEnumerable<USER>,
Request.GetNextPageLink(),
Request.GetInlineCount());
-- More Info --
Using /api/Users?$top=10&$skip=0
, if I set a breakpoint at the beginning of the action method and while debugging in Visual Studio I set the Skip.RawValue, and continue execution, I get the 10 results expected:
It appears that somehow the results are being subject to an additional skip, which may be why no results are displayed.
When http://localhost:59337/api/Users?$top=10&$skip=9
is used, the second-last result of page 2 is displayed - only one result.
What am I missing to get this working properly, and has anyone else experienced this?
You don't mix and match QueryableAttribute
and ODataQueryOptions<T>
. Pick one depending on whether you want manual control over applying the query options (ODataQueryOptions<T>
) or make it happen automatically (QueryableAttribute
).
You have two options,
public IEnumerable<USER> Get(ODataQueryOptions<USER> options)
{
var dbContext = new ATMS.DAL.AtmsContext();
var ret = options.ApplyTo(dbContext.USERS).Cast<USER>();
return ret;
}
or
[Queryable]
public IEnumerable<USER> Get(ODataQueryOptions<USER> options)
{
var dbContext = new ATMS.DAL.AtmsContext();
var ret = dbContext.USERS;
return ret;
}
The reason you are seeing that behavior is that you are applying the query twice, once using ODataQueryOptions<T>.ApplyTo
and then again through QueryableAttribute
.
这篇关于为什么在使用OData选项与Entity Framework DBContext时缺少客户端结果?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!