Web API and OData V4 CRUD and Actions Part 3

This article demonstrates how simple CRUD operations can be mapped to an OData service, how to map DateTimeOffset to DateTime types in the entity framework and also how to create OData actions for Entities and Entity Collections. This post is part 3 of the Web API and OData V4 series.

Part 1 Getting started with Web API and OData V4 Part 1.
Part 2 Web API and OData V4 Queries, Functions and Attribute Routing Part 2
Part 3 Web API and OData V4 CRUD and Actions Part 3
Part 4 Web API OData V4 Using enum with Functions and Entities Part 4
Part 5 Web API OData V4 Using Unity IoC, SQLite with EF6 and OData Model Aliasing Part 5
Part 6 Web API OData V4 Using Contained Models Part 6
Part 7 Web API OData V4 Using a Singleton Part 7
Part 8 Web API OData V4 Using an OData T4 generated client Part 8
Part 9 Web API OData V4 Caching Part 9
Part 10 Web API OData V4 Batching Part 10
Part 11 Web API OData V4 Keys, Composite Keys and Functions Part 11

Code: https://github.com/damienbod/WebAPIODataV4

OData CRUD

Before POST or PUT can be executed, the Database mapping needs to be changed. OData V4 only supports DateTimeOffset. The database uses DateTime. This needs a mapping.
In the ContactType entity class, change the DateTime property:

public static class ContactTypeExpressions
{
 public static readonly Expression<Func<ContactType, DateTime>> ModifiedDate = c => c.LastModifiedOnInternal;
}

public DateTimeOffset ModifiedDate
{
 get { return new DateTimeOffset(LastModifiedOnInternal); }
 set { LastModifiedOnInternal = value.DateTime; }
}

private DateTime LastModifiedOnInternal { get; set; }

Now that the Entity maps the DateTime to a DateTimeOffset, this needs to be added in the OnModelCreating method in the DomainModel.

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
  modelBuilder.Entity<ContactType>().Ignore(c => c.ModifiedDate);
  modelBuilder.Entity<ContactType>().Property(WebAppODataV4.Database.ContactType.ContactTypeExpressions.ModifiedDate).HasColumnName("ModifiedDate");
       

Create a HTTP POST method in the OData ContactTypeController

[ODataRoute()]
[HttpPost]
[EnableQuery(AllowedQueryOptions = AllowedQueryOptions.All)]
public IHttpActionResult Post(ContactType contactType)
{
  if (!ModelState.IsValid)
  {
    return BadRequest(ModelState);
  }

  _db.ContactType.AddOrUpdate(contactType);
  _db.SaveChanges();
  return Created(contactType);
}

Now a new ContactType entity can be created with a HTTP post:
odatav4WebApi_07

This can then be validated in the database:
odatav4WebApi_08

Now the PUT, PATCH and DELETE methods can be added. No Content is required per default.

HttpPut method or Update:
Note: The request header requires Prefer: return-content, otherwise the default no-content is returned.

[ODataRoute("({key})")]
[HttpPut]
[EnableQuery(AllowedQueryOptions = AllowedQueryOptions.All)]
public IHttpActionResult Put([FromODataUri] int key, ContactType contactType)
{
  if (!ModelState.IsValid)
  {
     return BadRequest(ModelState);
  }

  if (key != contactType.ContactTypeID)
  {
    return BadRequest();
  }

  _db.ContactType.AddOrUpdate(contactType);
  _db.SaveChanges();

  return Updated(contactType);
}

HttpDelete

[ODataRoute()]
[HttpDelete]
[EnableQuery(AllowedQueryOptions = AllowedQueryOptions.All)]
public IHttpActionResult Delete([FromODataUri] int key)
{
  var entityInDb = _db.ContactType.SingleOrDefault(t => t.ContactTypeID == key);
  _db.ContactType.Remove(entityInDb);
  _db.SaveChanges();

   return Content(HttpStatusCode.NoContent, "Deleted");
}

HttpPatch partial update
Note: The request header requires Prefer: return-content, otherwise the default no-content is returned.

[ODataRoute()]
[HttpPatch]
[EnableQuery(AllowedQueryOptions = AllowedQueryOptions.All)]
public IHttpActionResult Patch([FromODataUri] int key, Delta<ContactType> delta)
{
  if (!ModelState.IsValid)
  {
    return BadRequest(ModelState);
  }

  var contactType = _db.ContactType.Single(t => t.ContactTypeID == key);
  delta.Patch(contactType);
  _db.SaveChanges();
  return Updated (contactType);
}

OData Actions

Sometimes it is not so easy to do everything with CRUD operations, sometimes it needs extra logic to save etc. This can be done with OData actions.

In this example, an action is created and configured so that it can be called from the ContactType Entity or the ContactType Entity Collection. This code is added to the OData builder method.

EntitySetConfiguration<ContactType> contactType = builder.EntitySet<ContactType>("ContactType");
var actionY = contactType.EntityType.Action("ChangePersonStatus");
actionY.Parameter<string>("Level");
actionY.Returns<bool>();

var changePersonStatusAction = contactType.EntityType.Collection.Action("ChangePersonStatus");
changePersonStatusAction.Parameter<string>("Level");
changePersonStatusAction.Returns<bool>();

The action requires a level parameter and returns true or false.

This can then be added to the ContactTypeController:

[HttpPost]
[ODataRoute("Default.ChangePersonStatus")]
[EnableQuery(AllowedQueryOptions = AllowedQueryOptions.All)]
public IHttpActionResult ChangePersonStatus(ODataActionParameters parameters)
{
  if (ModelState.IsValid)
  {
   var level = parameters["Level"];
   // SAVE THIS TO THE DATABASE OR WHATEVER....
   }
   return Ok(true);
}

Now the action can be called:
For the Entity:
http://localhost:51902/odata/ContactType(5)/Default.ChangePersonStatus
For the Entity Collection:
http://localhost:51902/odata/ContactType/Default.ChangePersonStatus

odatav4WebApi_06

This calls the action method with a Http Post. The Model is then validated and the parameters can be read and used.The action returns a bool.

Links:

http://www.asp.net/web-api/overview/odata-support-in-aspnet-web-api/odata-actions

http://msdn.microsoft.com/en-us/library/ff478141.aspx

http://www.odata.org/documentation/odata-version-2-0/uri-conventions/

SAMPLES: https://aspnet.codeplex.com/SourceControl/latest#Samples/WebApi/OData/v4/

https://aspnetwebstack.codeplex.com/SourceControl/latest

http://aspnetwebstack.codeplex.com/

http://www.asp.net/web-api/overview/releases/whats-new-in-aspnet-web-api-22

http://www.asp.net/web-api/overview/odata-support-in-aspnet-web-api/odata-routing-conventions

http://stackoverflow.com/questions/18233059/apicontroller-vs-odatacontroller-when-exposing-dtos

http://meyerweb.com/eric/tools/dencoder/

http://techbrij.com/jquery-datatables-asp-net-web-api-odata-service

http://www.asp.net/web-api/overview/odata-support-in-aspnet-web-api/supporting-odata-query-options

http://www.dotnetcurry.com/showarticle.aspx?ID=1008

18 comments

  1. Any help with filtering DateTimeOffset values would be helpful. Particularly with overriding DateTime properties with DateTimeOffset in this way. I’m having an issue with filtering for greater than a datetimeoffset value.

    My URL looks like:
    http://cloud-dev.sqlsentry.com/sqlsentryodata/EventsLogs?%24format=json&%24top=25&%24filter=(ObjectID+eq+40f67164-dc92-477f-8870-8e18111f348f+and+NormalizedStartTimeUtc+ge+cast(%272014-7-15T20%3A00%2B0%3A00%27%2C%27Edm.DateTimeOffset%27))&%24count=true

    The response I receive is:
    “The query specified in the URI is not valid. The binary operator GreaterThanOrEqual is not defined for the types ‘System.Nullable`1[System.DateTimeOffset]’ and ‘System.Object’.”

    This is strange because:
    1) My entity property is not nullable
    2) Even if it were I think/hope nullable DateTimeOffset property would be valid.

    1. Hi Tim
      Thanks for the comment. I’ll have a look at this when I get time. A possible workaround would be that you use a long ToFileTime() in the Edm Model which represents the DateTime in the database. Not very nice but it will work in the query.

  2. Thanks for the mapping examples dealing with DateTime. Hopefully MS will fix this brokenness soon – encourage them by voting for https://aspnetwebstack.codeplex.com/workitem/2072 and http://aspnet.uservoice.com/forums/147201-asp-net-web-api/suggestions/6242255-odata-v4-service-should-support-datetime

  3. I do this with fiddler2 and still get no content header?
    {Id:4,”CreatedAt”:”2014-09-23T16:16:21Z”,”UpdatedAt”:”2014-09-23T16:16:21Z”,”Title”:”;lk;kljkjll;k;lk;lk;lkl;Word o Mouth”,”IsDefault”:false,”IsArchived”:false,”ModifiedById”:101 }

    User-Agent: Fiddler
    Prefer: return-content
    Content-Type: application/json; charset=utf-8
    Host: localhost:15255
    Content-Length: 180

    Body

    odata function
    [ODataRoute()]
    [HttpPut]
    [EnableQuery(AllowedQueryOptions = AllowedQueryOptions.All)]
    public async Task Put([FromODataUri] int key, MarketingSource inMarketingSource)
    {
    if (!ModelState.IsValid)
    {
    return BadRequest(ModelState);
    }
    if (key != inMarketingSource.Id)
    {
    return BadRequest();
    }
    _db.Entry(inMarketingSource).State = EntityState.Modified;
    try
    {
    await _db.SaveChangesAsync();
    }
    catch (DbUpdateConcurrencyException)
    {
    if (!MarketingSourceExists(key))
    {
    return NotFound();
    }
    else
    {
    throw;
    }
    }
    return Updated(inMarketingSource);
    }

    1. Thanks for your comment. ‘ll have a look at this

      greetings Damien

  4. Thanks for the great blogpost.

    I have a question. I got an error message when having ODataRoute on my Put method. Do you have any idea why ?

    The path template ‘({key})’ on the action ‘Put’ in controller ‘Employees’ is not a valid OData path template. Empty segment encountered in request URL. Please make sure that a valid request URL is specified.

    1. Thanks for you comment. Have you defined the ODataRoutePrefix on the controller? It should work then.

      [ODataRoutePrefix(“Employees”)]

      Greetings Damien

      1. Thanks, it works. I thought I don’t need that prefix attribute

  5. I must have missed a step when translating this into my own project. THe part where we change from DateTime to DateTimeOffset has this

    modelBuilder.Entity().Property(WebAppODataV4.Database.ContactType.ContactTypeExpressions.ModifiedDate).HasColumnName(“ModifiedDate”);

    In my code, the .Database gets the error ‘type or namespace ‘Database’ does not exist. What am I missing ?

    1. Hi Pete
      Thanks for your comment. The class is here
      https://github.com/damienbod/WebAPIODataV4/blob/master/WebAppODataV4/Database/ContactType.cs
      Database is a namespace in the application => namespace

      greetings Damien

      1. Thanks, Damien! That was the missing link!

        Pete

      2. Great
        Nice to have helped
        cheers Damien

  6. The solution you give for datetime/datetimeoffset works but you cannot filter or orderby those dates any longer

    {
    “error”:{
    “code”:””,”message”:”An error has occurred.”,”innererror”:{
    “message”:”The ‘ObjectContent`1’ type failed to serialize the response body for content type ‘application/json; odata.metadata=minimal’.”,”type”:”System.InvalidOperationException”,”stacktrace”:””,”internalexception”:{
    “message”:”The specified type member ‘CreatedAt’ is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported.”,”type”:”System.NotSupportedException”,”stacktrace”:” at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MemberAccessTranslator.TypedTranslate(ExpressionConverter parent, MemberExpression linq)\r\n at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TypedTranslator`1.Translate(ExpressionConverter parent, Expression linq)\r\n at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)\r\n at

    1. Sorry didn’t leave url.
      http://localhost:18832/Aas/Activities?$top=11&$orderby=CreatedAt
      Everything works great posting, put select. It is almost like the conversion is happening from EF to odata but not the opposite.

      1. Hi,
        Did you find a work around for this in the end? I’m having the same problem…
        Thanks

      2. jonalberghini · ·

        No I have posted this all over and no one can answer it.

  7. jonalberghini · · Reply

    As to what I wrote earlier about the 2 workarounds from
    http://stackoverflow.com/questions/25189557/how-to-get-web-api-odata-v4-to-use-datetime
    https://damienbod.wordpress.com/2014/06/16/web-api-and-odata-v4-crud-and-actions-part-3/

    neither work. You can no longer filter or sort by these fields or get this error

    http://localhost:18832/Aas/Activities?$top=11&$orderby=CreatedAt
    Gives this error

    “code”:””,”message”:”An error has occurred.”,”innererror”:{
    “message”:”The ‘ObjectContent`1’ type failed to serialize the response body for content type ‘application/json; odata.metadata=minimal’.”,”type”:”System.InvalidOperationException”,”stacktrace”:””,”internalexception”:{
    “message”:”The specified type member ‘CreatedAt’ is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported.”,”type”:”System.NotSupportedException”,”stacktrace”:”

    But this works as it is addressed indirectly
    http://localhost:18832/Aas/AppUsers%28102%29/AppUserActivities?$expand=Case&$filter=%28Reminder%20ne%20null%20and%20IsComplete%20eq%20null%29&$top=15&$orderby=Reminder&$count=true

    Reminder Iscomplete are from activity from AppUserActivities.

    This is wierd. Does anyone have a solution?

    I connect to mysql. You bunch of idiots. There is not a datetimeoffset.

    And everyone wonders why no one wants to develop for Microsoft Technologies.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: