extended property for a business object

[Gopinath]

Hello Team,

I am using telerik orm 2011.2.713 version. Need your help in extending my business object with additional property which has source as an sql query.

For ex: Below is table

EmpNo     Name          Mgr.Id
100            xxx            104
102            yyy           104
104            zzz            

I have the above table mapped as an business object already using reverse mapping wizard. In the Bo, I need to additional property that says employee count, which should retuen the no. of employees report to a specific employee.
so aprt from above 3 properties, additional property of employeecount. this property for object 104 should return 2 as 2 employess report to him. Could you pls. help?

BTW, i do not want to use view which reduceds the application performance as the above bo is heavily used in the application.

Thanks,
Gopi

[Viktor Zhivkov]

Hi Gopinath,

Your question is very interesting and there are several possible solutions. The choice which one to implement is up to you and largely depends on the scenario you are facing.
Here are my suggestions:

1. Create a stored procedure that loads all the data from the Employees data table and calculates the number of employees each manager has. Map the result using out Stored Procedure Editor to a complex type and use the result as read-only view of the employees data. Use the normal generated persistent class for create, update and delete scenarios
Pros: You hit the "complex" aggregate query only when you need the employees count and use the normal data context features to all operations that don't require the calculated column. This can save you some performance issues.
Cons: You need to maintain two almost identical Employee types and make sure you are using the proper one in each case. This increases code complexity.


Create a view in the database that contains the additional calculated column and map it in your domain context instead of the Employees data table. OpenAccess will handle all CRUD operations. 
Pros: Code complexity is very low because you are using standard OpenAccess functionality. You have very good chance to utilize OpenAccess cache when querying the view and it will make up for any performance lost due the view.
Cons: The view will calculate the number of manager's employees even it is not required in the particular use case.


Use standard persistent types generated by OpenAccess and create a new scalar stored procedure that calculates the number of manager's employees on demand. Map the stored procedure using the Stored Procedure Editor. Call the data context method mapped to the new procedure each time you need the additional data.
Pros: Suitable if the number of employees is required rarely because calculation is done on demand.
Cons: If you need to show a grid full of employees and you are required to show the employees numbers this approach get very inefficient because it will issue a query to the database for each row.

You can find more information how to use our Stored Procedure Editor and Complex Types in this video.

My personal preference will be Option 2 because it doesn't introduce any more complexity in the code and the performance cost is fairly low. But your choice depends on the scenario and the business requirements that are present to you.

If you need any further assistance don't hesitate to contact us.

Regards,
Viktor Zhivkov
the Telerik team

[Gopinath]

Hello,

Thank you very much. I would like to go ahead with option 2 as you suggested.

Could you please redirect me to any article that explains openaccess cache for performance improvement. Because I already see some performance issues related to that table.

Thanks,
Gopi

[Viktor Zhivkov]

Hi,

I am glad you have found my suggestions useful.

OpenAccess ORM supports two levels of caching:

• Level 1 (L1 in short) - embedded in single data context. 
• Level 2 (L2 in short) - shared between all data context in a single application domain. Very handy in multi-thread applications (like Web Applications).

You can get started in using our L1 and L2 Cache reading these articles:

• Caching Overview 
• Caching API Short Reference
• Enable L2 Cache - via Visual Designer, via Code

If you like to go even further and utilize the cache in distributed environment (like server farm) check out this article.

Aside from employing our cache facility you can read Performance Considerations article.
You can also use our Profiler and Tuning Advisor to identify bottlenecks and parts of code that are slow.

Let me know if you need any assistance and how it worked out in your scenario.

Greetings,
Viktor Zhivkov
the Telerik team