OpenAccess first look issues

[AV]

Hello.
We are choosing ORM system for our new project and consider OpenAccess as potential.
So, we spent some time to research, and found out some bugs / unexpected behavior of OpenAccess tool.


The list is below, so please, comment / provide your feedback for them.


1. Mapping of entities to database through stored procedures is the point for us, and, it was where we started from. 


So
1.1 Entity CUD mapping to stored procedures:
1.1.1 There is no way to map CUD operations to existing stored procedure. Not nice surpise.
1.1.2 There is no transparent and straightforward way to rename CUD stored procedure. Yes, we found out, that if we go to model schema explorer, choose the procedure, and rename it, it will work out,
but why it should be so unclear? 
And what about weird naming style for CUD stored procedures? Setting "Create" stored procedure for entity "RentalOrder" (CarRentWebSite Telerik example), we got someting named 'sp_oa_ins__rental_orders'.
Looks like we live in 197X, and no way and time for 20XX naming conventions.


Is it possible to provide at least naming template for auto-generated stored procedures?
Is it possible to provide transparent way to rename auto-generated stored procedures fo CUD operations?
Is it possible to use existing (not autogenerated) stored procedures for entity CUD operations?
Is it possible to modify autogenerated CUD stored procedures (adding some kind of logging / error handling for database side) and will have no issues during database / OpenAccess schema synchronization?
Is it possible to modify options of CUD procedure auto generation (just to remove some calculated / autogenerated fields)?


P.S. One more reason to provide ability to modify / use existing CUD stored procedures is the fact that autogenerated sps can contain some errors:


Input:


-- Generating stored procedure for OpenAccess
CREATE PROCEDURE [sp_oa_ins__rental_orders] ( @TankLevel varchar(40) = NULL, @RentStartDate datetime = NULL, @RentEndDate datetime = NULL, @RateApplied money = NULL, @OrderTotal money = NULL, @OrderStatus varchar(50) = NULL, @MileageStart int = NULL, @MileageEnd int = NULL, @EmployeeID int, @Days int = NULL, @DateProcessed datetime = NULL, @CustomerID int, @CarID int )  AS 
INSERT INTO [RentalOrders] ( [TankLevel] , [RentStartDate] , [RentEndDate] , [RateApplied] , [OrderTotal] , [OrderStatus] , [MileageStart] , [MileageEnd] , [EmployeeID] , [Days] , [DateProcessed] , [CustomerID] , [CarID] ) VALUES ( @TankLevel , @RentStartDate , @RentEndDate , @RateApplied , @OrderTotal , @OrderStatus , @MileageStart , @MileageEnd , @EmployeeID , @Days , @DateProcessed , @CustomerID , @CarID )  
select scope_identity();
RETURN


go


Output:
Msg 271, Level 16, State 1, Procedure sp_oa_ins__rental_orders, Line 3
The column "OrderTotal" cannot be modified because it is either a computed column or is the result of a UNION operator.
Msg 271, Level 16, State 1, Procedure sp_oa_ins__rental_orders, Line 3
The column "Days" cannot be modified because it is either a computed column or is the result of a UNION operator.


1.2 Mapping "Read" stored procedures to entities
For CarRentWebSite Telerik we create a simple stored procedure, returning all car list:


CREATE PROCEDURE [dbo].[SelectCars]
AS
BEGIN
SELECT [CarID]
,[TagNumber]
,[Make]
,[Model]
,[CarYear]
,[Category]
,[mp3layer]
,[DVDPlayer]
,[AirConditioner]
,[ABS]
,[ASR]
,[Navigation]
,[Available]
FROM  [dbo].[Cars]
// some search criteria can be added later
END


Then we update model from database and get stored procedure mapped method:


[Function("SelectCars")]
public object[] SelectCars()
{
object[] queryResult = this.ExecuteStoredProcedure<object>("'SelectCars' ", null);
    return queryResult;
}
, that will return set of anonymous type instances, if we execute it.


If we modify it like this
[Function("SelectCars")]
public Car[] SelectCars()
{
return this.ExecuteStoredProcedure<Car>("'SelectCars' ", null);
}
, we will get array of Car instances without any issues, so, OpenAcces framework support such kind of thing.
BUT WHY OpenAccess DESIGN TOOLS DOESN'T SUPPORT IT? Is it still Rapid Application Development tool?


So, is there any way to modify settings of stored procedurs return data types without writing additional code?
 
1.2. "Update from database" issues
We create some CRUD stored procedures on SQL Server 2008 SofiaCarRental1 (database from CarRentWebSite Telerik example) with object oriented naming style:
dbo.[Cars.Select],
dbo.[Cars.Insert],
dbo.[Cars.Update],
dbo.[Cars.Delete],


and try to run "Update From Database" command.


"Update from database" wizard just didn't "see" the procedures, so, no way to update OpenAccess model with them.


Then we renamed stored procedures:
dbo.[Cars.Select] -> dbo.[SelectCars]
dbo.[Cars.Insert] -> dbo.[InsertCars]
dbo.[Cars.Update] -> dbo.[UpdateCars]
dbo.[Cars.Delete] -> dbo.[DeleteCars]


After that, "Update from database" wizard found out, that something changed on database side.


Is there any chance that the issue will be fixed nearest time?


1.3. Code generation questions
1.3.1 Why OpenAccess generate entity classes with properties that use "container" fields like this:
public partial class Car
{
private int carID;
[Column("CarID", OpenAccessType = OpenAccessType.Int32, IsBackendCalculated = true, IsPrimaryKey = true, SqlType = "int")]
[Storage("carID")]
public virtual int CarID 
{ 
   get
   {
       return this.carID;
   }
   set
   {
       this.carID = value;
   }
}

Why it didn't use auto properties:


public partial class Car
{
private int carID;
[Column("CarID", OpenAccessType = OpenAccessType.Int32, IsBackendCalculated = true, IsPrimaryKey = true, SqlType = "int")]
[Storage("carID")]
public virtual int CarID 
{ 
   get;
   set;
}


1.3.2 Why all properties of entity classes are virtual?

[Ady]

Hi AV,

 Here is some explanation to each of your points:

 1.1.1 - We did support mapping existing stored procedures to CUD operations in the 'classic' version but this feature is currently not available via the new Domain model designer. We will definitely consider adding this feature in one of the next releases.
 1.1.2 - Yes I totally agree that changing the default generated stored procedure name is very cumbersome and not obvious. We will improve the usability there.
 1.1.3 - If you would like to modify auto-generated stored procedures, you can use the 'Copy to Clipboard' button to copy the generated script and edit in manully so that you can add logging/error handling etc. You would need to execute this script externally (not using OpenAccess).
1.1.4 Specifying the return type of stored procedures - We are in the process of resdesigning several aspects of the stored procedure support and we will definitely add the ability to specify the return type.

1.2 - This will be fixed

1.3.1 - We will provide a template for generating Auto properties by the Q2 release.

1.3.2 - Generated properties are virtual so that you can always override the behaviour in  a partial class in case you need aditional behavior. The class file gets regenerated every time you save the .rlinq file and hence you would lose your changes if you made them in that file.

We know the stored procedure related features need more usability and will definitely work on that.
Do get back in case you need further clarification.

Regards,
Ady
the Telerik team

[AV]

Hello.
Thank you for the explanations. 
Could you make some estimations about release date of "stored procedures advanced functionality"? :)

[Ady]

Hi AV,

 A simpler API to execute stored procedures and SQL statements is scheduled for the Q2 release (mid July). The UI related changes would be available after that. We do not have an exact date for that yet but would definitely give usability a high priority.

Regards,
Ady
the Telerik team

[IT]

I have to agree in regard to stored procedures

it was the one issue for me in regard to using openaccess
and the reason why I went back to Entity Framework.


really hope to see better stored procedure integration in the next version

Keep up the great work Telerik

[Ady]

Hello Brian,

We are aware of the limitations/difficulties in the current stored procedure support and are working towards improving the experience there. We will have a better,easier API to execute stored procedures via the context for the Q2 release. The improved UI would be available in one of the next releases after the Q2.

All the best,
Ady
the Telerik team

[N Mackay]

Yep,

Stored procedure support was a deal breaker for us too unfortunately, we had to go with another ORM tool instead. Other aspects of OpenAccess ORM did impress though so I keep checking back on it's progress, oData, RIA services, WCF support etc seemed very good.

The more alternatives to EntityFramework the better.

Also the ORM support team are very helpful.

[IT]

Dont worry I know the Telerik folks are on the case.

I also keep checking back for possible updates to OpenAccess.

Looking forward to seeing how the stored procedure support works out in future versions