Hi all, apologies if this has been discussed elsewhere; I did look but couldn’t find anything.

I have a pretty large graph to store in an SQL Server database; it maps accountability within large organisations. One ‘party’ might be accountable to many other parent parties, who in-turn are also accountable to other parties etc. At the moment I have an adjacency list model. A very much simplified example of the schema is as follows:

Party - ID (PK) - Name - Etc

Accountability - ParentPartyId (PK, FK) - ChildPartyId (PK, FK) - AccountabilityTypeID

This is all very well. Possibly not the best way to store a graph? But I don’t know of a better one (If it were a hierarchy, I’d use a nested set). In order to speed things up a bit and save on round trips to the database, I’m actually holding the entire accountability model (data from the accountability table, mapped on to an object) in the application’s cache. That way I can efficiently traverse it in memory. However, this object only stores the accountability, not the actual parties themselves - to do so take up far too much memory (the graphs can be huge and the party objects are large). My problem is with deciding on the most efficient way of retrieving the party data. For example, one of the more common operations will be to retrieve all parties that are accountable to a particular ‘parent’ party, both directly and indirectly (including those accountable to an accountable child and so on).

One thing I can do is traverse my in-memory accountability model collecting all of the accountable child ids and then create a predicate expression to retrieve a collection of parties with those Ids. The problem with this is that the list of child ids could be huge (thousands) so I’m pretty sure generating a query with ‘WHERE ID IN (thousands of ids…)’ is not the best way to go?

Another option is to find a way of creating a temporary table and inner join it on to the party table. Is this even possible within LLBLGen? I could also create some sort of recursive stored procedure and map the results on to an entity collection but I’d rather avoid this as we’ve already got a hell of a lot to maintain and we’re adding fields to the party table quite often (it’s an evolving system).

I’d be very grateful if anyone could offer any pointers or advice. I'm using LLBLGen Pro v3.1.

Thanks.

IMHO, the best approach, is to fetch both tables separately. Then use a dictionary or a hashtable to link them in memory.

Thanks for the response. Do you mean fetch both tables into entity collections containing all records? I'm a bit worried about the performance implications of populating an entity collection with up to 65000 entities.

I could just get the records using a DataReader, then map them on to entities during a read through but then would I have to maintain the fields in a projector?

I guess what I really want to do here is populate an entity collection but only those with certain IDs... But without performing that filter within the database. I need to intercept the population code of the entity collection from the underlying reader. Possible?

Thanks.

Do you mean fetch both tables into entity collections containing all records? I'm a bit worried about the performance implications of populating an entity collection with up to 65000 entities.

Yes I ment that, and you can indeed fetch both collections with the filtering you want.

We had a lot of debates on this over the years. Please check this thread for pointers http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=3208

I found this:

http://www.codeproject.com/Articles/22824/A-Model-to-Represent-Directed-Acyclic-Graphs-DAG-o

Looks like a sensible approach... Similar in theory to your (Frans) precalc approach in that you need to do most of the work on insert/delete. But it's all in a single table and it will also me do things like select and entire sub-tree and related 'parties' with a single query and will still allow me to use LLBLGen.

Hopefully this will do the trick.