locked
Ordering Entity datasource wilth Nulls first RRS feed

  • Question

  • User2057255475 posted

    Hi,

    I have gridview in which some a column has both alphabets and nulls values coming from database(oracle). But when i perform the sort operation on that column it is sorting the data in ascending with NULL values coming at the last. After some analysis i came to know that oracle by default sorts the data with NULLs at the end.

    Is there any way i can override this default behaviour?

    I tried by setting orderby property of Entity Data Source like below.

     <asp:EntityDataSource ID="GridDataSource" runat="server" EnableDelete="True" EnableUpdate="True"
                    ConnectionString="name=HQADataEntities" DefaultContainerName="HQADataEntities" OrderBy="CASE 
                    WHEN it.[GNRC_LKUP_CATEGORY] is null THEN 0 
                    else 1      
                    END,it.[GNRC_LKUP_CATEGORY]"
                    EnableFlattening="False" EnableInsert="True" EntitySetName="GEN_LOOKUP">

    But this woking on initial page load, when i perform sort by clicking the grid header it is not performing the sort correctly.

    Thanks,

    Praveen.

    Wednesday, February 5, 2014 5:10 AM

Answers

  • User-330204900 posted

    also try adapting the SortExpression of the EntityDataSourceSelectingEventArgs.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, February 6, 2014 9:48 AM
  • User-933407369 posted

    I'm sorry that i don't reproduce your issue  without installing Oracle database Client.

    i suggest you that you try to write own codes with parameters  to achieve your target in codebehind or change your sqlstatement with orderby.

    Please refer to the links for details:

     OrderBy Parameter type in EntityDataSource

    http://forums.asp.net/t/1530224.aspx

    Hope it helps.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, February 6, 2014 9:10 PM

All replies

  • User-933407369 posted

    hi Praveen,

    i try the demo with GridView and EntityDataSource , it is working for me.

       <asp:GridView ID="GridView1" DataSourceID="EntityDataSource1" runat="server" AllowSorting="True" EnableSortingAndPagingCallbacks="True"></asp:GridView>
               <asp:EntityDataSource ID="EntityDataSource1" runat="server" ConnectionString="name=MyNorthwindEntities" 
                OrderBy="CASE 
                    WHEN it.[FirstName] is null THEN 0 
                    else 1      
                    END,it.[EmployeeID]"
     DefaultContainerName="MyNorthwindEntities" EnableFlattening="False" EntitySetName="Employees" Select="it.[FirstName], it.[LastName], it.[Title], it.[DataConsegna], it.[Designation], it.[Gender], it.[EmployeeID]" AutoGenerateOrderByClause="false"></asp:EntityDataSource>

    you need to set the property 'AllowSorting='true'' for your datacontrol. if you only want to set the column which you want , you can try to remove the 'OrderBy' instead set AutoGenerateOrderByClause, please refer to the link for details:

    http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.entitydatasource.autogenerateorderbyclause(v=vs.110).aspx

    Hope it helps.

     

     

    Wednesday, February 5, 2014 10:38 PM
  • User2057255475 posted

    Hi Happy,

    I have set the "AutoGenerateOrderByClause" to false. But on page load it was working fine, when i click on gridview header to perform the sort it is not sorting correctly the NULL values are coming at last for asceding sort.

    By the way i am using Oracle database not sql. I think it wont occur with SQL database. Do you have any idea of how to achive this with Oracle database?

    Thanks,

    Praveen.

    Thursday, February 6, 2014 12:17 AM
  • User-330204900 posted

    Try adding this to the on selecting event as the "OrderBy2 clause will only be executed on the initial load of the page but not when you click the column headers.

    Thursday, February 6, 2014 6:47 AM
  • User2057255475 posted

    I tried by setting like below.

     Private Sub GridDataSource_Selecting(sender As Object, e As System.Web.UI.WebControls.EntityDataSourceSelectingEventArgs) Handles GridDataSource.Selecting
            Dim et = e.DataSource
            et.OrderBy = "CASE WHEN it.[GNRC_LKUP_CATEGORY] IS NULL THEN 0 ELSE 1 END"
        End Sub

    But still it is not sorting the data correctly. Are there any alternatives?

    Thanks,

    praveen.

    Thursday, February 6, 2014 7:43 AM
  • User-330204900 posted

    not sure I've never had to do that :( you may get better milage using QueryCreated or even use the query extender. Once we can use EF6 there is the possibility of doing this by adapting the queries as they are run.

    Thursday, February 6, 2014 9:46 AM
  • User-330204900 posted

    also try adapting the SortExpression of the EntityDataSourceSelectingEventArgs.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, February 6, 2014 9:48 AM
  • User-933407369 posted

    I'm sorry that i don't reproduce your issue  without installing Oracle database Client.

    i suggest you that you try to write own codes with parameters  to achieve your target in codebehind or change your sqlstatement with orderby.

    Please refer to the links for details:

     OrderBy Parameter type in EntityDataSource

    http://forums.asp.net/t/1530224.aspx

    Hope it helps.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, February 6, 2014 9:10 PM