Method GetById and SingleResult

Question

Here is my method that I use to get one entity in an ASP.NET Web API application.

[EnableQuery]
public virtual HttpResponseMessage GetById([FromODataUri]int id)
{
    // get concrete entity from repository
    var entity = repository.GetById(id);
   // check entity
    if (entity == null)
    {
        var message = string.Format("No {0} with ID = {1}", GenericTypeName, id);
        return ErrorMsg(HttpStatusCode.NotFound, message);
    }

    // problem
    return Request.CreateResponse(HttpStatusCode.OK, SingleResult.Create(repository.Table.Where(t => t.ID == id)));
}

I'm using SingleResult for OData request (because $expand for single entity does not work if I do not create SingleResult). But in this case I repeatedly do almost the same action repository.GetById(id); and repository.Table.Where(t => t.ID == id).

How can I improve it?

Answer 1

You can wrap the single entity into an IQueryable. Something like this should work:

SingleResult.Create(new[]{entity}.AsQueryable());

You could write a helper function that uses this trick to convert from a single T to an IQueryable<T>. There may even be a built-in way of doing this.

Answer 2

[EnableQuery] and SingleResult does in fact return 404 OOTB, that is one of the key reasons for SingleResult to exist in the first place.

The problem is that your code is trying to return a raw HttpResponseMessage directly which will bypass the logic that makes this happen.

The basic implementation is simply this:

[EnableQuery]
public virtual SingleResult GetById([FromODataUri]int id)
{
    return SingleResult.Create(repository.Table.Where(t => t.ID == id)));
}

NOTE: we have not used the GetById(id) method on the repo at all. The key is to use IQueryable responses from your repo or DbContext, then you can pass them straight through to the SingleResult factory.

A core concept in OData is to avoid blocking the query and to delay execution right up until the EnableQueryAttribute can process it at the end of your method. GetById() looks like it resolves an item, so it has already executed the query against the DB without any of the additional projections that might be provided via $select and $expand. For EnableQueryAttribute to inject the necessary expressions we need to return an IQueryable<T> response that has not yet been enumerated.

If you do not want to return IQueryable<T>, then you must manually apply the ODataQueryOptions or otherwise explicitly include all the possible navigation values that you want to support and eagerly load them... that's a lot of work that we get for free OOTB if we return IQueryable<T>

If you have a complex requirement that might involve returning different results in different scenarios, then you can use the IHttpActionResult instead of SingleResult. A common scenario is that you want to explicitly throw an exception when there are multiple results to your expression, the SingleResult implementation will simply return TOP 1 from your query expression, and throw 404 when there are no results. It will not throw an exception when there are multiple results, it will only return the first result.

Do not confuse SingleResult with the linq Enumerable.Single() method that throws an InvalidOperationException when the input sequence contains more than one element -or- the input sequence is empty

SingleResult doesn't implement IHttpActionResult so we can't return it easily from our methods OOTB, I use this method in my controller base that inherits from ODataController, you could re-write it as an extension method to ApiController if you haven't extended the default controller implementation with your own base class.

/// <summary>
/// Helper to Create a SingleResult (200 OK) Response as IHttpActionResult
/// </summary>
/// <typeparam name="T">The type of item in query.</typeparam>
/// <param name="itemQuery">The query to get the item.</param>
/// <returns>A <see cref="System.Web.Http.SingleResult"/> from the specified <paramref name="itemQuery"/></returns>
protected IHttpActionResult SingleResultAction<T>(IQueryable<T> itemQuery)
{
    return Content(HttpStatusCode.OK, System.Web.Http.SingleResult.Create(itemQuery));
}

This example of lookup by Id shouldn't ever really return multiple results, but it demonstrates how to do the check anyway

[EnableQuery]
public virtual IHttpActionResult GetById([FromODataUri]int id)
{
    var itemQuery = repository.Table.Where(t => t.ID == id);
    if (itemQuery.Count() > 1)
        return InternalServerError();
    return SingleResultAction(itemQuery));
}

I wouldn't advise moving the greater than 1 check into the SingleResultAction method because that would force every query to evaluate twice, this is only provided as an example both of how to reuse the original IQueryable<T> expression and how to return SingleResult as an IHttpActionResult

Another common scenario where SingleResult is useful is with endpoints that insert data, so the Post method. If you use the EnableQueryAttribute on the Post method then instead of explicitly defining the possible expansions and returning a CreatedODataResult your can return SingleResult and get the added protection of validation that the item was actually committed to the database:

[OData.EnableQuery]
[HttpPost]
public virtual IHttpActionResult Post(TEntity item)
{
    if (!ModelState.IsValid)
        return BadRequest(ModelState);

    repository.Table.Add(item);
    repository.SaveChanges();

    // item will now have been assigned an Id from the database
    var newId = item.Id;
    return SingleResultAction(repository.Table.Where(t => t.ID == newId)));
}

It's out of scope for here, but you could easily encapsulate all those repository.Table.Where(t => t.ID == newId) calls into an abstract method on a base class or change the GetById() to return IQueryable<T> not an instance of an item.

---

You really want to avoid the solution mentions in this response because it breaks the IQueryable<T> expectation that the query can be re-evaluated against the database. Using that solution will force you to also eagerly load all the navigation properties that you might want to support.