1

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.

Andy Furniss
  • 3,814
  • 6
  • 31
  • 56
  • 1
    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 https://automapper.org/ – Christoph Lütjen Sep 25 '18 at 15:54
  • 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. – Yeldar Kurmangaliyev Sep 25 '18 at 15:55
  • Yes, you'll need to handle this with view models/DTOs, instead of using your entity class directly. – Chris Pratt Sep 25 '18 at 15:55
  • @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. – Andy Furniss Sep 25 '18 at 16:01
  • @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`? – Yeldar Kurmangaliyev Sep 26 '18 at 10:42

1 Answers1

0

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.

m3n7alsnak3
  • 3,026
  • 1
  • 15
  • 24
  • Won't this require multiple DB calls? – Andy Furniss Sep 25 '18 at 16:04
  • 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. – m3n7alsnak3 Sep 25 '18 at 16:08
  • 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. – m3n7alsnak3 Sep 25 '18 at 16:11
  • 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. – Andy Furniss Sep 25 '18 at 16:26
  • what is `this.Items`? Aren't you querying against a dbcontext? – m3n7alsnak3 Sep 25 '18 at 16:27
  • It's the DbSet. I added a comment below the code to explain. – Andy Furniss Sep 25 '18 at 16:28
  • 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 – m3n7alsnak3 Sep 25 '18 at 16:29
  • 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? – Andy Furniss Sep 25 '18 at 16:31
  • 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 – m3n7alsnak3 Sep 25 '18 at 17:13
  • I don't have lazy loading enabled but this seems to have worked - `this.Items.Where(x => x.ParentTypeId == null).Include(x => x.ChildTypes)`. – Andy Furniss Sep 26 '18 at 09:24
  • 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? – Andy Furniss Sep 26 '18 at 09:44
  • 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 – m3n7alsnak3 Sep 26 '18 at 16:23