Get recursive rootID of parent-child-table with ef-core

Question

I have an SQL-Table which is build like a tree list (parent child). Means you have rootId's and childId's. Each entity has a ParentId. If the ParentId is null then that element is a root item.

Here an example of the hierarchical architecture

- Fruit (1)
  - TreeFruits (2)
    - Apple (4)
    - Bulb (5)
  - SeaFruits (3)
    - DeepSeaFruits (6)
      - Sushi (9)
      - Kraken (13)
    - Shrimp (7)
    - Fish (8)
- Vegetable (10)
  - Carrot (11)
  - Potato (12)

You can see that each root element (number 1 and 10) has at least 1 child. But the deep size of elements is unknown.

Here an example what the table looks like

------------
| Id*      |<---
|          |   |
| ParentId |----
| Name     |
------------

My task is to query (with EntityFramework Core) the RootId for a given fruit. Of course I could load all Entities from db and do it in memory but that is not clever. Have you any idea how I can write my Linq that this task happens on database?

Do you know if it is possible to call this CTE via linq-query and is it also possible to mock this CTE for unit testing. — Prophet, Feb 17 '21 at 12:49
I found out how to call the CTE in my linq-query. So now the last question is, how I can mock it for unit tests. — Prophet, Feb 17 '21 at 14:04
Calling CTE works with the "DbFunction"-Attribute but I don't know how to create a mock for unit test (see https://stackoverflow.com/questions/53269315/call-dbfunction-with-ef-core-2-1). — Alois, Feb 18 '21 at 06:39

score 1 · Accepted Answer · edited Jun 20 '21 at 07:15

If you don't interest to load all records from database then process them, you have two choices :

Solution 1 : Write a stored procedure and do the processing in the database.
Solution 2 : Write a recursive function that send multiple requests to the database (in the amount of root depth)

Here is a function for solution 2 :

    public async Task<int> GetRoot(int id)
    {
        var current = await _dbContext.Set<Entity>().Where(p => p.Id == id).FirstOrDefaultAsync();

        if (current.ParentId is not null)
        {
            return await GetRoot(current.ParentId.Value);
        }
        else
        {
            return current.Id;
        }
    }

Get recursive rootID of parent-child-table with ef-core

1 Answers1