Howto dynamic sql and pageing?

5 posts, 1 answers
  1. Richard Koslik
    Richard Koslik avatar
    71 posts
    Member since:
    Apr 2010

    Posted 02 Oct 2012 Link to this post

    Hello,

    is it possible to use dynamic sorting and also serverside pageing in a linq statement?
    In my Silverlight WCF-RIA application i request a data-page, for example from my customer table.
    So i call my loadoperation...

    public LoadOperation<VW_BP> GetPageovBP2s(BLS_DomainContext dcAct,
          string sSearchText,
          int iPage,
          int iItemsPerPage,
          string sSortOrder,
          Action<LoadOperation<VW_BP>> op)
            {
                EntityQuery<VW_BP> loadDatas = dcAct.GetPageovBPs2Query(sSearchText, iPage, iItemsPerPage, sSortOrder);
         loadDatas.IncludeTotalCount = true;
                return dcAct.Load(loadDatas, LoadBehavior.MergeIntoCurrent, op, null);
            }

    On Serverside i have that..

    public IQueryable<VW_BP> GetPageovBPs2(string sSearchText, int iPage, int iItemsPerPage, string sSortOrder, out int totalCount)
        {
            // Get Page of Customer-Table
            totalCount = 0;
            if ((sSearchText != null) &&
                (sSearchText != ""))
            {
                totalCount = this.DataContext.ExecuteQuery<int>("select count(*) from bls.vw_bp where " +
                            "bls.fu_check_firma_rep('" + sCRepcode + "',bp_s_crepcode)=1 and " +
                            "bls.fu_check_firma_rep('" + sLRepcode + "',bp_s_lrepcode)=1 and (" +
                            "nvl(upper(bp_s_nr),'null') like '%" + sSearchText.ToUpper() + "%' or " +
                            "nvl(upper(bp_s_name),'null') like '%" + sSearchText.ToUpper() + "%' or " +
                            "nvl(upper(bp_s_postcode),'null') like '%" + sSearchText.ToUpper() + "%' or " +
                            "nvl(upper(bp_s_cityname),'null') like '%" + sSearchText.ToUpper() + "%' or " +
                            "nvl(upper(bp_s_host_nr),'null') like '%" + sSearchText.ToUpper() + "%' or " +
                            "nvl(upper(bp_s_reference),'null') like '%" + sSearchText.ToUpper() + "%') and " +
                            "bp_b_rcpt <> " + iRCPT.ToString() + " and bp_b_bip <> " + iBIP.ToString() + " and bp_b_db <> " + iDB.ToString() + " and bp_b_cr <> " + iCR.ToString()).FirstOrDefault();

                return (from c in this.DataContext.VW_BPs
                             .Where(p => ((p.BP_S_NR != null && p.BP_S_NR.ToString().ToUpper().Contains(sSearchText.ToUpper()))) ||
                                          (p.BP_S_NAME != null && p.BP_S_NAME.ToUpper().Contains(sSearchText.ToUpper())) ||
                                          (p.BP_S_POSTCODE != null && p.BP_S_POSTCODE.ToUpper().Contains(sSearchText.ToUpper())) ||
                                          (p.BP_S_CITYNAME != null && p.BP_S_CITYNAME.ToUpper().Contains(sSearchText.ToUpper())) ||
                                          (p.BP_S_HOST_NR != null && p.BP_S_HOST_NR.ToUpper().Contains(sSearchText.ToUpper())) ||
                                          (p.BP_S_REFERENCE != null && p.BP_S_REFERENCE.ToUpper().Contains(sSearchText.ToUpper())))
                        select c)
                        .OrderBy(sSortOrder)
          .Skip((iPage - 1) * iItemsPerPage)
                        .Take(iItemsPerPage);
            }
            else
            {
                totalCount = this.DataContext.ExecuteQuery<int>("select count(*) from bls.vw_bp").FirstOrDefault();

                return (from c in this.DataContext.VW_BPs
                         select c)
                        .OrderBy(sSortOrder)
          .Skip((iPage - 1) * iItemsPerPage)
                        .Take(iItemsPerPage);
            }
        }

    The variable "sSortOrder" is something like "BP_S_NAME ASC, BP_S_CITY DESC"....
    To do this i use the Dynamic.cs Class from MS which Extends the Queryable type...

    When i remove the OrderBy in the Linq statement the ORM creates the correct pageing statement for the database (ORACLE).

    Is there any way to have dynamic sorting and pageing correctly sent to the DB?

    Glad for any ideas or solutions...
    regards Richard.


  2. Viktor Zhivkov
    Admin
    Viktor Zhivkov avatar
    291 posts

    Posted 05 Oct 2012 Link to this post

    Hi Richard,

    You have posted the same question in our support system and our communication will be executed there.
    Once the issue is resolved I will post the solution here so our community can share the knowledge.

    Please do not post in this thread instead of the thread in the support system.

    All the best,
    Viktor Zhivkov
    the Telerik team
    Follow @OpenAccessORM Twitter channel to be the first one to get the latest updates on new releases, tips and tricks and sneak peeks at our product labs!
  3. DevCraft banner
  4. Viktor Zhivkov
    Admin
    Viktor Zhivkov avatar
    291 posts

    Posted 13 Nov 2012 Link to this post

    Hello,

    You can apply a work around this issue. 

    The change in code is relatively simple - move the OrderBy clause before anything else in the query. Your code after the modifications should look like this:
    1.var query = dataModel.Products.OrderBy("Productname DESC, Unitprice ASC").Skip(20).Take(10);
    2.query = (from p in query
    3.      join o in dataModel.VW_REPCODEs on p.Crepcode equals o.REPCODE2SCODE
    4.      join i in dataModel.VW_REPCODEs on p.Lrepcode equals i.REPCODE2SCODE
    5.      where (o.REPCODEBVALID == 1) && (o.REPCODE1SCODE == "1010000000000000") &&
    6.           (i.REPCODEBVALID == 1) && (i.REPCODE1SCODE == "1000000000000000") &&
    7.           p.Discontinued != 1
    8.      select p);

    Now the translation of the LINQ query to SQL should work as intended and should enable server side paging.

    Regards,
    Viktor Zhivkov
    the Telerik team
    Telerik OpenAccess ORM Meets ASP.NET Web API. Read more.
  5. Richard Koslik
    Richard Koslik avatar
    71 posts
    Member since:
    Apr 2010

    Posted 19 Nov 2012 Link to this post

    Hello Viktor,

    now after implementing your workaround (about 50h of work!) there is again a big problem...
    The "TotalEntityCount" of the loadoperation on clientside dose not work anymore! It returns only the count of the take statement.
    So if i have now a Take of 20, also TotalEntityCount is 20...but the database hold more then 1000 with the specified search option...

    What can you do for me in this case?

    regards Richard.
  6. Answer
    Richard Koslik
    Richard Koslik avatar
    71 posts
    Member since:
    Apr 2010

    Posted 20 Nov 2012 Link to this post

    The solution for now is to only have the OrderBy on the "Top" of the query and the Skip&Take at the End...

    Example:
    function IQueryable<Table> (string sSortby, int iPage, int iTake, out int iCountRecords)
    {
    var q = Table.OrderBy(sSortby);

    q = from p in q
          join....
          join....
          where ....
          select p;

    iCountRecords = q.Count();
    return q.Skip((iPage-1)*iTake).Take(iTake);
    }
Back to Top
DevCraft banner