Howto dynamic sql and pageing?

[Richard Koslik]

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.

[Viktor Zhivkov]

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

[Viktor Zhivkov]

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

[Richard Koslik]

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.

[Richard Koslik]

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);
}