How do I limit the columns that are returned by web api?

Question

How do I limit the columns that are returned by web api & entity framework? I would appreciate as much info as possible as I am still a newbie ;)

My Controller:

         //GET: api/Creditors
    public IQueryable<Creditor> GetCreditors()
    {
        return db.Creditors;
    }

My Class:

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Web;

namespace PurchaseOrders.Models
{
public class Creditor
{
    [Key]
    public int CreditorID { get; set; }

    [MaxLength(10, ErrorMessage = "Maximum of 10 characters")]
    public string CRKEY { get; set; }

    [Display(Name = "Business Name")]
    [MaxLength(40, ErrorMessage = "Maximum of 40 characters")]
    public string BusinessName { get; set; }

    [MaxLength(40, ErrorMessage = "Maximum of 40 characters")]
    public string Address { get; set; }

    [MaxLength(40, ErrorMessage = "Maximum of 40 characters")]
    public string City { get; set; }

    [MaxLength(4, ErrorMessage = "Maximum of 4 characters")]
    public string State { get; set; }

    [MaxLength(4, ErrorMessage = "Maximum of 4 characters")]
    public string Postcode { get; set; }

    [MaxLength(15, ErrorMessage = "Maximum of 15 characters")]
    public string Phone { get; set; }

    [MaxLength(15, ErrorMessage = "Maximum of 15 characters")]
    public string Fax { get; set; }

    [MaxLength(60, ErrorMessage = "Maximum of 60 characters")]
    public string Email { get; set; }

    [MaxLength(60, ErrorMessage = "Maximum of 60 characters")]
    public string Website { get; set; }

    [MaxLength(30, ErrorMessage = "Maximum of 30 characters")]
    public string ContactName { get; set; }

    [MaxLength(15, ErrorMessage = "Maximum of 15 characters")]
    public string ABN { get; set; }

    [Display(Name = "Registered for GST")]
    public bool RegisteredForGST { get; set; }

}

}

This currently returns:

[{"CreditorID":1,"CRKEY":"test1","BusinessName":"test1","Address":"7 Smith Street","City":"Melbourne","State":"VIC","Postcode":"3000","Phone":null,"Fax":null,"Email":null,"Website":null,"ContactName":null,"ABN":"null","RegisteredForGST":true},{"CreditorID":2,"CRKEY":"test2","BusinessName":"test2","Address":"10 Smith Street","City":"SYDNEY","State":"NSW","Postcode":"2000","Phone":null,"Fax":null,"Email":null,"Website":null,"ContactName":null,"ABN":"null","RegisteredForGST":true}]

This is the result I want (only the "CreditorID" & "BusinessName"):

[{"CreditorID":1,"BusinessName":"test1"},{"CreditorID":2,"BusinessName":"test2"}]

Answer 1

There are several different ways to handle this requirement without OData. I tend to use a projection query (as Lanorkin mentioned in his answer). Here are some examples.

1. Return a dynamic type from your WebAPI controller:

This is the quickest and easiest method. Some would argue that dynamic return types are sloppy, but they get the job done.

[HttpGet]
public dynamic GetCreditors() {
    return db.Creditors
      .Select(x => new {
      x.CreditorID,
      x.BusinessName,
    }).ToArray()
}

2. Use an explicit return type in your controller:

This works with WebAPI as well as WCF which does not allow dynamic return types. This is a "better" approach if your standard practice is to use static return types.

Create a class for your return type:

public class CreditorResult {
    public int CreditorID { get; set; }
    public string BusinessName { get; set; }
}

Then your API method would look like this:

[HttpGet]
public CreditorResult[] GetCreditors() {
    return db.Creditors
      .Select(x => new CreditorResult() {
      x.CreditorID,
      x.BusinessName,
    }).ToArray()
}

3. Use model attributes to control the output fields:

The current version of WebAPI uses JSON.NET as its serializer, and older versions can be set up to use it too. You can specify data attributes on your model to tell JSON.NET which properties to include or to ignore.

If you're using the code-first approach to Entity Framework then you can add the attributes directly to your class. If you're using the database-first approach (code generation) then it's safest to put your attributes in a Metadata class. http://www.ozkary.com/2015/01/add-data-annotations-to-entity.html

If you only want to include a few fields, you should add [DataContract] to the class and [DataMember] to the properties. Only the properties with [DataMember] will be included in the output. For example, the following would only return CreditorID and BusinessName:

[DataContract]
public class Creditor
{
    [DataMember]
    [Key]
    public int CreditorID { get; set; }

    [DataMember]
    [Display(Name = "Business Name")]
    [MaxLength(40, ErrorMessage = "Maximum of 40 characters")]
    public string BusinessName { get; set; }
...

If you want to include most of your fields and ignore a few, the easier option is to add [JsonIgnore] to the properties that you wish to hide.

public class Creditor
{
    [Key]
    public int CreditorID { get; set; }

    [JsonIgnore]
    [MaxLength(10, ErrorMessage = "Maximum of 10 characters")]
    public string CRKEY { get; set; }

    [Display(Name = "Business Name")]
    [MaxLength(40, ErrorMessage = "Maximum of 40 characters")]
    public string BusinessName { get; set; }
...

There are a lot of other ways to fine-tune the output. Check out http://james.newtonking.com/archive/2009/10/23/efficient-json-with-json-net-reducing-serialized-json-size for more details.

Answer 2

In your question you're showing the json output of the query, so I assume you're making the GET request from Javascript. As you're using the IQueryable as the type of the return value from your API method, you should be able to take advantage of the OData support that WebApi provides so that you can issue an OData query to select just the columns you want. This this article for more detail on the OData support.

So firstly, the javascript side, assuming jQuery for ease of answering:

$.get('api/Creditors?$select=CreditorId,BusinessName', onSuccess)

The column names you want are specified in a comma separated list in the $select argument. (The onSuccess is a callback function you would define which would be passed the data that comes back from the API. See the jQuery documentation for more details.)

On the server side, you might need to derive your controller from ODataController instead of ApiController and you will need to add either the [Queryable] or the [EnableQuery] attribute to your GetCreditors() method depending on the version of WebApi you are using.

There is another bit of configuration you have to add if you find that you do need to inherit from ODataController to make this work, and that is to configure the OData endpoint. To do this you will need code similar to the following:

public static class WebApiConfig
{
    public static void Register(HttpConfiguration config)
    {
        ODataModelBuilder builder = new ODataConventionModelBuilder();
        builder.EntitySet<Creditor>("Creditors");
        config.MapODataServiceRoute(
            routeName: "ODataRoute",
            routePrefix: null, // or "api" ?
            model: builder.GetEdmModel());
    }
}

Somewhere in your web startup code (e.g. Application_Start) you will need to call this as follows:

GlobalConfiguration.Configure(WebApiConfig.Register);

Depending on how you've set your project up, some of this latter configuration might not be necessary as it will already be done, but I thought I'd mention it for good measure. Have a look at this page for more details.

Answer 3

This can be done using projection, here is sample using anonymous type:

db.Creditors
  .Select(x => new {
      x.CreditorID,
      x.BusinessName,
  })
  .ToArray()

This will result in query to database which will get only two fields you need wrapped in anonymous class. You can return it directly from your WebAPI controller with JSON result.

If you need to pass result (which is of type SomeAnonymousClassICanNotReference[]) between layers, you can either use dymanic keyword (not a good option actually), or use your custom class like Select(x => new MyClass { Id = x.CreditorID ...