AspNetCoreOData
AspNetCoreOData copied to clipboard
OData
$Count is not working with .NET Core and ODataQueryOptions
I am using Odata with .NET 5.0. Odata version is : (Microsoft.AspNetCore.OData\8.0.1)
$Count is not working in my each request.
http://localhost:33451/odata/Users?$count=true http://localhost:33451/odata/Users?$skip=20&$top=10&$count=true http://localhost:33451/odata/Users?$filter=Salary gt 20000 and Salary le 50000&$count=true http://localhost:33451/odata/Users?$skip=20&$top=10&$filter=Salary gt 20000 and Salary le 50000&$count=true
I am using ODataQueryOptions without EnableQuery.
I also gone through to remove "ApiController" and [Route("[controller]")] but it was not working.
using Comman.TestData;
using Domain;
using Domain.BO;
using Domain.Service;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.OData.Query;
using System;
using System.Collections.Generic;
using System.Linq;
using AspNetCoreOData.Helper;
namespace AspNetCoreOData.Controllers
{
// [ApiController]
// [Route("[controller]")]
public class UsersController : ControllerBase
{
private readonly IMapperSession session;
// 0. Decalare constant - 2000
private const int batchSize = 2000;
public UsersController(IMapperSession session)
{
this.session = session;
}
[HttpGet]
// [EnableQuery()]
public IQueryable<User> Get(ODataQueryOptions options)
{
// 1. Get all IQuerable<User> from dao.
var userList = session.Users;
// 2. Define Include/ExcludeIDs more than 5000
List<int> includeIdsList = TestData.GetIncludeExcludeIdList();
IQueryable<User> results = Enumerable.Empty<User>().AsQueryable();
for (int i = 0; i < includeIdsList.Count; i = i + batchSize)
{
// 3. Split Include/ ExcludeIds in batch(constant - options.count)
var items = includeIdsList.Skip(i).Take(batchSize).ToArray();
IQueryable<User> getGridData = IQuerableHelper.GetGridData(userList, items, null);
if (options.Filter != null)
{
// 4.Apply Filter with Include / ExcludeIds in loop of batch.count and total includeId/ ExcludeId
results = results.Concat((IQueryable<User>)options.Filter.ApplyTo(getGridData, new ODataQuerySettings()));
}
else
{
results = results.Concat(getGridData);
}
}
if (options.OrderBy != null)
{
results = options.OrderBy.ApplyTo(results); //perform sort
}
if (options.Skip != null)
{
results = options.Skip.ApplyTo(results, new ODataQuerySettings()); //perform skip
}
if (options.Top != null)
{
results = options.Top.ApplyTo(results, new ODataQuerySettings()); //perform take
}
return results;
}
}
}
public class Startup
{
public Startup(IConfiguration configuration)
{
Configuration = configuration;
}
public IConfiguration Configuration { get; }
// This method gets called by the runtime. Use this method to add services to the container.
public void ConfigureServices(IServiceCollection services)
{
var connStr = Configuration.GetConnectionString("DefaultConnection");
services.AddNHibernate(connStr);
//services.AddControllers()
// .AddOData(
// opt => opt.Select()
// .SkipToken()
// .SetMaxTop(100)
// .Count()
// .Filter()
// .AddRouteComponents("odata", GetEdmModel()));
services.AddControllers().AddOData(opt => opt.Select().Expand().Filter().OrderBy().SetMaxTop(null).SkipToken().Count().AddRouteComponents("odata", GetEdmModel()));
services.AddSwaggerGen(c =>
{
c.SwaggerDoc("v1", new OpenApiInfo { Title = "AspNetCoreOData", Version = "v1" });
});
// services.AddControllersWithViews();
}
IEdmModel GetEdmModel()
{
var odataBuilder = new ODataConventionModelBuilder();
odataBuilder.EntitySet<User>("Users");
odataBuilder.EntitySet<User>("Employee");
odataBuilder.EntitySet<WeatherForecast>(nameof(WeatherForecast));
return odataBuilder.GetEdmModel();
}
// This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
{
if (env.IsDevelopment())
{
app.UseDeveloperExceptionPage();
app.UseSwagger();
app.UseSwaggerUI(c => c.SwaggerEndpoint("/swagger/v1/swagger.json", "AspNetCoreOData v1"));
}
app.UseRouting();
app.UseAuthorization();
app.UseEndpoints(endpoints =>
{
endpoints.MapControllers();
});
}
}
Request URL : http://localhost:33451/odata/users?$skip=10&$top=10&$count=true **Response : **
{
"@odata.context": "http://localhost:33451/odata/$metadata#Users",
"value": [
{
"UserID": 7238,
"FirstName": "adam",
"LastName": "adkins",
"Designation": "Business Administrator",
"Email": "[email protected]",
"Mobile": "1342532785",
"Gender": "Female",
"Salary": 19144.00,
"CreatedAt": "2010-07-25T00:00:00+05:30"
},
{
"UserID": 7239,
"FirstName": "adolph",
"LastName": "adams",
"Designation": "Software Developer",
"Email": "[email protected]",
"Mobile": "0335938520",
"Gender": "Male",
"Salary": 35966.00,
"CreatedAt": "2010-04-15T00:00:00+05:30"
},
{
"UserID": 7240,
"FirstName": "Alpesh",
"LastName": "adkins",
"Designation": "Frontend Developer",
"Email": "[email protected]",
"Mobile": "0620974648",
"Gender": "Female",
"Salary": 23516.00,
"CreatedAt": "2012-02-26T00:00:00+05:30"
},
{
"UserID": 7241,
"FirstName": "abel",
"LastName": "acosta",
"Designation": "Technical Lead",
"Email": "[email protected]",
"Mobile": "0000082508",
"Gender": "Female",
"Salary": 36391.00,
"CreatedAt": "2020-07-24T00:00:00+05:30"
},
{
"UserID": 7242,
"FirstName": "abe",
"LastName": "abbott",
"Designation": "Software Developer",
"Email": "[email protected]",
"Mobile": "4621393553",
"Gender": "Male",
"Salary": 19025.00,
"CreatedAt": "2018-07-18T00:00:00+05:30"
},
{
"UserID": 7243,
"FirstName": "abby",
"LastName": "adams",
"Designation": "Manager",
"Email": "[email protected]",
"Mobile": "3370941413",
"Gender": "Female",
"Salary": 11530.00,
"CreatedAt": "2011-02-20T00:00:00+05:30"
},
{
"UserID": 7244,
"FirstName": "adolfo",
"LastName": "adams",
"Designation": "Manager",
"Email": "[email protected]",
"Mobile": "5541084362",
"Gender": "Female",
"Salary": 38385.00,
"CreatedAt": "2015-05-01T00:00:00+05:30"
},
{
"UserID": 7245,
"FirstName": "Maulik",
"LastName": "acosta",
"Designation": "QA",
"Email": "[email protected]",
"Mobile": "0574550885",
"Gender": "Female",
"Salary": 10403.00,
"CreatedAt": "2020-05-01T00:00:00+05:30"
},
{
"UserID": 7246,
"FirstName": "adam",
"LastName": "acosta",
"Designation": "Software Developer",
"Email": "[email protected]",
"Mobile": "0072052077",
"Gender": "Male",
"Salary": 36698.00,
"CreatedAt": "2015-03-14T00:00:00+05:30"
},
{
"UserID": 7247,
"FirstName": "Alpesh",
"LastName": "abbott",
"Designation": "Technical Lead",
"Email": "[email protected]",
"Mobile": "7722694832",
"Gender": "Male",
"Salary": 10209.00,
"CreatedAt": "2013-04-20T00:00:00+05:30"
}
]
}
Hey @asvaghela
I have looked the sample code you shared.
For the UsersController, inherit from ODataController instead of the ControllerBase.
Sample code below:
public class UsersController : ODataController
{
// Controller methods
[EnableQuery]
public IQueryable<User> Get()
{
return _db.Users.AsQueryable<User>();
}
}
@KenitoInc @xuzhg Same issue is happening with me. We don't see the odata.Count property. Please can you let me know when this will be fixed ?
Same here! only I am on dotnet 3.1 and odata 8.0.1. Using [EnableQuery] and EdmModel at startup
Same here. Doesn't work on net6, doesn't even show @odata.context as it used to in core 3.1
Same here. Doesn't work on net6, doesn't even show @odata.context as it used to in core 3.1
Same issue. The @odata.context is missing even in the case of OData attribute routing (using [ODataAttributeRouting] attribute) which contradicts to this article: Attribute Routing in ASP.NET Core OData 8.0 RC.
- .Net 6
- Microsoft.EntityFrameworkCore 6.0.1
- Microsoft.AspNetCore.OData 8.0.4 Update: It does work but only with Edm model
@DuckScapePhilip @FrozenPhoenix92 @rgudkov-uss @Cimbro @Leonhard1987 @bturner1273 @gadagrj @asvaghela
I have a simple project with the target framework net6.0. Below are the packages I installed.
Microsoft.AspNetCore.OData v8.0.8
Microsoft.EntityFrameworkCore.InMemory v6.0.3
https://github.com/KenitoInc/AspNetCoreOData8Net6
When i call GET http://localhost:5000/odata/Books?$count=true I get the response below
{
"@odata.context": "http://localhost:5000/odata/$metadata#Books",
"@odata.count": 9,
"value": [
{
"Id": 1,
"ISBN": "978-0-321-87758-1",
"Title": "Essential C#5.0",
"Author": "Mark Michaelis",
"Price": 59.99,
"Location": {
"City": "Redmond",
"Street": "156TH AVE NE",
"Postal": {
"Box": "1234",
"PostalCode": "00100"
}
}
},
{
"Id": 2,
"ISBN": "063-6-920-02371-5",
"Title": "Enterprise Games",
"Author": "Michael Hugos",
"Price": 49.99,
"Location": {
"City": "Bellevue",
"Street": "Main ST",
"Postal": {
"Box": "5554",
"PostalCode": "00200"
}
}
},
{
"Id": 3,
"ISBN": "999-6-920-02371-5",
"Title": "Purple Hibiscus",
"Author": "Chimamanda Ngozi",
"Price": 49.99,
"Location": {
"City": "Abuja",
"Street": "Main ST",
"Postal": {
"Box": "31846",
"PostalCode": "00800"
}
}
},
{
"Id": 4,
"ISBN": "777-6-920-02371-5",
"Title": "Things fall apart",
"Author": "Chinua Achebe",
"Price": 49.99,
"Location": {
"City": "Lagos",
"Street": "Main ST",
"Postal": {
"Box": "97984",
"PostalCode": "00500"
}
}
},
{
"Id": 5,
"ISBN": "777-6-920-02371-5",
"Title": "Things fall apart",
"Author": "Ngugi wa thiongo",
"Price": 49.99,
"Location": {
"City": "Nairobi",
"Street": "Moi Avenue",
"Postal": {
"Box": "1314",
"PostalCode": "90500"
}
}
},
{
"Id": 6,
"ISBN": "777-6-920-02371-5",
"Title": "Things fall apart",
"Author": "Chinua Achebe",
"Price": 49.99,
"Location": {
"City": "Lagos",
"Street": "Main ST",
"Postal": {
"Box": "97984",
"PostalCode": "00500"
}
}
},
{
"Id": 7,
"ISBN": "777-6-920-02371-5",
"Title": "Things fall apart",
"Author": "Chinua Achebe",
"Price": 49.99,
"Location": {
"City": "Lagos",
"Street": "Main ST",
"Postal": {
"Box": "97984",
"PostalCode": "00500"
}
}
},
{
"Id": 8,
"ISBN": "777-6-920-02371-5",
"Title": "Things fall apart",
"Author": "Chinua Achebe",
"Price": 49.99,
"Location": {
"City": "Lagos",
"Street": "Main ST",
"Postal": {
"Box": "97984",
"PostalCode": "00500"
}
}
},
{
"Id": 9,
"ISBN": "777-6-920-02371-5",
"Title": "Things fall apart",
"Author": "Chinua Achebe",
"Price": 49.99,
"Location": {
"City": "Lagos",
"Street": "Main ST",
"Postal": {
"Box": "97984",
"PostalCode": "00500"
}
}
}
]
}
when i call GET http://localhost:5000/odata/Books?$filter=Id lt 4&$count=true i get the response below
{
"@odata.context": "http://localhost:5000/odata/$metadata#Books",
"@odata.count": 3,
"value": [
{
"Id": 1,
"ISBN": "978-0-321-87758-1",
"Title": "Essential C#5.0",
"Author": "Mark Michaelis",
"Price": 59.99,
"Location": {
"City": "Redmond",
"Street": "156TH AVE NE",
"Postal": {
"Box": "1234",
"PostalCode": "00100"
}
}
},
{
"Id": 2,
"ISBN": "063-6-920-02371-5",
"Title": "Enterprise Games",
"Author": "Michael Hugos",
"Price": 49.99,
"Location": {
"City": "Bellevue",
"Street": "Main ST",
"Postal": {
"Box": "5554",
"PostalCode": "00200"
}
}
},
{
"Id": 3,
"ISBN": "999-6-920-02371-5",
"Title": "Purple Hibiscus",
"Author": "Chimamanda Ngozi",
"Price": 49.99,
"Location": {
"City": "Abuja",
"Street": "Main ST",
"Postal": {
"Box": "31846",
"PostalCode": "00800"
}
}
}
]
}
Kindly check if this sample project will help you resolve your issues
@asvaghela Please check that you're enabling Count query option from Startup class. Something like this...
services.AddControllers().AddOData(
options => options.Select().Filter().OrderBy().Expand().Count().SetMaxTop(null));
From a quick repro I tried, I get back @odata.count in the response. Here's the code in my controller
public class CustomersController : ODataController
{
private static List<Customer> customers = new List<Customer>(
Enumerable.Range(1, 3).Select(idx => new Customer { Id = idx, Name = $"Customer {idx}" }));
public ActionResult Get(ODataQueryOptions<Customer> queryOptions)
{
return Ok(queryOptions.ApplyTo(customers.AsQueryable()));
}
}
Request:
http://localhost/odata/Customers?$count=true
Response:
{
"@odata.context": "http://localhost:52391/odata/$metadata#Customers",
"@odata.count": 3,
"value": [
{
"Id": 1,
"Name": "Customer 1"
},
{
"Id": 2,
"Name": "Customer 2"
},
{
"Id": 3,
"Name": "Customer 3"
}
]
}
I have tried everything that is in this thread and I still have this issue.... Any new news about this problem?
I had same issue and I fixed it. It was so ridiculous. My controller was like that:
[Route("odata/[controller]")]
[Authorize(AuthenticationSchemes = TenantRequired.DefaultSchemeName)]
public class BanksController : ODataController
{
private readonly ReadModelContext _readModelContext;
private readonly ExcelService _excelService;
public BanksController(ReadModelContext readModelContext, ExcelService excelService)
_readModelContext = readModelContext;
_excelService = excelService;
}
private IQueryable<Bank> Query() => _readModelContext.Set<Bank>();
[EnableQuery]
public IQueryable<Bank> GetList()
{
return Query();
}
}
When I call http://localhost:8000/v2/banks?$count=true I got:
[
{
"bankType": null,
"branch": "45",
"accountNumber": "1567897",
"cartNumber": "4444444444444",
"shaba": null,
"accountOwner": null,
"initialBalance": 0,
"detailAccountType": null,
"code": "1001",
"description": null,
"firstName": null,
"lastName": null
},
{
"bankType": null,
"branch": null,
"accountNumber": null,
"cartNumber": null,
"shaba": null,
"accountOwner": null,
"initialBalance": 0,
"detailAccountType": null,
"code": "6",
"description": null,
"firstName": null,
"lastName": null
}
]
As you can see there is no @odata.count in response.
So I made a little change and replace GetList with Get like this:
[EnableQuery]
public IQueryable<Bank> Get()
{
return Query();
}
And it's worked! I Guess OData is a bit sensitive about name of actions!
{
"@odata.context": "http://localhost:8087/odata/$metadata#Banks",
"@odata.count": 2,
"value": [
{
"detailAccountType": null,
"code": "1001",
"description": null,
"firstName": null,
"lastName": null,
"branch": "45",
"accountNumber": "1567897",
"cartNumber": "4444444444444",
"shaba": null,
"accountOwner": null,
"initialBalance": 0.0
},
{
"detailAccountType": null,
"code": "6",
"description": null,
"firstName": null,
"lastName": null,
"branch": null,
"accountNumber": null,
"cartNumber": null,
"shaba": null,
"accountOwner": null,
"initialBalance": 0.0,
}
]
}
And don't forget adding .Count() to `AddOData' options:
.AddOData(options => options.AddRouteComponents("odata", builder.GetEdmModel())
.Select().Expand().Filter().OrderBy().Count().SetMaxTop(null));
I know it's so silly but it worth a try.
I Guess OData is a bit sensitive about name of actions!
You are absolutely right @mhaz2000 . The names used to match the route conventions are fairly strict, and that is on purpose.
Now that OData supports attribute routing, I'd recommend switching to that since it is less "magical" and gives you more flexibility.
Have the same issue
@PavelKochkin2 I suspect most of the issues are being caused by this bug:
- https://github.com/OData/AspNetCoreOData/issues/430
Post your EDM model, config and controller code if you want to double check. Also check the output of the $odata debug endpoint as documented in this project's readme.
@xuzhg any chance someone on the team could prioritize the bug above? Coming from earlier versions or even migrating a non-OData API into an OData API will stumble upon that issue and it is not immediately clear what is going on.
- First thing to check is Count() is added in config, like this
.AddOData(options => options.AddRouteComponents("odata", builder.GetEdmModel())
.Select().Expand().Filter().OrderBy().Count().SetMaxTop(null));
- If you are using attribute routing, then the value used in EntitySet should match the attribute route name.
- In Config -> var odataBuilder = new ODataConventionModelBuilder();
odataBuilder.EntitySet<User>("Users");
odataBuilder.EntityType<User>().Page(maxTopValue: 100, pageSizeValue: 100);
- Attribute api route should be -> 'odata/Users'
