C# Entity Framework 6 with ASP.Net web API returning empty array in JSON response

Question

This is the code I am using to handle a GET request in my "Works Orders" controller:

    // GET: api/WorksOrders/5
    /// <summary>
    /// Fetches the Works Order with corresponding ID (pkOrderItemID)
    /// </summary>
    [Authorize]
    public OrderItem Get(int id)
    {
        using (var entities = new customappsEntities())
        {
            return entities.OrderItems.FirstOrDefault(e => e.pkOrderItemID == id);
        }
    }

When this is run I get this error message in the JSON response:

 "Error getting value from 'OrderItemDepartments' on 'System.Data.Entity.DynamicProxies.OrderItem_501562E50E13B847D4A87F7F2DEC7C8CEDAF127355CB4FC30E12653275CE6412'.",

I can fix this by adding

entities.Configuration.ProxyCreationEnabled = false;

This returns the entire table but has a set of empty arrays at the bottom for example the JSON formatted response looks like:

{
  "$id": "1",
  "pkOrderItemID": 271,
  "StartedOn": "2015-01-01T00:00:00",
  "CompletedOn": "2014-10-15T00:00:00",
  "Costs": [],
  "Dispatches": []
}

The arrays appear to be foreign key relationships in the database and I believe they return empty because it is stuck in a self-referencing loop (I was able to get that error message a few times but I haven't been able to recreate it since). I've tried adding

GlobalConfiguration.Configuration.Formatters.JsonFormatter.SerializerSettings.Re‌​ferenceLoopHandling = ReferenceLoopHandling.Ignore;

From: Self referencing loop detected - Getting back data from WebApi to the browser

And many other solutions from the same or similar threads but it didn't solve the issue. All of my data classes are auto-generated by Entity Framework so I can't modify them, maybe this is the wrong way to have a web API set up?

Any help to figure out why the arrays return empty will be appreciated. Thank you.

Update

Here's a screenshot of the DataModel Diagram to show the fk relationship

Answer 1

Do not return anything other than POCOs or anonymous objects via JSON serializing. Entity Framework objects have a huge overhead you do not want to see and often have circular relationships that the serializer cannot cope with (and you do not want, even if you configure it to allow them).

Create a POCO or return a select:

[Authorize]
public MyOrderItem Get(int id)
{
    using (var entities = new customappsEntities())
    {
        var item = entities.OrderItems.FirstOrDefault(e => e.pkOrderItemID == id);
        return new MyOrderItem()
        {
             StartedOn = item.StartedOn,
             ...
        };
    }
}

The key to correctly using JSON is to return only what you need across the line.

Update

As the item contains child items, you need to ensure you have POCOs for those too:

e.g.

var myOrderItem = new MyOrderItem()
{
     StartedOn = item.StartedOn,
     Costs = new List<MyCost>();  // <=== This can also go in the default constructor
     ...
};
foreach (var cost in item.Costs)
{
     myOrderItem.Costs.Add(new MyCost()
     {
         Value = cost.Value
         ...
     });
}
return myOrderItem;

Another update

That is very odd. It certainly should give you your records. You can also try an .Include() with .Select() and FirstOrDefault() instead of just FirstOrDefault() to guarantee the related records are pre-loaded.

e.g.:

entities.OrderItems.Include(x=>x.Costs).Select(x=>x).FirstOrDefault();

Answer 2

Add Newtonsoft Json Library and return the result like this

JsonSerializerSettings jss = new JsonSerializerSettings { ReferenceLoopHandling = ReferenceLoopHandling.Ignore };
    return JsonConvert.SerializeObject(result, Formatting.Indented, jss);