ASP.NET Core 3.1 + OData + EF Core Many-to-many expand problem

Question

In .NET 5, when you describe a many-to-many relation, for example, between Persons and Clubs (for simpliness, they have only Id properties), you should only define a property ICollection<Club> Clubs in Person class, and vice versa a property ICollection<Person> Persons in Club class. EF Core recognizes the many-to-many relation, and creates Persons, Clubs and PersonClubs tables in the database. In this case, your OData Uri can be like http://myapi.com/odata/persons?$expand=Clubs, and returns something like

{
    "@odata.context": "http://myapi.com/odata/$metadata#persons",
    "value": [
        {
            "Id": 1,
            "Clubs": [ { "Id": 123 } ]
        }
    ]
}

In .NET 3.1 there is no support of many-to-many relations, so you have to create models and implement DbContext's OnModelCreating method like here. Moreover, you have to change your Startup.cs/GetEdmModel() method like this (as advised here):

    private IEdmModel GetEdmModel()
    {
        var odataBuilder = new ODataConventionModelBuilder();
        odataBuilder.EntitySet<Person>("Persons");
        odataBuilder.EntitySet<Club>("Clubs");
        // THIS ROW was added!
        odataBuilder.EntityType<PersonClubs >()
            .HasKey(x => new {x.PersonId, x.ClubId});

        return odataBuilder.GetEdmModel();
    }

Finally, you should change controllers methods (this is an example for PersonsController, the method Get) from

    [EnableQuery]
    public IActionResult Get()
    {
        return Ok(_dbContext.Persons);
        // or return Ok(_dbContext.Persons.Include(x => x.Clubs);
    }

to

    [EnableQuery]
    public IActionResult Get()
    {
        return Ok(_dbContext.Persons
            .Include(x => x.PersonClubs)
            .ThenInclude(x => x.Club));
    }

All this allows you to navigate from person to its Clubs using $expand, but not directly: uri will be http://myapi.com/odata/persons?$expand=PersonClubs($expand=Club), and result will be more complicated:

{
    "@odata.context": "http://myapi.com/odata/$metadata#Persons(PersonClubs(Club()))",
    "value": [
        {
            "Id": 1,
            "PersonClubs": [
                {
                    "PersonId": 1,
                    "ClubId": 123,
                    "Club": {
                        "Id": 123
                    }
                }
            ]
        }
    ]
}

The question: how can I get the JSON structure like in the beginning of question? I think, if it's possible, there should be some EDM model builder additions as well as controller changes.

ASP.NET Core 3.1 + OData + EF Core Many-to-many expand problem

0 Answers0