Eager loading a full hierarchy from a self referencing table not using its primary key

Question

I'm trying to load hierarchical data from a self referencing table that is not using its primary key. So this answer seems to not apply.

I have the following table fleetdetail that contains hierachichal data

You can see that the table is not self referencing itself by its primary key fleetdetailid

Instead it is using fleetchildid and fleeid. See the bold values to understand the concept.

fleetdetailid	fleetid	fleetname	fleetchildid	childname
76234	12327	Direction Générale Adjointe Ressources Humaines et Moyens Technique	12328	Patrimoine bâti & logistique technique
76235	12328	Patrimoine bâti & logistique technique	12329	Gestion domaniale
76236	12328	Patrimoine bâti & logistique technique	12330	Ateliers Parc Auto
76239	12328	Patrimoine bâti & logistique technique	12333	Manifestations & Festivités
76241	12328	Patrimoine bâti & logistique technique	12335	Mission Entretien et Assurances
76248	12328	Patrimoine bâti & logistique technique	12342	Entretien Bâtiments
76249	12342	Entretien Bâtiments	12344	Cellule EPI
76257	12342	Entretien Bâtiments	12351	Cellule Electricité
76242	12335	Mission Entretien et Assurances	12336	Concierge
76243	12335	Mission Entretien et Assurances	12337	Agent d'entretien polyvalent du bâtiments
76240	12333	Manifestations & Festivités	12334	Fontainier
76237	12330	Ateliers Parc Auto	12331	Astreinte dépannage
76238	12330	Ateliers Parc Auto	12332	Astreinte Viabilité Hivernale

The Fleet table is here:

fleetid	fleetname
12327	Direction Générale Adjointe Ressources Humaines et Moyens Technique
12328	Patrimoine bâti & logistique technique
12330	Ateliers Parc Auto
12333	Manifestations & Festivités
12335	Mission Entretien et Assurances
12342	Entretien Bâtiments
12344	Cellule EPI
12351	Cellule Electricité
12336	Concierge
12337	Agent d'entretien polyvalent du bâtiments
12334	Fontainier
12331	Astreinte dépannage
12332	Astreinte Viabilité Hivernale

The entities are the following:

public partial class Fleet
{
        public Fleet()
        {
                FleetDetailFleet = new HashSet<FleetDetail>();
                FleetDetailFleetChildNavigation = new HashSet<FleetDetail>();
                AdditionalInit();
        }
        public int FleetId { get; set; }
        public string Name { get; set; }

        public virtual ICollection<FleetDetail> FleetDetailFleet { get; set; }
        public virtual ICollection<FleetDetail> FleetDetailFleetChildNavigation { get; set; }

        partial void AdditionalInit();
}


public partial class FleetDetail
{

    public int FleetDetailId { get; set; }

    public int FleetId { get; set; }

    public string FleetName { get; set; }

    public int? FleetChildId { get; set; }

    public string ChildName { get; set; }
    
    public virtual Fleet Fleet { get; set; }

    public virtual Fleet FleetChildNavigation { get; set; }
}

I have tried the following EF query but it does not work:

 var tree = context.FleetDetail
.Include(fd => fd.FleetChildNavigation)
.ThenInclude(f => f.FleetDetailFleetChildNavigation).AsEnumerable()
.Where(fd => fd.FleetChild == null);

It returns a resultset without the navigation properties populated.

Eager loading a full hierarchy from a self referencing table not using its primary key

0 Answers0