How to filter, order and query LTree in EF Core

Question

As already described here I'm struggling with LTree and the right way to query. I'm using an object, containing LTree as a column on one of my tables. This is how the object looks like:

using Microsoft.EntityFrameworkCore;

public class MyObject
{
    public Guid Id {get;set;}
    public LTree LTreeColumn {get;set;}
    ....
}

If it is not possible to use the LTree-functions as expected, it's not nice, but also not worst case. But it would be nice, if there is a way, to order and filter a collection, based on LTree.

OrderBy fails, because of missing IComparable implementation. Is there an alternative approach? And how to find the first item on the "tree" given a specific sub-path?

Let's say, I have a structure beginning with Top, followed by Top.One, Top.Two, Top.One.One and so on...

Now I want to find the highest object, beginning with Top in a collection. How can I do this on database and in memory?

And in general (based on a common sub-path) how to evaluate < or > correctly?

Answer 1

Here's what I figured out so far... If someone knows better or more I highly appreciate another answer.

All LTree-functions will be translated to queries. LTree holds NLevel as a property, which can be used for ordering (as query, not in memory):

var ordered = _dbContext.MyObjects.OrderBy(e=>e.LTreeColumn.NLevel)

Highest in specific path:

var highest = _dbContext.MyObjects.Where(e=>e.LTreeColumn.MatchesLQuery("Top.*"))
    .OrderBy(e=>e.LTreeColumn.NLevel).First();

If I need the level of LTreeColumn in an in-memory collection I use these extension-methods:

public static int GetLTreeLevel(this string lTree)
    => lTree.Split('.').Length;

public static int GetLTreeLevel(this LTree lTree)
    => lTree.ToString().GetLTreeLevel();

Compare the level of two instances of MyObject:

var isHigher = first.LTreeColumn.GetLTreeLevel() < second.LTreeColumn.GetLTreeLevel();