How-to-use-OData-in-

How to use OData in .net core?

OData stands for Open Data Protocol which helps to build and consuming of RESTFul APIs. It is an ISO/IEC-approved and OASIS standard. OData will take care of various approaches to RESTful API like Status codes, URL conventions, request and response headers, media types, query options, payload formats, etc. so as a developer you can only focus on your business logic. It provides instructions for defining functions/actions for reusable procedures, tracking changes, and sending asynchronous/batch requests.

Let’s get started by creating one sample which describes the use of OData in .net core API.

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"));
      
      		}
      
      		});

Hire .NET Developers

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:
      • 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.
      • NameOperatorUse
        Equal  eq /Odata/Product?$filter=Name eq ‘IQZ3’
        Not  Equalne/Odata/Product?$filter=Name ne ‘IQZ3’
        Less thanlt/Odata/Product?$filter=Price lt 20000
        Less than or equalle /Odata/Product?$filter=Price le 20000
        Greater thangt/Odata/Product?$filter=Price gt 15000
        Greater than or equalge/Odata/Product?$filter=Price gt 15000
        Logical Oror/Odata/Product?$filter=Price gt 15000 or Price le 20000
        Logical andand/Odata/Product?$filter=Price gt 15000 and Price le 20000
        Logical notnot/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
0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply