Sql Anywhere 11 Skip & Take not working

7 posts, 0 answers
  1. Manuel
    Manuel avatar
    4 posts
    Member since:
    Aug 2013

    Posted 04 Jun 2014 Link to this post

    Hi Guys!

    I'm currently evaluating the DataAccess project and try to get it running with our database.

    We are running SQLAnywhere 11.

    Object and mapping are nothing special:
    1.public class Akt
    2.   {
    3.       public int Id { get; private set; }
    4. 
    5.       public string Identifier { get; set; }
    6.   }
    01.public class AktMapping : MappingConfiguration<Akt>
    02.   {
    03.       public AktMapping()
    04.       {
    05.           MapType(m => new
    06.           {
    07.               AKTID = m.Id,
    08.               BEZEICHNUNG = m.Identifier
    09.           }).ToTable("AKT");
    10. 
    11. 
    12.           HasProperty(m => m.Id).IsIdentity(KeyGenerator.Autoinc);
    13. 
    14.           HasProperty(m => m.Identifier).HasLength(DomainTypes.T_SEARCH_KEYS);
    15.       }
    16.   }


    No trouble with this so far, but something is very strange when you execute the following statement:

    1.var array = context.GetAll<Akt>()
    2.                   .Skip(10)
    3.                   .Take(201).ToArray();

    the following statement gets dropped to the database 

    1.SELECT a."AKTID" COL1, a."BEZEICHNUNG" COL2 FROM "AKT" a


    shouldn't get the following sql generated?


    1.select top 201 start at 10 a."AKTID" COL1, a."BEZEICHNUNG" COL2 FROM "AKT" a

    Is there something i'm missing or is this a bug? 
    Is there a way i could write a custom provider for SQLAnyhwere or some way to debug the generation of the sql statements?

    Thanks 
    Manuel
  2. Viktor Zhivkov
    Admin
    Viktor Zhivkov avatar
    291 posts

    Posted 06 Jun 2014 Link to this post

    Hi Manuel,

    The behavior that you have experienced is due to design decision not to provide database server side paging due to the "un-orthodoxal" way it is done in SQL Anywhere.
    I am afraid that you will not be able to plug your own custom provider if you want to modify the original behavior.
    In order to debug your queries and have better overview of what is happening in the LINQ translation you can:
    • call .ToString() to any IQueryable<T> query to see the SQL and any client side expression that will be executed
    • or use openAccessContext.Log property that will show you all database calls including insert, update, delete and queries.
    In both cases you can control the amount of information using the LogEvents level setting (for example to include all parameter values).

    If you need any further assistance getting to know Telerik Data Access do not hesitate to contact us with your questions.

    Regards,
    Viktor Zhivkov
    Telerik
     
    OpenAccess ORM is now Telerik Data Access. For more information on the new names, please, check out the Telerik Product Map.
     
  3. DevCraft banner
  4. Manuel
    Manuel avatar
    4 posts
    Member since:
    Aug 2013

    Posted 06 Jun 2014 in reply to Viktor Zhivkov Link to this post

    Hi Viktor,

    thanks for your answer.
    I don't think this is a very "un-orthodoxal" way of doing a query in a paging szenario. but maybe you can provide me a better way of handling pagination?

    However, i'm not generating this kind of query, but a third party vendor (dx) with their server mode support, but i had not done this in any other way.


    i have another issue, and i'd like to have a guidiance in this evaluation phase, i'm primary interested in Open Access. Is there any specific support channel (for a quicker response) other than this public forum? DevCraft subscription is a heavy load to take at the moment, but i'd like to pay for OpenAccess support.

    I'm working as an Consultant on a prototype, following all your documentations, but failing with some basic tasks like M-M Relationships in FluentModelFirst (generating ArgumentNullExceptions)

    Thanks Manuel








  5. Manuel
    Manuel avatar
    4 posts
    Member since:
    Aug 2013

    Posted 06 Jun 2014 in reply to Manuel Link to this post

    Hi Viktor,

    if i reread your post is ServerSidePaging supported in SqlAnywhere?

    Greetings, Manuel
  6. Viktor Zhivkov
    Admin
    Viktor Zhivkov avatar
    291 posts

    Posted 09 Jun 2014 Link to this post

    Hi Manuel,

    I am sorry that I was not clear enough the first time - database server paging as result of LINQ .Skip() and .Take() expressions is not supported by Telerik Data Access when targeting SQL Anywhere.
    We have not implemented this translation because SQL Anywhere uses 
    SELECT TOP(N) START AT M ...
    Which is quite different than the paging in most other database servers.

    Regarding the options to get Telerik Data Access 24 hour support - unfortunately we are not offering it as separate package and the way to acquire it for the time being is by getting a DevCraft license.

    If you have any questions, do not hesitate to post them in our forums and we will do our best to get back to you in timely fashion.

    Regards,
    Viktor Zhivkov
    Telerik
     
    OpenAccess ORM is now Telerik Data Access. For more information on the new names, please, check out the Telerik Product Map.
     
  7. Manuel
    Manuel avatar
    4 posts
    Member since:
    Aug 2013

    Posted 10 Jun 2014 in reply to Viktor Zhivkov Link to this post

    Hi Viktor,

    thanks for your answer.

    This is quite bad, that makes the product quite useless to us :(

    i can't imaging any product beside a simple todo-list application that can well function without pagination.

    Is this feature on the backlog, or can i make a feature request?


    For my M-n problem i was giving 2 objects the same table name (stupid me)


    Greetings Manuel
  8. Viktor Zhivkov
    Admin
    Viktor Zhivkov avatar
    291 posts

    Posted 11 Jun 2014 Link to this post

    Hi Manuel,

    You can add this as a feature request in our Ideas and Feedback portal.
    In general we are weighting the support (likes) that each request has received together with it's relative complexity before starting to implement one. 

    Regards,
    Viktor Zhivkov
    Telerik
     
    OpenAccess ORM is now Telerik Data Access. For more information on the new names, please, check out the Telerik Product Map.
     
Back to Top
DevCraft banner