0

I have a table of elements that can be associated to each other (within the same table).

So for example a structure like:

[Id]                      Int 
[ParentWithinThisTableId] Int
[AFieldToQueryAgainst]    NVarCharMax

At the beginning of the search, I will be given a string query and a single Id (call it StartId that points to an element in this table. What I want to do is compare the string query to the [AFieldToQueryAgainst] on the element at the Id given, but also query against the same column for all rows that have [ParentWithinThisTableId] == StartId and then all of the rows that have these ids as [ParentWithinThisTableId].

The only way I can think to do it is recursively like:

    var forms = db.Forms.Where(m => m.ParentWithinThisTableId == this.Id);

    var searchMatches = new List<Form>();

    foreach (var form in forms)
    {
        forms = forms.Concat(AddSearches(query, form.Id));
    }

    foreach (var form in forms)
    {
        if (form.AFieldToQueryAgainst.Contains(query))
        {
            searchMatches.Add(form);
        }
    }

With AddSearches being recursive like:

    private IQueryable<Form> AddSearches(string query, int startId)
    {
        var items = RepositoryProxy.Context.Forms.Where(m => m.ParentWithinThisTableId == startId);

        foreach (var item in items)
        {
            items = items.Concat(AddSearches(query, item.Id));
        }

        return items;
    }

But this takes about 1.8 seconds run time in my testing on a relatively shallow tree. Is there a more efficient way to do this? Possibly avoid iterating each of the IQueryables until they're all compiled somehow?

Tevis
  • 729
  • 1
  • 12
  • 27
  • `efficiently` + `tree structure` + `SQL database`. Pick any two. You can't have all three (other than using some database extensions as I hear Oracle has). – Eric J. May 21 '15 at 20:38
  • This might be a duplicate of this: [searching-a-tree-using-linq](http://stackoverflow.com/questions/7062882/searching-a-tree-using-linq) – SunilK May 21 '15 at 20:39
  • Do you only need to down two levels? Or do you need to recurse until there are no more matches? – Peter May 21 '15 at 21:00
  • I need to recurse until no more matches, I always struggle to describe these things :) – Tevis May 22 '15 at 13:07

1 Answers1

0

why can't you just use a self join ?

var these = (from p in items
                join x in items on p.Id equals x.ParentId
                where x != null
                select x).ToList();
cechode
  • 1,002
  • 1
  • 8
  • 20
  • I believe this gets all rows where *any* `ParentId` is populated right? Is there a way to limit this to only a certain Id and its descendants? – Tevis May 22 '15 at 14:04
  • you can always add a where clause before the join clause to filter out which items (p) are within your requested range :) – cechode May 22 '15 at 16:25