c# - Recursive linq query -

i have following object structure.

public class study  {     public guid? previousstudyversionid { get; set; }     public guid studyid { get; set; }     //other members left brevity } 

it persisted using entity framework code first.

it results in table this

previousstudyversionid                  studyid ef90f9dc-c588-4136-8aae-a00e010ce87b    e4315cfd-9638-4225-998e-a00e010ceeec null                                    1c965285-788a-4b67-9894-3d0d46949f11 1c965285-788a-4b67-9894-3d0d46949f11    7095b746-8d32-4cc5-80a9-a00e010ce0ea 7095b746-8d32-4cc5-80a9-a00e010ce0ea    ef90f9dc-c588-4136-8aae-a00e010ce87b 

now want query studyid's recursive. came following solution:

so when call method in repository getallstudyversionids(new guid("7095b746-8d32-4cc5-80a9-a00e010ce0ea")) returns me 4 studyid's.

    public ienumerable<guid> getallstudyversionids(guid studyid)     {         return searchpairsforward(studyid).select(s => s.item1)             .union(searchpairsbackward(studyid).select(s => s.item1)).distinct();     }      private ienumerable<tuple<guid, guid?>> searchpairsforward(guid studyid)     {         var result =             getall().where(s => s.previousstudyversionid == studyid).tolist()             .select(s => new tuple<guid, guid?>(s.studyid, s.previousstudyversionid));         result = result.traverse(a => searchpairsforward(a.item1));         return result;     }      private ienumerable<tuple<guid, guid?>> searchpairsbackward(guid studyid)     {         var result = getall().where(s => s.studyid == studyid).tolist()             .select(s => new tuple<guid, guid?>(s.studyid, s.previousstudyversionid));         result = result.traverse(a => a.item2.hasvalue ? searchpairsbackward(a.item2.value) : enumerable.empty<tuple<guid, guid?>>());         return result;     } 

this implementetion of extension method.

public static class myextensions {     public static ienumerable<t> traverse<t>(this ienumerable<t> source, func<t, ienumerable<t>> fnrecurse)     {         foreach (var item in source)         {             yield return item;             var seqrecurse = fnrecurse(item);             if (seqrecurse == null) continue;             foreach (var itemrecurse in traverse(seqrecurse, fnrecurse))             {                 yield return itemrecurse;             }         }     } } 

is there way of moving closer database (iqueryable) , optimize code.

i have done making recursive table function pulling parent records based on id , creating view produce list each record , parents. consume in ef model , associate able use linq.