Monthly Archives: September 2013

WebApi Extension 3.0.1 Released

MicroLite.Extensions.WebApi 3.0.1 has been released on NuGet.

The only update is that the OData parsing part of the extension has been split out into a separate library & project.

This has 2 benefits, firstly we can evolve the OData parsing separately from the MicroLite extension and also others can use it.

The code can be found on GitHub (Net.Http.WebApi.OData) and the published binaries on NuGet.

Advertisements

MicroLite 4.0.1 Released

MicroLite 4.0.1 has been released on NuGet.

We have added some convenience methods to the configuration API so that you don’t have to specify the dialect string:

Configure.Fluently().ForMsSqlConnection("Connection");
Configure.Fluently().ForMySqlConnection("Connection");
Configure.Fluently().ForPostgreSqlConnection("Connection");
Configure.Fluently().ForSQLiteConnection("Connection");

WebApi OData filtering

In the previous post, we discussed the OData support in MicroLite.Extensions.WebApi 3.0.

When it comes to using $filter, we may want to restrict our results based upon properties which are data types other than a string.

OData dates and guids are specified as strings (quoted with single quotes) but allows the data type to prefix the value:

Dates should be specified in the yyyy-mm-dd format and optional include time by adding Thh:mm after dd. Dates in the Round Trip Date Format (“o” in C#) are also supported if you need to include higher precision and/or timezone information.

For a Date: $filter=Joined gt datetime'2010-01-01'
For a DateTime: $filter=Joined gt datetime'2013-10-23T10:00'
For a Guid: $filter=Identifier eq guid'D30648F9-6CAB-47AF-B010-53C8CD1CCB44'

Boolean values (true or false) are supported as unquoted literals:

$filter=Enabled eq true
$filter=Enabled eq false

To specify a decimal, use the M character after the value

$filter=Total gt 125.00M

To specify a double, use the D character after the value

$filter=Area gt 1512.1245D

To specify an integer, just specify a whole integer value

$filter=ItemCount eq 12

To specify a single, use the F character after the value

$filter=Distance gt 1243.224F

In MicroLite WebApi 3.0, the following operators are supported

eq – equal
gt – greater than
ge – greater than or equal
lt – less than
le – less than or equal
ne – not equal

The following string functions are also supported:

startswith – startswith(Name, 'Hayes') eq true would translate to WHERE Name LIKE ‘Hayes%’
endswith – endswith(Name, 'Hayes') eq true would translate to WHERE Name LIKE ‘%Hayes’
substringof – substringof('Hayes', Name) eq true would translate to WHERE Name LIKE ‘%Hayes%’

There are some other OData functions which are defined in OData 3.0 which MicroLite doesn’t currently support. Some of them may be added in future releases but the support in 3.0 allows for the most common types of query.

WebApi 3.0 OData Support

The biggest enhancement to the WebApi Extension in 3.0 is the support for OData queries.

A new controller has been added MicroLiteODataApiController<TEntity, TId> which is in the MicroLite.Extensions.WebApi.OData namespace. This extends the base MicroLiteApiController<TEntity, TId> so you can add OData support to “entity” controllers.

As per the MicroLiteApiController<TEntity, TId>, the OData support is opt-in so you need to create a public method called Get which accepts a parameter of ODataQueryOptions and calls the base class to return the response

public HttpResponseMessage Get(ODataQueryOptions queryOptions)
{
    return this.GetEntityResponse(queryOptions);
}

The following query options are currently supported:

  • $filter
  • $format
  • $inlinecount
  • $orderby
  • $select
  • $skip
  • $top

Given the following class:

public class Customer
{
    public int CustomerId { get; set; }
    public string Forename { get; set; }
    public string Surname { get; set; }
    public DateTime Joined { get; set; }
}

and the corresponding controller:

public class CustomerController : MicroLiteODataApiController&lt;Customer, int&gt;
{
    public CustomerController()
    {
        // This MaxTop will default to 50 if not otherwise specified &amp; will be used to limit the max value 
        // allowed in $top or constrain the results if no $top is specified in the OData query.
        this.ValidationSettings.MaxTop = 100;
    }

    public HttpResponseMessage Get(ODataQueryOptions queryOptions)
    {
        return this.GetEntityResponse(queryOptions);
    }
}

The ValidationSettings property allows you to limit the query options which can be used with the controller and also limit the number of results returned per request.

Example queries:

$select – this allows you to limit which properties are returned in the query, if no $select value is specified, all properties will be included. Property names must be cased correctly.

URI: /api/customer?$select=CustomerId,Forename,Surname

would return results such as

[{ "CustomerId": 1, "Forename": "John", "Surname": "Smith" }, { "CustomerId": 2, "Forename": "Joe", "Surname": "Bloggs" } ]

This means that the result set won’t contain properties that you have not specified so you won’t get default values returned in property values which haven’t been retrieved from the database and makes the response payload smaller.

$filter – this allows you to constrain the results returned based upon specified predicates, if no $filter value is specified, all results will be returned (up to the maximum supported by the controller)

URI /api/customer?$filter=Joined gt datetime'2010-01-01' and Surname eq 'Smith'

would return all customers with the surname Smith who joined after 1st Jan 2010.

$skip and $top can be used to constrain the number of results returned to implement paging e.g:

URI /api/customer?$skip=50&$top=50

$orderby – this allows you to specify how the results are ordered in the result set

URI /api/customer?$orderby=Surname, Joined desc

would sort by Surname ascending and then within each Surname, Joind date descending.

If not specified, the order will default to ascending. If specified, the order must be asc or desc

$format – allows you to override the format the data should be returned in regardless of the value in the content-type HTTP header:

regardless of the content-type header value, return the results in JSON:
URI /api/customer?$format=json

regardless of the content-type header value, return the results in XML:
URI /api/customer?$format=xml

$inlinecount – allows you to ask the results to be returned along with a total count of the results. If not specified, no inline count will be used

URI /api/customer?$inlinecount=allpages

include in the results a property called count which contains the total number of results

URI /api/customer?$inlinecount=none

do not include a count in the results – this is the default behavior if not specified.

The query options can be combined to filter results however you like, BUT each option may only appear once.