This is a migrated thread and some comments may be shown as answers.
Howto dynamic sql and pageing?
4 Answers 52 Views
This is a migrated thread and some comments may be shown as answers.
This question is locked. New answers and comments are not allowed.
Richard Koslik
Top achievements
Rank 1
Richard Koslik asked on 02 Oct 2012, 03:49 PM
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.


4 Answers, 1 is accepted

Sort by
0
Viktor Zhivkov
Telerik team
answered on 05 Oct 2012, 10:59 AM
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!
0
Viktor Zhivkov
Telerik team
answered on 13 Nov 2012, 09:32 AM
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.
0
Richard Koslik
Top achievements
Rank 1
answered on 19 Nov 2012, 12:49 PM
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.
0
Accepted
Richard Koslik
Top achievements
Rank 1
answered on 20 Nov 2012, 04:11 PM
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);
}
Tags
LINQ (LINQ specific questions)
Asked by
Richard Koslik
Top achievements
Rank 1
Answers by
Viktor Zhivkov
Telerik team
Richard Koslik
Top achievements
Rank 1
Share this question
or