Greetings,

If I want to have a linked property (ReadOnly) or an Own property (Read-Write) that looks up its data from another entity based on an ID field.

example: upon extending the Employee entity, I wanted to add a ReadOnly property (DepartmentName) that looks up the departments name from the Department table.

I know I could use the RelationPredicateBucket to walk the PK-FK relation between the two tables and obtain whatever fields I want from the Department entity, but my question really concerns optimizing this process to minimize round-trips to the presistance storage. Is there a best practice approach to such a scenario?

One approach I am contemplating is to build a dynamic list from the Department entity that I could use to lookup the department's name instead of going back to the database (of course I am wrestling with the synchronization issues when the persistence storage becomes out-of synch with the cached dynamic list).

Did anybody wrestle with such issues? I would like very much to learn from other people’s experiences so that not to travel a dead-end road...

OMAR

If I understand you correctly, you have a concrete entity object, with a read only property, that you want to initialize with the value from another related entity.

Wouldnt the prefetch path accomplish this objective?

Wouldn’t the prefetch path accomplish this objective?

Yes it would. My problem is NOT how to fetch the lookup value but rather what is the optimum way of doing this. In the UI, if I have a combo-box (lists Employees) and a TextBox. Whenever the user selects an employee from the combo-box, the text box would be filled with the looked-up value of that employee's Department. What worries me in such a scenario is the excessive trips to the persistent storage to lookup the Department value whenever the user changes his selection in the UI. I thought that I could somehow cache the list of departments as a data-table dynamic list but then I would have issues of the cached list going out of synch with the DB (another user has updated a department after the cached data-table was obtained). This lead me to the idea of a centralized object space that acts as a gateway to the DB and all the sudden I found myself talking about building what MS are promising in their upcoming .NET... In the meantime, any ideas of how to optimally handle such a scenario or should I just bite the bullet and live with this kind of DB trips

appreciate any insights...

OMAR

Microsoft has a caching application block, thats already written, located at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag/html/CachingBlock.asp.

You could cache the data using the caching block.

During a save operation, use an IConcurrencyPredicateFactory to enforce concurrency checks on the data being saved.

After the save operation, kill the objects being managed in the caching block.

This will save you round trips to the DB and will handle your concurrency issues, but if having a stateless application is important, then this is the wrong approach.

If performance is an issue and roundtrips are noticeable, try tuning the indexes to facilitate the TSQL being executed. This could improve perfomance in such a way that round trips arent noticeable.

Hope this helps.

omar wrote:

If I want to have a linked property (ReadOnly) or an Own property (Read-Write) that looks up its data from another entity based on an ID field.

example: upon extending the Employee entity, I wanted to add a ReadOnly property (DepartmentName) that looks up the departments name from the Department table.

I know I could use the RelationPredicateBucket to walk the PK-FK relation between the two tables and obtain whatever fields I want from the Department entity, but my question really concerns optimizing this process to minimize round-trips to the presistance storage. Is there a best practice approach to such a scenario?

As DevilDog explained, use a prefetch path, that way you know the related entity is there and it will fetch it in a separate query, 1 per type (so if you fetch multiple employees, it will still use 2 queries, one for the employee and one for the department)

One approach I am contemplating is to build a dynamic list from the Department entity that I could use to lookup the department's name instead of going back to the database (of course I am wrestling with the synchronization issues when the persistence storage becomes out-of synch with the cached dynamic list). Did anybody wrestle with such issues? I would like very much to learn from other people’s experiences so that not to travel a dead-end road...

Caching data that doesn't change a lot can help performance. Like a hashtable which contains data stored by a given key which you use to lookup data for a given purpose. Caching is however more efficient when it is done higher up the call-chain. For example: it's way more efficient to cache a webpage for 1 minute, than to cache the data used to build the webpage: with the data-cache you still have to perform the processing to build the webpage.

For stale data issues (data outside the database gets outofsync with the data inside the database), you can setup a variety of systems to get that worked out: for example refresh data in the cache every X minutes, or setup some sort of mechanism which invalidates the cache based on parameters like a poll every 10 minutes in the database.

Caching of data is however often not that fast as you might think, especially when the data changes a lot: as soon as you have to verify fetched data with data in a cache, the process will become slower than when you fetch the data directly, again. After all: the database caches query resultsets also, as well as query execution plans, which makes it often faster to refetch the data than to walk lists in memory sequentially.