Look up the nested parents (self-reference) until condition is met in EF core LINQ?

Question

0.00/5 (No votes)

See more:

Hello,

is there a good and fast way to get a single Parent object (or its id) by condition without iterating up all the levels of self-referencing relationship?

The model is for example this (pseudocode):

class MyObject {
bool myCondition;
virtual MyObject Parent;
int ParentId;
}

And the hierarchy can be infinite e.g. real scenario:
MyBuilding -> MyFloor -> MyOffice -> MyRoom etc.

and what I need to get from SQL Db using the latest EF Core is the first parent
that meets the condition so e.g. I have MyRoom id = 4 and I want to get Parent object which has myCondition = true and I have no idea at which level this condition is met (might be true already in MyRoom, might be true in MyBuilding, besically anywhere in the relationship path).

Is that possible to write in LINQ without getting all the levels one by one and checking the flag? The performance is the key, the nested levels might be a lot.

What I have tried:

I have completely no idea how to create this query, if it's even possible?

Posted 13-Jul-20 23:30pm

Member 14637786

Updated 15-Jul-20 1:20am

Add a Solution

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

#realJSOP · Answer 1 · 2020-07-14T01:39:00

Solution 1

Create a stored proc and call it from EF to populate the colle ction. Let the query in the stored proc add the necessary "relationship" columns and populate them. At that point, your child objects should have a reference to the parent. Let the database do the work.

Posted 14-Jul-20 1:39am

#realJSOP

Comments

[no name] 14-Jul-20 10:32am

Unfortunately, we don't store any logic anywhere but API :( Thanks for the suggestion, tho.

Dave Kreskowiak 14-Jul-20 12:18pm

That's a stupid restriction.

EF is not known for generating the most efficient SQL code. You'd be must better off writing the SQL code in a stored procedure, where it will be compiled by the database and run far more efficiently then depending on EF to do a bad job at generating equivalent code. The end result will be easier to debug and support.

[no name] 14-Jul-20 12:33pm

Used to have issues with that and not only me when it comes to maintenance and further development of pretty big solutions so I kinda agree with not using SQL stored procedures for logic. Also problematic when moving to a newer platform and moving the logic with it etc. so again I understand this decision.

But it's not the issue. Also it's pretty easy to pass an SQL command in a form of TSQL string from EF Core to SQL Db and I've thought of that but maybe there's a better way, hence the question.

For now the working solution is getting all the records for a certain path (Include/ThenInclude) and then calculating it in memory just by getting deeper till the condition is met instead doing all the joins on database side. This works just fine as long as the paths won't grow to large ones. That's the only thing I'm afraid of right now but we're far from real life scenarios like that atm.

I'll pass the idea for plain TSQL command send to db but I don't think it'll be accepted.

Also looking forward for any further input from anyone who's encountered this issue.

#realJSOP · Answer 2 · 2020-07-14T08:30:00

Your model makes absolutely no sense.

0) The MyCondition property doesn't appear to have any association with the specified question.

1) The ParentID property should be called ID because it's the id of the object, not the parent object..

Assuming EF generates partial entity classes (I don't know because I avoid EF at all costs), create a partial file for the entity and put add a method to your entity that gets it's parent object id, and when you get to an object that doesn't have a parent, return its id

C#

public int GetTopParentID()
{
    return (this.ParentObject != null) ? this.ParentObject.GetTopParentID() : this.ParentID;
}

That's the absolute best way to do it (no iteration necessary).

Richard Deeming · Answer 3 · 2020-07-15T01:19:00

Are you free to modify the table structure? If so, you might be able to use a HierarchyID column.
hierarchyid (Transact-SQL) - SQL Server | Microsoft Docs[^]

It's not supported natively, but there's a third-party library which adds support to EF Core 3.1 or later:
GitHub - efcore/EFCore.SqlServer.HierarchyId: Adds hierarchyid support to the SQL Server EF Core provider[^]

You could then use IsDescendantOf and GetLevel to find the closest matching parent, which should produce a fairly decent query:

C#

class MyObject
{
    public HierarchyId Id { get; set; }
    public bool myCondition { get; set; }
}

MyObject targetObject = ...;
HierarchyId targetId = targetObject.Id;

MyObject closestParent = context.MyObjects
    .Where(o => o.myCondition)
    .Where(o => targetId.IsDescendantOf(o.Id))
    .OrderByDescending(o => o.GetLevel())
    .FirstOrDefault();

NB: x.IsDescendantOf(x) will return true, so this will work if the condition is met on the target object.

The down-side is that there is currently no support for navigation properties:
Navigation property support · Issue #8 · efcore/EFCore.SqlServer.HierarchyId · GitHub[^]

Laxmidhar tatwa technologies · Answer 4 · 2020-07-14T06:22:00

public class MyBuilding
    {
        public int id { get; set; }
        public List<MyFloor> MyFloors { get; set; }
    }
    public class MyFloor
    {
        public int id { get; set; }
        public List<MyOffice> MyOfficerooms { get; set; }
    }
    public class MyOffice
    {
        public int id { get; set; }
        public int MyRoom { get; set; }
    }

 void  travelthroughclass()
        {
            var MyOffices = new List<MyOffice>();
            MyOffices.Add(new MyOffice() { id = 1, MyRoom = 2 });
            MyOffices.Add(new MyOffice() { id = 2, MyRoom = 3 });
            var Floors = new List<MyFloor>();
            Floors.Add(new MyFloor() { id = 1, MyOfficerooms = MyOffices });
            var MyBuildings = new List<MyBuilding>();
            MyBuildings.Add(new MyBuilding() { id = 1, MyFloors = Floors });

            var myoffrooms = (from b in MyBuildings.Select(f => f.MyFloors.Select(r => r.MyOfficerooms.Where(o=>o.id ==2)))

                             select b).ToList();
                               
        }

Look up the nested parents (self-reference) until condition is met in EF core LINQ?

4 solutions

Solution 1

Solution 3

Solution 4

Solution 2

Add your solution here

Preview 0