Map property to a scalar-valued function or procedure

[Tevez]

Hi everyone,

I have another question, i'm working with the OA ORM and have mapped some tables. But now i want to add an extra property that is not mapped to a column but to a scalar-valued function.
So the SQL should look a bit like SELECT ColumnA, ColumnB, dbo.fn_get_calculated_value(ColumnC) As ColumnD FROM...

Before i used Hibernate and i could easily add a property that was mapped to a "formula" , but in OA i cannot see how i could do something similar to that..

I found out that i can map a stored procedure, and use that in a property in my non-generated partial class.. but this is not an option, it creates way to many round trips to the database.

Any ideas?

Best Regards,
Tevez

[Kosta Hristov]

Hello Tevez,

 Currently, there is no support for such feature in OpenAccess. A property cannot be mapped to a custom SQL query nor to a stored procedure directly.
Anyway, you should be able to create a property in a partial class, as you said, which utilizes a stored procedure directly. If you want to make only one call to the database, you can cache the value of the property using a nullable field. This will limit the "trips" to the database to only 1 in the first call. Please check the attached project where you can find an example. In this demo, a very simple stored procedure is called :

CREATE PROCEDURE [dbo].[ConsoleApp_Dog_GetDogsCount]
AS
SELECT 1

The OpenAccessContextBase.ExecuteScalar() method is used to executes the stored procedure and obtain the first column of the first row of the query resultset.

In order to run the project don't forget to :
1. Generate the appropriate table in the database
  * right click on the designer and select "Update database from model"
2. Create the stored procedure using the script above.
3. Edit the connection string located in the App.Config file to point to your local database.

I hope this will help you solve your problem. If you have any more questions, please do not hesitate to ask.

All the best,
Kosta
the Telerik team

[Tevez]

Hi Kosta,

Your example look great.. but when i try to use .ExecuteScalar in my project, or when i try to open your project, i don't see the ExecuteScalar method at all!? . I only have .ExecuteStoredProcedure.

Am i missing something? Or are we talking about different versions?

Regards, Tevez

[Kosta Hristov]

Hello Tevez,

The ExecuteScalar method is part of the new ADO API, which was introduced with our Q2 release (2011_2_713). Can you please check if there isn't any version mismatch in your project. Maybe the project in which you are trying to call the ExecuteScalar is still referencing our old Assemblies. The easiest way to fix this problem is to use the auto-upgrade tool available with OpenAccess. You can click on the Upgrade Reference menu item in our OpenAccess menu to active the tool (see the attached image).




Hope this solves the problem.

All the best,
Kosta
the Telerik team

[Tevez]

Hi Kosta,

Thanks for your tips. I've upgraded, and now this method is available. And it works like it should.

But, i'm still far from what my goal was; mapping a property to a scalar function.
I've found a complex workaround for that, but it would be really nice if this UDF mapping would be placed on the OpenAccess roadmap.
My old NHibernate app uses a bunch of those properties and it gives me really powerful possibilities!

Best Regards, Tevez

[Damyan Bogoev]

Hi Tevez,

Firstly I want to thank you for the valuable input.
I am afraid that currently such feature is not on our to-do list but we will consider it for the future versions of the product.

All the best,
Damyan Bogoev
the Telerik team