EF Core one-way self referencing entity binding

Question

I have set up a self referential entity using EF Core which looks like this:

Entity

public class DetailType
{
    public int DetailTypeId { get; set; }
    public string Name { get; set; }

    public int? ParentTypeId { get; set; }
    public DetailType ParentType { get; set; }
    public IEnumerable<DetailType> ChildTypes { get; set; }
}

Binding

modelBuilder.Entity<DetailType>()
    .ToTable("detailtype")
    .HasOne(x => x.ParentType)
    .WithMany(x => x.ChildTypes)
    .HasForeignKey(x => x.ParentTypeId);

I am retrieving these entities via an API and the current result looks something like this:

[
    {
        "detailTypeId": 20,
        "name": "Money",
        "parentTypeId": null,
        "parentType": null,
        "childTypes": null
    },
    {
        "detailTypeId": 22,
        "name": "Optional Extra",
        "parentTypeId": null,
        "parentType": null,
        "childTypes": [
            {
                "detailTypeId": 42,
                "name": "Extra Nights",
                "parentTypeId": 22,
                "childTypes": null
            }
        ]
    },
    {
        "detailTypeId": 42,
        "name": "Extra Nights",
        "parentTypeId": 22,
        "parentType": {
            "detailTypeId": 22,
            "name": "Optional Extra",
            "parentTypeId": null,
            "parentType": null,
            "childTypes": []
        },
        "childTypes": null
    }
]

The problem I have with this is that the third item in the array is just the reverse of the second. Is there a way to avoid this so that I only have the parent -> child relationship rather than both parent -> child as well as child -> parent. The example above is a heavily cut-down version of what my API is actually returning so I want to reduce the unnecessary bloat as much as possible because there'll be a lot of relationships going on.

Ideally what I want is to just get rid of the ParentType property but still have the ChildTypes collection but I'm not sure how to define that in the model builder.

EDIT:

I have removed the fluent relationship as it's not needed. I've also tried the following:

var roots = this.Items.Where(x => x.ParentTypeId == null);
foreach (var root in roots)
{
    root.ChildTypes = this.Items.Where(x => x.ParentTypeId == root.DetailTypeId);
}

return roots.ToList();

(this.Items is the DbSet by the way)

However this requires changing ChildTypes to an IQueryable and when I do that I get the following exception:

The type of navigation property 'ChildTypes' on the entity type 'DetailType' is 'EntityQueryable' which does not implement ICollection. Collection navigation properties must implement ICollection<> of the target type.

Cavalry

How about using a separate model for your api that does not contain the ParentType property. Then simply map internal (db) model to external (api) model in your controller using e.g. Automapper automapper.org – Hydro

As already mentioned, there are some design-level proper ways to do this. But just as a workaround - you can apply .Where(x => x.parentTypeId == null) to get rid of all items except for top-level. This should provide the desired output. – Rockrose

Yes, you'll need to handle this with view models/DTOs, instead of using your entity class directly. – Geomorphology

@YeldarKurmangaliyev This does indeed only return the root items. However, it also removes all the items in ChildTypes so all I get is root items which is not what I want. – Cavalry

@AndyFurniss It does return root items with all non-root items listed in ChildItems, isn't it? Why would it remove all items in ChildTypes? – Rockrose

Answer 1

First things first - you don't need to specify this relation(s) in the model builder. It figures it out on its own.

Then regarding your question - the first thing, that comes to my mind (I don't know your entire dataset) is to get all the DetailType objects, that have ParentTypeId == null.

By this you will get the roots, and then, recursively build the tree of child elements.

Doing this will clean-up your result, and you will see the structure as you want to see it.

Cinquecento

Won't this require multiple DB calls? – Cavalry

Well, depends - do you have lazy loading enabled, or you can explicitly include .ChildTypes in the call to the DB. Also - you can query all the items from the db initially, and then from the received list, build your structure, by getting only the root elements and continue with the recursion. You will have queried all the items already, and all the operations will be in-memory, without further calls to the db. – Cinquecento

Furthermore - if you work with an IQuerable collection of DetailType and build your structure, and at the end you call the .ToList() of the collection, you will execute only one query (when the .ToList() is called). You can always use SQL Server Profiler, to check your queries and be sure that what is executed is what you want to be executed. – Cinquecento

Hmm, I've given it a go but haven't made much progress. Please see the edit to my question for details. Let me know if I'm doing it wrong. – Cavalry

what is this.Items? Aren't you querying against a dbcontext? – Cinquecento

It's the DbSet. I added a comment below the code to explain. – Cavalry

I have a similar case, and indeed I'm using ICollection<> for the child collection. Just had a look at it. Go ahead with that – Cinquecento

But to use ICollection I have to call ToList() on each loop iteration (after the Where) which is going to end up calling the database X number of times where X is the number of root items. How does yours deal with this? – Cavalry

You don't need to. That is why I asked - do you have lazy loading enabled, or are you explicitly including the Child collection. This is how I'm dealing with it and I have only one query – Cinquecento

I don't have lazy loading enabled but this seems to have worked - this.Items.Where(x => x.ParentTypeId == null).Include(x => x.ChildTypes). – Cavalry

This still seems to require me to have the ParentType property, even though it's always going to be null. I get a "Unknown column 'x.DetailTypeId1' in 'field list'" error if I remove it from the model. Is there a way I can configure the relationship to not need this property, so it just has ParentTypeId and ChildTypes properties? – Cavalry

No way - you need to keep the column. My point with the relations was for the modelbuilder stuff. More specific - .HasOne(x => x.ParentType) .WithMany(x => x.ChildTypes) this part. You don't need this, because it resolves it itself, BUT you still need the foreign key column (ParentId). I'm glad to see that the explicit include worked for you. You are on the right way – Cinquecento