Linq query condition on self-referencing entity

Question

I am trying to retrieve all entities of type Queried from the database where Referenced property or it's ancestor for which its label (meaning referenced.Parent.ChildLabel) is equal to some given label value exampleLabel, has an Id equal to exampleId.

I tried using:

var result = nhibernateSession
    .Query<Queried>()
    .Where(queried => queried.SelfReferencing.GetSelfOrAncestor("exampleLabel") == exampleId)
    .ToList();

but it throws a "System.NotSupportedException", probably because it does not know how to translate the GetSelfOrAncestor into SQL.

The method GetSelfOrAncestor(string label) returns the Id of the SelfReferencing instance on which it was called or it's ancestor which meets the condition that this.Parent.ChildLabel is equal to exampleLabel, otherwise returns 0.

For example, in the following diagram if queried.SelfReferencing would point to the one at Level 2, GetSelfOrAncestor("exampleLabel") would return the Id of the object at Level 1.

http://j.mp/Xl86OP

public class Queried
{
    public int Id { get; set; }
    public SelfReferencing Referenced { get; set; }
}

public class SelfReferencing
{
    public SelfReferencing Parent { get; set; }
    private IList<SelfReferencing > children = new List<SelfReferencing >();
    public virtual IList<SelfReferencing > Children
    {
        get
        {
            return children;
        }
        set
        {
            children = value;
        }
    }
    public string ChildLabel { get; set; }
}

Any help on how to achieve this would be highly appreciated :)

Can you provide a sample object graph please? Just to see how your selfreferencing works. Your parent can be in the children collections, right? — Mare Infinitus, Feb 05 '13 at 12:24
@MareInfinitus: the graph is tree-like, so there are no cycles. Basically I start at some node and check if it is the right label, if not, go to the parent and check the label again, and so on, until it matches the required label. Once it matches. I need to compare the `Id` of that `SelfReference` with a given `Id`. I tried to give a graphical example in that picture, where I am looking for the node having `parent.ChildLabel == "exampleLabel"`. — Răzvan Flavius Panda, Feb 05 '13 at 12:32
LINQ in itself is not the best tool for recursive querying and when it should be translated to SQL it's even worse. But try to think of a straightforward SQL statement that does the job - can't be done. Recursive querying in plain SQL requires common table expressions. There is no linq-to-whatever that supports that. Usually the best option is a view with a CTE, otherwise: a query at each level. — Gert Arnold, Feb 06 '13 at 08:21

Mare Infinitus · Answer 1 · 2013-02-05T16:51:18.767

0

To achieve what you want, I would provide a method in SelfReferencing that does the search for the label through the object graph.

Should be something like this: (Warning, not tested yet!)

    public bool ContainsLabel(string label)
    {
        if (this.ChildLabel.Equals(label))
        {
            return true;
        }
        else
        {
            foreach (var child in Children)
            {
                return child.ContainsLabel(label);
            }
        }

        return false;
    }

You can use it then as follows:

var result = nhibernateSession
    .Query<Queried>()
    .Where(queried => queried.SelfReferencing.ContainsLabel("exampleLabel"))
    .ToList();

Edited for more convenience in reading.

edited Feb 05 '13 at 16:51

answered Feb 05 '13 at 12:54

Mare Infinitus

8,024
8
64
113

why are you passing `exampleId` in a local variable named `label`? – Răzvan Flavius Panda Feb 05 '13 at 14:33
This is the exampleId from your example code. It is used for comparison with the labels in the `SelfReferencing` tree, to find out whether it is contained or not. Have I misunderstood your example? – Mare Infinitus Feb 05 '13 at 15:26
I don't understand why you are comparing and id with a label: `this.ChildLabel.Equals(label)`. And the matching label node can only be the current one or one of the ancestors, so why are you going down the tree? Sorry for answering after so much time, been busy trying possibilities. – Răzvan Flavius Panda Feb 05 '13 at 16:31
I copied this from your example, but made a small mistake. Your `GetSelfOrAncestor` will return the element containing the label, my `ContainsLabel` just returns `true` or `false`. The key is that the Linq part from your query is decoupled from the tree traversal which is done in `ContainsLabel`, having a good result. If the label is not found, you have to check for null in your case I believe, not seeing your implementation for the `GetSelfOrAncestor`, but this can be somewhat complicated inside the Linq statement. – Mare Infinitus Feb 05 '13 at 16:49

Linq query condition on self-referencing entity

1 Answers1