Asp.net Web API database querying

Question

I have an Asp.net Web Api project, which has CRUD methods.

However, i have a method, called List, which retrieves a list of items found in database.

I need to give flexibility for the user to apply custom sorts or custom queries on top of the entities.

The best solution i found so far is using OData.

However, OData is much more complex than this, and i think it creates extra complexity no needed (Create / Update / Delete works just fine as they are now).

My question is:

Is something similar to OData that i can implement in my Asp.net Web Api project which allows me to query data from database?
If no, can i implement OData just for one method in my controller (List) ?

I don't know if the server will know how to interpret the query string to a query criteria by default. I need some type of configuration / mapping — Catalin
– Catalin, Commented Feb 3, 2015 at 8:26
Media-type formatters asp.net/web-api/overview/formats-and-model-binding/… — rism
– rism, Commented Feb 3, 2015 at 8:32

Faris Zacina · Accepted Answer · 2015-02-03 09:46:58Z

2

OData is a good option on the .NET platform. There are alternatives of course, but OData should be good enough for your use-case. If you don't need all the features of OData you can also implement querying yourself by following similar principles, afer all OData is a protocol, and Microsoft has it's own implementation in .NET.

Yes you can enable OData querying globally or for a single action in a Web API controller.

Here is an excerpt from the ASP.NET site:

The EnableQuerySupport method enables query options globally for any controller action that returns an IQueryable type. If you don’t want query options enabled for the entire application, you can enable them for specific controller actions by adding the [Queryable] attribute to the action method.

Here is an example Web API controller with querying enabled:

public class ProductsController : ApiController
{
    [EnableQuery]
    IQueryable<Product> Get() {}
}

You could invoke client-driven paging on the controller above with the following URL:

http://localhost/Products?$top=10&$skip=20

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

In the example above i used the EnableQuery attribute instead of Queryable since the latter is obsolete.

You can install OData using the following NuGet package:

https://www.nuget.org/packages/Microsoft.AspNet.OData/

edited Feb 3, 2015 at 9:46

answered Feb 3, 2015 at 9:24

Faris Zacina

14.3k8 gold badges64 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Catalin Over a year ago

Exactly what i was looking for. Only one problem remains (two actually): How can i customize the property names of the entity? For example, ignore the "DateCreated" property because DateTime is not supported by OData, and transform Name to application-name for example?

Faris Zacina Over a year ago

I don't think the dash is supported by the OData protocol, so you will not be able to use "application-name". DateCreated could be converted to DateTimeOffset to make it work. However, i would suggest using a different class that is built only for the purpose of OData querying, so you are exposing only what is needed by OData in a form that is usable by it.

Catalin Over a year ago

Ok, i agree. I have been looking for an example of how to build a different class used just for the OData but with no success. From what i have tried, the class always needs to be the same class used by Entity Framework. Do you have an example of how to create a different class just for OData?

Faris Zacina Over a year ago

That sounds like good material for a new question ;) One way to do it is to project the Product into a DTO using LINQ. e.g. _products.Select((p) => new ProductDto(){ AppName = p.Name }).

nanonerd Over a year ago

config.EnableQuerySupport() is now obsolete. Cannot configure it for global use in .net 4.6 ;-( msdn.microsoft.com/en-us/library/…

Collectives™ on Stack Overflow

Asp.net Web API database querying

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related