Fetch history of records using LINQ

Question

I am using entity framework with repository pattern and unit of work objects..

I have an entity Request with properties "RequestId", "OldRequestId", which can be accessed using requestRepository object.

eg: requestRepostiory.GetAll(), requestRepository.GetFiltered(r=> r.Requestid =10)

If I pass a RequestId, it should retrieve me the specific record.
If the OldRequestId is not null in the retrieved record, it should bring the old request data as well.
It should go on until the OldRequestId is null.

I believe you are going to want to use a recursive CTE for this. This is not directly supported in EF (as far as I know) but take a look at this: http://stackoverflow.com/questions/11929535/writing-recursive-cte-using-entity-framework-fluent-syntax-or-inline-syntax — Jason Boyd, Aug 10 '15 at 13:27

score 0 · Answer 1 · answered Aug 10 '15 at 15:36

Simple way would be something like this:

public static IEnumerable<Data> GetRecursive(int id)
{
    while (true)
    {
        var tmp = GetFiltered(x => x.Requestid == id);
        yield return tmp;

        if (tmp.OldRequestId.HasValue)
            id = tmp.OldRequestId.Value;
        else
            yield break;
    }
}

Please note, that this code would run make multiple queries towards the database. Performance won't be the best, but it might work for your scenario.

Fetch history of records using LINQ

1 Answers1