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?
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:
If I need the level of LTreeColumn in an in-memory collection I use these extension-methods:
Compare the level of two instances of MyObject: