Is there any way I can achieve this in Entity Framework?

Question

I'm running an ASP.NET Core Web API project and I have this model class with a self-referencing association :

public class VehicleCategory 
{
    [Key]
    public int Id { get; set; }
    public string description { get; set; }

    [ForeignKey("ParentVehicleCategoryId")]
    public int? ParentVehicleCategoryId { get; set; }
    [JsonIgnore]
    public virtual VehicleCategory ParentVehicleCategory { get; set; }
    public virtual IEnumerable<VehicleCategory> subVehicleCategories { get; set; }
}

I have configured everything in EF to retrieve get all data and added this config into the startup.cs file to prevent looping:

services.AddControllers().AddNewtonsoftJson(options =>
            options.SerializerSettings.ReferenceLoopHandling = Newtonsoft.Json.ReferenceLoopHandling.Ignore
        );

The problem is that when I try to get all data from the database, I end up with this json result :

[
  {
    "id": 7,
    "description": "description 1",
    "parentVehicleCategoryId": null,
    "subVehicleCategories": [
      {
        "id": 9,
        "description": "description 2",
        "parentVehicleCategoryId": 7,
        "subVehicleCategories": null
      },
      {
       "id": 10,
        "description": "description 3",
        "parentVehicleCategoryId": 7,
        "subVehicleCategories": null
      }
    ]
  },
  {
    "id": 9,
    "description": "description 2",
    "parentVehicleCategoryId": 7,
    "subVehicleCategories": null
  },
  {
    "id": 10,
    "description": "description 3",
    "parentVehicleCategoryId": 7,
    "subVehicleCategories": null
  }
]

Is there a way I can only have this data with only the array representing the children:

[
  {
    "id": 7,
    "description": "description 1",
    "parentVehicleCategoryId": null,
    "subVehicleCategories": [
      {
        "id": 9,
        "description": "description 2",
        "parentVehicleCategoryId": 7,
        "subVehicleCategories": null
      },
      {
       "id": 10,
        "description": "description 3",
        "parentVehicleCategoryId": 7,
        "subVehicleCategories": null
      }
    ]
  },
 
]

To get data I'm using this code:

var test = _raceContext.VehicleCategories
                       .Include(x => x.subVehicleCategories)
                       .ToList();

Anders Marzi Tornblad · Accepted Answer · 2021-07-07T13:58:43.347

4

If you want to include only top-level entities, and their children listed inside, you could use a Where clause to get only those where ParentVehicleCategory is null:

var test = _raceContext.VehicleCategories
                       .Where(vc => vc.ParentVehicleCategory == null)
                       .Include(x => x.subVehicleCategories)
                       .ToList();

This way, the top-level list is the "parent-less" entities, but the second-level entities will appear in your tree, because those entities are all children of some parent, and is included because of your call to the Include method. If you have only two levels of hierarchy, this method will work for you, but if a "child" entity can also be a "parent", you will need to do some extra work.

edited Jul 07 '21 at 13:58

answered Jul 07 '21 at 09:04

Anders Marzi Tornblad

18,896
9
51
66

This would return only top and first level entities, and the rest levels will be missing. – Ivan Stoev Jul 07 '21 at 12:27
Yes, I thought that was clear in my first sentence. But I edited my last paragraph to maybe add some clarity. – Anders Marzi Tornblad Jul 07 '21 at 13:59

Is there any way I can achieve this in Entity Framework?

1 Answers1