OData.QueryBuilder

Library for creating complex OData queries (OData version 4.01) based on data models with linq syntax.

Benefits

• Support:
  • nested extenders with a choice of filtering
  • operators
    • in
    • any
    • all
  • functions
    • date
      • date
    • string and collection
      • contains
      • substringof (deprecated)
      • toupper
      • tolower
      • concat
      • indexof
      • startswith
      • length
    • type
      • cast
  • sorting by several fields with indication of direction
  • dynamic properties

Installation

To install OData.QueryBuilder from Visual Studio, find OData.QueryBuilder in the NuGet package manager user interface or enter the following command in the package manager console:

Install-Package OData.QueryBuilder

To add a link to the main dotnet project, run the following command line:

dotnet add package OData.QueryBuilder

Usage

1. Build instance

   As soon as possible, create a new instance of the OData.QueryBuilder object indicating the data models and the optional base path:

   var odataQueryBuilder = new ODataQueryBuilder<Your OData root model>(<Your base url>?);
   
   // specify the resource for which the request will be built
   odataQueryBuilder.For<Your OData entity model>(s => s.ODataEntity)
   

   :information_source: OData.Query Builder assumes you are using OData Connected Service and you have a root model otherwise use:

   var odataQueryBuilder = new ODataQueryBuilder(<Your base url>?);
   
   // specify the resource for which the request will be built
   odataQueryBuilder.For<Your OData entity model>("ODataEntity")
   

2. Select request type

   The builder allows you to build queries on the key and the list:

   • ByKey
     • expand
       • filter
       • select
       • orderby
       • orderby desc
       • skip
       • top
       • count
     • select
   • ByList
     • expand
       • filter
       • select
       • orderby
       • orderby desc
       • top
     • filter
     • select
     • orderby
     • orderby desc
     • skip
     • top
     • count

3. Get Uri request or collection of operators from the builder

   odataQueryBuilder.ToUri()
   odataQueryBuilder.ToDictionary()
   

Fluent api

<a name="ByKey"/> ByKey

var uri = new ODataQueryBuilder<ODataInfoContainer>("http://mock/odata")
    .For<ODataTypeEntity>(s => s.ODataType)
    .ByKey(223123123)
    .ToUri()

http://mock/odata/ODataType(223123123)

var uri = new ODataQueryBuilder<ODataInfoContainer>("http://mock/odata")
    .For<ODataTypeEntity>(s => s.ODataType)
    .ByKey("223123123")
    .ToUri()

http://mock/odata/ODataType("223123123")

var uri = new ODataQueryBuilder<ODataInfoContainer>("http://mock/odata")
    .For<ODataTypeEntity>(s => s.ODataType)
    .ByKey(223123123)
    .For<ODataKindEntity>(s => s.ODataKind)
    .ByKey(223123123)
    .ToUri()

http://mock/odata/ODataType(223123123)/ODataKind(223123123)

<a name="ByList"/> ByList

var uri = new ODataQueryBuilder<ODataInfoContainer>("http://mock/odata")
    .For<ODataTypeEntity>(s => s.ODataType)
    .ByList()
    .ToUri()

http://mock/odata/ODataType

Usage options

<a name="select"/> select

.Select(s => s.Id)

$select=Id

.Select(s => new { s.Id, s.Sum, s.Type })

$select=Id,Sum,Type

<a name="expand"/> expand

.Expand(s => s.BaseType)

$expand=BaseType

.Expand(s => new { s.BaseType, s.ODataKind })

$expand=BaseType,ODataKind

.Expand(f => f
    .For<ODataKindEntity>(s => s.ODataKind)
    .Expand(s=> s
        .For<ODataCodeEntity>(s => s.ODataCode)
        .Filter(s => s.IdKind == 1)
        .OrderBy(s => s.IdKind)
        .Top(1)
        .Select(s => s.IdCode));
})

$expand=ODataKind($expand=ODataCode($filter=IdKind eq 1;$orderby=IdKind;$top=1;$select=IdCode))

<a name="filter"/> filter

.Filter(s => s.ODataKind.ODataCode.Code >= "test_code" || s.IdType >= 5)

$filter=ODataKind/ODataCode/IdCode eq 'test_code' or IdType ge 5

.Filter(s => s.IdRule != default(int?) && s.IdRule == null)

$filter=IdRule ne null and IdRule eq null

.Filter(s => s.ODataKind.OpenDate == DateTime.Today || s.ODataKind.OpenDate == new DateTime(2019, 7, 9)) || s.ODataKind.OpenDate == DateTime.Now)

$filter=ODataKind/OpenDate eq 2019-02-09T00:00:00Z or ODataKind/OpenDate eq 2019-02-09T00:00:00Z or ODataKind/OpenDate eq 2019-02-09T15:10:20Z

.Filter(s => s.IsActive && s.IsOpen == true && !s.ODataKind.ODataCode.IdActive)

$filter=IsActive and IsOpen eq true and not ODataKind/ODataCode/IdActive

.Filter(s => s.ODataKind.Color == ColorEnum.Blue)

$filter=ODataKind/Color eq 2

:information_source: Use parenthesis in filter

var constStrIds = new[] { "123", "512" };
var constValue = 3;
...
.Filter((s, f, o) => s.IdRule == constValue
    && s.IsActive
    && (f.Date(s.EndDate.Value) == default(DateTimeOffset?) || s.EndDate > DateTime.Today)
    && (f.Date((DateTimeOffset)s.BeginDate) != default(DateTime?) || f.Date((DateTime)s.BeginDate) <= DateTime.Now)
    && o.In(s.ODataKind.ODataCode.Code, constStrIds), useParenthesis: true)

$filter=(((IdRule eq 3 and IsActive) and (date(EndDate) eq null or EndDate gt 2020-08-29T00:00:00Z)) and (date(BeginDate) ne null or date(BeginDate) le 2020-08-29T18:09:15Z)) and ODataKind/ODataCode/Code in ('123','512')

<a name="orderby"/> orderby

.OrderBy(s => s.IdType)

$orderby=IdType asc

.OrderBy(s => new { s.IdType, s.Sum })

$orderby=IdType,Sum asc

.OrderBy((entity, sort) => sort
    .Ascending(entity.BeginDate)
    .Descending(entity.EndDate)
    .Ascending(entity.IdRule)
    .Ascending(entity.Sum)
    .Descending(entity.ODataKind.OpenDate))

$orderby=BeginDate asc,EndDate desc,IdRule asc,Sum asc,ODataKind/OpenDate desc

<a name="orderbydesc"/> orderby desc

.OrderByDescending(s => s.IdType)

$orderby=IdType desc

.OrderByDescending(s => new { s.IdType, s.Sum })

$orderby=IdType,Sum desc

<a name="count"/> count

.Count()

$count=true

.Count(false)

$count=false

<a name="skip"/> skip

.Skip(12)

$skip=12

<a name="top"/> top

.Top(100)

$top=100

Usage operators

<a name="in"/> in

.Filter((s, f, o) => o.In(s.ODataKind.ODataCode.Code, new[] { "123", "512" }) && o.In(s.IdType, new[] { 123, 512 }))

$filter=ODataKind/ODataCode/Code in ('123','512') and IdType in (123,512)

<a name="any"/> any

.Filter((s, f, o) => o.Any(s.ODataKind.ODataCodes, v => v.IdCode == 1)

$filter=ODataKind/ODataCodes/any(v:v/IdCode eq 1)

<a name="all"/> all

.Filter((s, f, o) => o.All(s.ODataKind.ODataCodes, v => v.IdActive))

$filter=ODataKind/ODataCodes/all(v:v/IdActive)

Usage date functions

<a name="date"/> date

.Filter((s, f) => f.Date(s.Open) == DateTime.Today)

$filter=date(Open) eq 2019-02-09T00:00:00Z

Usage string and collections functions

<a name="contains"/> contains

.Filter((s, f) => f.Contains(s.ODataKind.ODataCode.Code, "W"))

$filter=contains(ODataKind/ODataCode/Code,'W')

<a name="substringof"/> substringof

.Filter((s, f) => f.SubstringOf("W", s.ODataKind.ODataCode.Code))

$filter=substringof('W',ODataKind/ODataCode/Code)

<a name="toupper"/> toupper

.Filter((s, f) => f.ToUpper(s.ODataKind.ODataCode.Code) == "TEST_CODE")

$filter=toupper(ODataKind/ODataCode/Code) eq 'TEST_CODE'

<a name="tolower"/> tolower

.Filter((s, f) => f.ToLower(s.ODataKind.ODataCode.Code) == "test_code")

$filter=tolower(ODataKind/ODataCode/Code) eq 'test_code'

<a name="concat"/> concat

.Filter((s, f) => f.Concat(s.ODataKind.ODataCode.Code, "_1") == "test_code_1")

$filter=concat(ODataKind/ODataCode/Code, '_1') eq 'test_code_1'

<a name="indexof"/> indexof

.Filter((s, f) => f.IndexOf(s.ODataKind.ODataCode.Code, "testCode") == 1)

$filter=indexof(ODataKind/ODataCode/Code,'testCode') eq 1

<a name="startswith"/> startswith

.Filter((s, f) => f.StartsWith(s.ODataKind.ODataCode.Code, "test"))

$filter=startswith(ODataKind/ODataCode/Code,'test')

<a name="length"/> length

.Filter((s, f) => f.Length(s.ODataKind.ODataCode.Code) > 0)

$filter=length(ODataKind/ODataCode/Code) gt 0