ZEXSM/OData.QueryBuilder

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

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)

   ℹ️ 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

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)

ByList

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

http://mock/odata/ODataType

Usage options

select

.Select(s => s.Id)

$select=Id

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

$select=Id,Sum,Type

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))

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

ℹ️ 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')

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

orderby desc

.OrderByDescending(s => s.IdType)

$orderby=IdType desc

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

$orderby=IdType,Sum desc

count

.Count()

$count=true

.Count(false)

$count=false

skip

.Skip(12)

$skip=12

top

.Top(100)

$top=100

Usage operators

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)

any

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

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

all

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

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

Usage date functions

date

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

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

Usage string and collections functions

contains

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

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

substringof

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

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

toupper

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

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

tolower

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

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

concat

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

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

indexof

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

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

startswith

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

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

length

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

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

Usage type functions

cast

.Filter((s, f) => f.Contains(f.Cast(s.ODataKindNew.ODataCode.Code, "Edm.String"), "55"))

$filter=contains(cast(ODataKindNew/ODataCode/Code,Edm.String),'55')

Usage other functions

ConvertEnumToString

.Filter((s, f) => s.ODataKind.Color == f.ConvertEnumToString(ColorEnum.Blue))

$filter=ODataKind/Color eq 'Blue'

ConvertDateTimeToString

.Filter((s, f) => s.ODataKind.OpenDate == f.ConvertDateTimeToString(new DateTime(2019, 2, 9), "yyyy-MM-dd"))

$filter=ODataKind/OpenDate eq 2019-02-09

ConvertDateTimeOffsetToString

.Filter((s, f) => s.ODataKind.OpenDate == f.ConvertDateTimeToString(new DateTimeOffset(new DateTime(2019, 2, 9)), "yyyy-MM-dd"))

$filter=ODataKind/OpenDate eq 2019-02-09

ReplaceCharacters

var dictionary = new Dictionary<string, string> { { "&", "%26" } });
var constValue = "3 & 4";
...
.Filter((s, f) => s.ODataKind.ODataCode.Code == f.ReplaceCharacters(
    constValue,
    dictionary)

$filter=ODataKind/ODataCode/Code eq '3 %26 4'

var strings = new string[] {
    "test\\YUYYUT",
    "test1\\YUYY123"
};
...
.Filter((s, f, o) => o.In(s.ODataKind.ODataCode.Code, f.ReplaceCharacters(
    strings,
    new Dictionary<string, string>() { { @"\", "%5C" } })))

$filter=ODataKind/ODataCode/Code in ('test%5C%5CYUYYUT','test1%5C%5CYUYY123')

Using dynamic properties

For use-cases where a property which is used for queries needs to be dynamically resolved (eg. the user chooses the property name to filter on) a special method ODataProperty.FromPath<TProperty>() can be invoked in the query expressions:

...
.Filter(s => ODataProperty.FromPath<int>("IdRule") == 3))

Note that the generic type argument TProperty needs to match the type of the dynamically resolved property in the OData model.

Suppress exceptions

⚠️ May result in loss of control over the expected result.

Suppression of inclusion excludes the whole expression from the request

By default, suppression of function and operator argument exceptions is disabled. To enable it, you must pass the configuration ODataQueryBuilderOptions when initializing the builder with initialization of the SuppressExceptionOfNullOrEmptyFunctionArgs and SuppressExceptionOfNullOrEmptyOperatorArgs properties.

var builder = new ODataQueryBuilder<ODataInfoContainer>("http://mock/odata", new ODataQueryBuilderOptions {
    SuppressExceptionOfNullOrEmptyFunctionArgs = true,
    SuppressExceptionOfNullOrEmptyOperatorArgs = true,
});

var uri = builder
    .For<ODataTypeEntity>(s => s.ODataType)
    .ByList()
    .Filter((s, f, o) => o.In(s.ODataKind.ODataCode.Code, new string[0]) || o.In(s.ODataKind.ODataCode.Code, null)
        && f.Contains(s.ODataKind.ODataCode.Code, default(string)) 
        && o.In(s.IdType, new[] { 123, 512 }))
    .ToUri()

http://mock/odata/ODataType?$filter=IdType in (123,512)

ODataQueryBuilderOptions

UseCorrectDateTimeFormat

You should always manually set this option to true (default: false)

• true DateTime format $"{dateTime:yyyy-MM-ddTHH:mm:sszzz}" (with UTC offset)
• false DateTime format $"{dateTime:s}Z" (without UTC offset)