Single table hierarchical data with EF Database First

Question

I am modifying some existing code (with an existing database) to use Entity Framework Code First. One area I am struggling to get to grips with is a table that holds a history of itself. Items are grouped by their original ids (so updates to an item create a new entry, with the same original id) like so

- id = 1, originalid = 1 <-- oldest item in history

- id = 2, originalid = 1 <-- subsequent update

- id = 3, originalid = 1 <-- latest item

etc.

Each item also has a user id, and I often need to get all data for a user, but so that I see the top level (latest) items, with their history as a property of the latest item object, like so

id = 3 (because id 3 is that latest for the grouping with original id 1)
history { id = 1, id = 2 } (the other items with original id 1)

I am getting round this at the moment with a view of latest items, plus an stored procedure to get its history, but I feel it would be more efficient to get everything for the user in a single hit on the database, then rearrange into the groupings in code.

But I have no idea how.

Note I am restricted to .NET 4, so cannot take advantage of some of the feature in EF 5.

Any suggestions?

Thanks in advance.

Answer 1

Subject to only two levels in the hierarchy, you could simply do something like..

int userId = 1;

var query = items.Where(i => i.userid == userId).GroupBy(i => i.originalid).Select(i => new
                    {
                        LatestItem = i.OrderByDescending(x => x.id).First(),
                        History = i.OrderByDescending(x => x.id).Skip(1).Select(x => new
                        {
                            id = x.id,
                            originalid = x.originalid
                        }).ToList()
                    }).ToList();

You'll need to test performance but it should give you what it appears you're after.