The Story of My Life

Diary of a noob programmer

Fetch Self Referencing data in EF-Core

TL;DR

Before i begin, let me tell you about my works. I did so many things and got my hand dirty with any thing you may imagine. but not much when it came to tools most people are familiar with. MVC, jQuery,  … were something everybody enjoyed, but I hardly got along with it. and when it come to ASP.net MVC I had only one project in my life; and the customer didn’t event came to see the preview; so the project was terminated. All other usage I had for MVC was to create some test page, which I create to test and provide functionality that my ex-co-worker needed.

My jobs was to work with Web-API and old services, and Application which i wrote from scratch, or even before that, the good old “Windows Form” applications.

So it’s a new chapter for me that in this new company, I again turn my head toward ASP.Net MVC.

The Issue

As i got involved with MVC, they requested me to provide functionally to create a check list tree. The component was done, but soon i find out that not all of the data from database get displayed; actually two layer were displayed.

After searching a while, I found that the new EF-Core (Entity Framework Core) does not provides with tree mapping as strong as what we had in the EF which was implemented based on .NET Framework. there was only two possible option i found on the Internet:

  1. would be use of include per level of data you need to fetch in your tree structure.
  2. would be use of functionality that iterates over children, forcing system to fetch children of each child separately. By my experience I don’t recommend loading them lazily when we need to access and render them at the same time.

So I remembered my SQL class in NIIT courses I took long ago; We fetched tree data using T-SQL command. So i wrote it and execute it using EF-Core, then I wrote a functionality to turn them into a tree ( as data comes in a list from both SQL and EF ), and in the end I notice that before I make the tree, EF include each child in the parent, so I remove that duplicate functionality.

Here’s how we fetch Self-Referencing data (A Tree of data), in an flat collection.

; WITH RCTE as
(
  SELECT f.*, CAST(f.ID AS VarChar(Max)) as Level
  FROM dbo.Favor f
  WHERE f.ParentID IS NULL

  UNION ALL

  SELECT t.*, CAST(t.ID AS VarChar(Max)) + ', ' + r.Level 
  FROM dbo.Favor t  
  INNER JOIN RCTE r
  ON r.ID = t.ParentID
 )
SELECT * From RCTE

RCTE is just a name. CTE stand for Common Table Expression, and RTCE stand for Recursive Common Table Expression.

In opening part we need to specify the first node which we want to fetch, here i went directly for the root.

SELECT f.*, CAST(f.ID AS VarChar(Max)) as Level 
FROM dbo.Favor f 
WHERE f.ParentID IS NULL

Level is a computed column we can use to specify the route from the child to it’s ancestor.

The second part fetch the data of each child it finds using parent ID of the child.

SELECT t.*, CAST(t.ID AS VarChar(Max)) + ', ' + r.Level 
FROM dbo.Favor t 
INNER JOIN RCTE r 
ON r.ID = t.ParentID

and this little command in between, attach two similar list together.

UNION ALL

Now, we have a command that fetch our tree data from database. At this time, we have to tell EF-Core to fetch them.

var result = context.Table.FromSql<MyTableType>(@" 
    ; WITH RCTE as ( 
        SELECT f.*, CAST(f.ID AS VarChar(Max)) as Level 
        FROM dbo.SelfReferencingTable f 
        WHERE f.ParentId IS NULL -- or selected start node, like: f.ID = 1 

        UNION ALL 

        SELECT t.*, CAST(t.ID AS VarChar(Max)) + ', ' + r.Level 
        FROM dbo.SelfReferencingTable t 
        INNER JOIN RCTE r 
        ON r.ID = t.ParentId 
    ) 
    SELECT * From RCTE 
").ToList();

This will result in lot of nodes with their child nodes… all you need to do now is to filter the root node. a NULL value as ParentId indicate a root node for me.

return result.Where(w=>w.ParentID ==null);

Now, you have a single node, which contains any level of it’s descendants.

The End.

Thank you for reading,
Hassan Faghihi.

Leave a Reply

Your email address will not be published. Required fields are marked *.

*
*
You may use these <abbr title="HyperText Markup Language">HTML</abbr> tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>