OpenAccess first look issues

8 posts, 0 answers
  1. AV
    AV avatar
    2 posts
    Member since:
    May 2006

    Posted 22 May 2011 Link to this post

    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?

































  2. Ady
    Admin
    Ady avatar
    589 posts

    Posted 25 May 2011 Link to this post

    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
    Q1’11 SP1 of Telerik OpenAccess is available for download; also available is the Q2'11 Roadmap for Telerik OpenAccess ORM.
  3. DevCraft banner
  4. AV
    AV avatar
    2 posts
    Member since:
    May 2006

    Posted 25 May 2011 Link to this post

    Hello.
    Thank you for the explanations. 
    Could you make some estimations about release date of "stored procedures advanced functionality"? :)
  5. Ady
    Admin
    Ady avatar
    589 posts

    Posted 31 May 2011 Link to this post

    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
    Q1’11 SP1 of Telerik OpenAccess is available for download; also available is the Q2'11 Roadmap for Telerik OpenAccess ORM.
  6. IT
    IT avatar
    26 posts
    Member since:
    Dec 2008

    Posted 17 Jun 2011 Link to this post

    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
  7. Ady
    Admin
    Ady avatar
    589 posts

    Posted 20 Jun 2011 Link to this post

    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
    Q1’11 SP1 of Telerik OpenAccess is available for download; also available is the Q2'11 Roadmap for Telerik OpenAccess ORM.
  8. N Mackay
    N Mackay avatar
    228 posts
    Member since:
    Dec 2010

    Posted 20 Jun 2011 Link to this post

    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.
  9. IT
    IT avatar
    26 posts
    Member since:
    Dec 2008

    Posted 20 Jun 2011 Link to this post

    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
Back to Top
DevCraft banner