How to use OData in .net core?

Create a sample project

Create a sample project of Web API in visual studio or VS code.

Install the packages

• In Visual Studio go to Nuget Package Manager and install Microsoft.AspNetCore.OData.
• In VS code, go to terminal and execute dotnet add package Microsoft.AspNetCore.OData to add OData packages in a project.

OData using 7.5 and older versions

• Register OData

  • Go to Startup.cs and register OData in ConfigureServices method like below and also manage route for OData and build EDM model to manage OData batch in response. We also need to enable some standard query commands like :
    • Select -> to manage select using a query.
    • Filter -> manage filter on response.
    • Expand-> get a child and parent data into a response.
    • Count-> get count of data.
    • MaxTop-> specify a maximum number of data. Eg. If you specify maxpTop(100) then default it will only return 100 raws.
    • Order by -> to manage order by on a query on a query.

  • public class Startup
    		{
    		public void ConfigureServices(IServiceCollection services)
    		{
    		services.AddOData();
    		}
    
    		public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
    		{
    		app.UseEndpoints(endpoints =>
    		{
    		endpoints.MapODataRoute("OData","api", GetModel());
    		endpoints.Select().Expand().Count().SkipToken()
    		.MaxTop(100).Filter().OrderBy();
    		endpoints.MapControllers();
    		});
    		}
    
    		private IEdmModel GetModel()
    		{
    		ODataConventionModelBuilder builder = new ODataConventionModelBuilder();
    		builder.EntitySet<Product>("Product");
    		builder.EntitySet<Category>("Category");
    		return builder.GetEdmModel();
    		}
    		}
    
    		After registering Odata on the Startup file, now need to just manage the OData attribute over API method like below in controller.
    
    		[HttpGet]
    		[EnableQuery]
    		public IQueryable<Product> GetProducts()
    		{
    		return context.Products;
    		}

  • Now execute API on a browser or in postman to check the response.

• Swagger error with OData

  • If you are using OData with Swagger then it will break Swagger UI because Swagger by default cannot handle the input and output format of OData. So to fix this issue need to define input and output formatter for OData in Configure services method of Startup.cs

  • using Microsoft.AspNetCore.Mvc.Formatters;
    
    		using Microsoft.Net.Http.Headers;
    
    		services.AddMvcCore(options =>
    
    		{
    
    		foreach (var outputFormatter in
    
    		options.OutputFormatters.OfType<OutputFormatter>().Where(x =>
    
    		x.SupportedMediaTypes.Count == 0))
    
    		{
    
    		outputFormatter.SupportedMediaTypes.Add(
    
    		new MediaTypeHeaderValue(
    
    		"application/prs.odatatestxx-odata"));
    
    		}
    
    		foreach (var inputFormatter in
    
    		options.InputFormatters.OfType<InputFormatter>().Where(
    
    		x => x.SupportedMediaTypes.Count == 0))
    
    		{
    
    		inputFormatter.SupportedMediaTypes.Add(
    
    		new MediaTypeHeaderValue(
    
    		"application/prs.odatatestxx-odata"));
    
    		}
    
    		});

OData using 8 or the latest version

For the latest version the configuration is now changed, MapODataRoute is gone but AddOData is still there. Check the below configuration for the latest version, now need to just manage the basic setting in the ConfigureServices method of the startup file.

public void ConfigureServices(IServiceCollection services)
		{
		services.AddControllers().AddOData(option =>
		{
		option.Select();
		option.Expand();
		option.Filter();
		option.Count();
		option.SetMaxTop(100);
		option.SkipToken();
		option.AddRouteComponents("Odata", GetModel());
		}).AddNewtonsoftJson();
		services.AddDbContext<ProductDBContext>(option => option.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")
		));
		services.AddSwaggerGen(c =>
		{
		c.SwaggerDoc("v1", new OpenApiInfo { Title = "ODataDemo", Version = "v1" });
		});
		}
		public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
		{
		if (env.IsDevelopment())
		{
		app.UseDeveloperExceptionPage();
		app.UseSwagger();
		app.UseSwaggerUI(c => c.SwaggerEndpoint("/swagger/v1/swagger.json", "ODataDemo v1"));
		}
		app.UseHttpsRedirection();
		app.UseRouting();
		app.UseAuthorization();
		app.UseEndpoints(endpoints =>
		{
		endpoints.MapControllers();
		});
		}
		private IEdmModel GetModel()
		{
		ODataConventionModelBuilder builder = new ODataConventionModelBuilder();
		builder.EntitySet<Product>("Product");
		builder.EntitySet<Category>("Category");
		return builder.GetEdmModel();
		}

System Query Option

It is a query string parameter used to control the amount and order of data returned by API. All system query options are optionally prefixed with a dollar ($) character. It should be in lower case and prefix with $ to use in a query. OData Protocol V4.01 base service support case insensitive system query option names.

System query options are $expand, $filter, $count, $select, $top, $skip, and $orderby.

• • Select

    • $select query options used to request a specific set of properties of each entity. It allows selecting multiple properties so a client can provide multiple values with a comma-separated list of selection clauses.
    • Example:
      • /api/Product?$select=Name,Price
    • Response:

    • {
      
      		"@odata.context": "https://localhost:44360/Odata/$metadata#Product(Name,Price)",
      
      		"value": [
      
      		{
      
      		"Name": "IQZ3",
      
      		"Price": 20000
      
      		},
      
      		{
      
      		"Name": "Nokia 6",
      
      		"Price": 15000
      
      		}
      
      		]
      
      		}

  • Filter

    • As the name suggests it is used to filter a collation of data that will be returned by the requested URL. It will only return data that match provided filter. Its support major operator is listed below.

    • Name	Operator	Use
      Equal	eq	/Odata/Product?$filter=Name eq ‘IQZ3’
      Not  Equal	ne	/Odata/Product?$filter=Name ne ‘IQZ3’
      Less than	lt	/Odata/Product?$filter=Price lt 20000
      Less than or equal	le	/Odata/Product?$filter=Price le 20000
      Greater than	gt	/Odata/Product?$filter=Price gt 15000
      Greater than or equal	ge	/Odata/Product?$filter=Price gt 15000
      Logical Or	or	/Odata/Product?$filter=Price gt 15000 or Price le 20000
      Logical and	and	/Odata/Product?$filter=Price gt 15000 and Price le 20000
      Logical not	not	/Odata/Product?$filter=not startswith(Name,’I’)

  • In query itself, client can also use arithmetic operator like addition (add), subtraction (sub), multiplication (mul), Division (div) and modulo (mod).
    • /Odata/Product?$filter=Price add 1 gt 20000
    • /Odata/Product?$filter=Price sub 1 gt 20000
    • /Odata/Product?$filter=Price mul 3 gt 20000
    • /Odata/Product?$filter=Price div 2 gt 20000
    • /Odata/Product?$filter=Price mod 5 eq 0
  • Other than the above operator, the filter will also allow to use of string function, date function match function, and type function.
    • String functions: filter will allow to use of major available string functions like lower, upper, substring, indexOf, startswith, endswith, length, trim, concat and replace, etc
      • /Odata/Product?$filter=tolower(Name) eq ‘nokia’
      • /Odata/Product?$filter=toupper(Name) eq ‘NOKIA’
      • /Odata/Product?$filter=trim(Name) eq ‘Nokia’
      • /Odata/Product?$filter=concat(concat(Brand,’,’), Name) eq ‘Vivo V7’
      • /Odata/Product?$filter=replace(Name,’ ‘,’’) eq ‘Nokia’
      • /Odata/Product?$filter=indexof(Name,’Nokia’) eq 1
      • /Odata/Product?$filter=length(Name) eq 6
      • /Odata/Product?$filter=startswith(Name, ‘Nokia’) eq true
      • /Odata/Product?$filter=endswith(Name, ‘z’) eq true
      • /Odata/Product?$filter=substringof(‘Nokia’,Name) eq ‘Nokia’
      • /Odata/Product?$filter=substring(Name,1) eq ‘Nokia’
      • /Odata/Product?$filter=substring(Name,1,2) eq ‘No’
    • Date Functions: Below is the date functions that are used with a filter to get data based on the date filter.
      • /Odata/Order?$filter=day(OrderDate) eq 5
      • /Odata/Order?$filter=hour(OrderDate) eq 10
      • /Odata/Order?$filter=minute(OrderDate) eq 10
      • /Odata/Order?$filter=month(OrderDate) eq 2
      • /Odata/Order?$filter=second(OrderDate) eq 0
      • /Odata/Order?$filter=year(OrderDate) eq 2022
    • Type Functions :
      • /Odata/Product?$filter=isof(Name, ‘Edm.String’) eq 0
    • Math Functions: Filter will also allow to use of math functions like round, floor, ceiling.
      • /Odata/Order?$filter=round(OrderPrice) eq 1000

  • Expand

    • Using expand client can request a related entity with a defined entity in the result set, so it will include parent and child entity with a specific entity.
      • Example: /Odata/Product?$expand=category, Order
    • It will also allow to use of other query options with expanded entity
      • /Odata/Product?$expand=Order($filter=month(orderDate) eq 2)

• • Top, Skip, and Count

    • $top will return a specific number of items from the result set and $skip will skip the specific number of items from the result set and will not include in the result. Using $top and $skip, client can manage pagination. $count used to return a count of the total number of the item along with result collection.
      • /Odata/Product?$top(20)
      • /Odata/Product?$skip(20)
      • /Odata/Product?$count=true

  • OrderBy

    • Order by allow to order return collection from service.
      • /Odata/Product?$orderby=Name asc, price desc