3 Replies Latest reply on Oct 15, 2019 4:26 PM by Mark Williams

    EF Core 2.2 + Oracle.ManagedDataAccess.Core 2.19.31 - Descending sort order, NULLS LAST?

    3663071

      This one has been stumping me for a while, not sure there is a fix after the research I've already done.

       

      Problem:
      I have an Oracle 12C DB table with the following DDL:

       

      CREATE TABLE TEST_TABLE

      (

        ID NUMBER(10, 0) NOT NULL

      , STRINGCOL VARCHAR2(50)

      , CONSTRAINT TEST_TABLE_PK PRIMARY KEY

        (

          ID

        )

        ENABLE

      );

      INSERT INTO TEST_TABLE (ID, STRINGCOL) VALUES (1, null);

      INSERT INTO TEST_TABLE (ID, STRINGCOL) VALUES (2, 'ZZZZ');

      INSERT INTO TEST_TABLE (ID, STRINGCOL) VALUES (3, '1111');

      INSERT INTO TEST_TABLE (ID, STRINGCOL) VALUES (4, 'zzzz');

      INSERT INTO TEST_TABLE (ID, STRINGCOL) VALUES (5, '9999');

      INSERT INTO TEST_TABLE (ID, STRINGCOL) VALUES (6, 'aaaa');

      INSERT INTO TEST_TABLE (ID, STRINGCOL) VALUES (7, 'AAAA');

       

      I want to sort this column in either ASC or DESC and have values in this order:

       

      Expected Outcome:

      ASCDESC
      (null)zzzz
      1111aaaa
      9999ZZZZ
      aaaaAAAA
      zzzz9999
      AAAA1111
      ZZZZ(null)

       

      EF Core 2.2 LINQ expression (Descending sort):

      var results = dbContext.TestTables.OrderByDescending(x => x.StringCol).ToList();

       

      SQL Statement constructed:

      SELECT "x"."ID", "x"."STRINGCOL"
      FROM "TEST_TABLE" "x"
      ORDER BY "x"."STRINGCOL" DESC;

       

      Query Results:

      STRINGCOL
      (null)
      zzzz
      aaaa
      ZZZZ
      AAAA
      9999
      1111

       

       

      The SQL query I want to achieve is the following:

      SELECT "x"."ID", "x"."STRINGCOL"

      FROM "TEST_TABLE" "x"

      ORDER BY "x"."STRINGCOL" DESC NULLS LAST

      FETCH NEXT 20 ROWS ONLY;

       

      Query Results:

      STRINGCOL
      zzzz
      aaaa
      ZZZZ
      AAAA
      9999
      1111
      (null)

       

       

      I've tried a number of LINQ expressions, and have come up empty on getting the generated SQL query to properly allow this type of sorting.  Seems like many others would want this same thing, am I missing something here?

       

      EF Core 2.2 LINQ expression (Descending sort):

      var results = dbContext.TestTables.OrderByDescending(x => !string.IsNullOrEmpty(x.StringCol)).ThenByDescending(x => x.StringCol).ToList();

       

      Generated Query:

      SELECT "x"."ID", "x"."STRINGCOL"

      FROM "TEST_TABLE" "x"

      ORDER BY CASE

          WHEN "x"."STRINGCOL" IS NOT NULL AND ("x"."STRINGCOL" <> N'')

          THEN 1 ELSE 0

      END DESC, "x"."STRINGCOL" DESC;

       

      Query Results:

      STRINGCOL
      (null)
      zzzz
      aaaa
      ZZZZ
      AAAA
      9999
      1111
        • 1. Re: EF Core 2.2 + Oracle.ManagedDataAccess.Core 2.19.31 - Descending sort order, NULLS LAST?
          Mark Williams

          I'm not sure if this will be helpful at all but perhaps...

           

          Thank you for providing the steps to attempt to replicate the issue you are seeing. I was able to use the information you provided to create a test case. However, in my environment I don't see the undesirable sorting behavior. That is, the NULLs are sorted last as you desire.

           

          I have noticed that the SQL generated in my test is slightly different from the SQL generated in your test. This makes me wonder if a different assembly version is causing the different SQL.

           

          I'm using the following versions which might be the same as you, but not sure:

           

          Oracle Database - 12.2.0.1

          Oracle.EntityFrameworkCore - 2.19.30

          Oracle.ManagedDataAccess.Core - 2.19.31

          System.Linq - 4.1.0

          Microsoft.NETCore.App - 2.1

           

          I'm not a frequent user of EF so I'm not sure if the Microsoft.NETCore.App version constitutes EF Core 2.1 rather than EF Core 2.2 as you are using? If so, I'll try to see about updating that to 2.2 version.

           

          From your post the following LINQ expression and SQL of interest are (bold font should be the differences):

           

          EF Core 2.2 LINQ expression (Descending sort):

          var results = dbContext.TestTables.OrderByDescending(x => !string.IsNullOrEmpty(x.StringCol)).ThenByDescending(x => x.StringCol).ToList();

           

          SELECT "x"."ID", "x"."STRINGCOL"

          FROM "TEST_TABLE" "x"

          ORDER BY CASE

              WHEN "x"."STRINGCOL" IS NOT NULL AND ("x"."STRINGCOL" <> N'')

              THEN 1 ELSE 0

          END DESC, "x"."STRINGCOL" DESC

           

          In my environment that LINQ expression results in the following SQL:

           

          SELECT "x"."ID", "x"."STRINGCOL"

          FROM "C##MARKWILL"."TEST_TABLE" "x"

          ORDER BY CASE

              WHEN "x"."STRINGCOL" IS NOT NULL AND ("x"."STRINGCOL" IS NOT NULL )

              THEN 1 ELSE 0

          END DESC, "x"."STRINGCOL" DESC

           

          This produces the desired sorting results.

           

          In my environment the "ThenByDescending" is not necessary. Removing it from the LINQ expression results in the follow SQL:

           

          SELECT "x"."ID", "x"."STRINGCOL"

          FROM "C##MARKWILL"."TEST_TABLE" "x"

          ORDER BY CASE

              WHEN "x"."STRINGCOL" IS NOT NULL AND ("x"."STRINGCOL" IS NOT NULL )

              THEN 1 ELSE 0

          END DESC

           

          This also produces the correctly sorted output.

           

          I know that a "works for me" type of answer isn't necessarily helpful, but since it does work for me I suspect there is something different in my environment - possibly a different assembly version is leading to different SQL.

           

          Regards,

          Mark

          1 person found this helpful
          • 2. Re: EF Core 2.2 + Oracle.ManagedDataAccess.Core 2.19.31 - Descending sort order, NULLS LAST?
            3663071

            Mark - Thanks for your answer, glad you didn't just give me a surfacey response.  Part of your response made me consider what I was doing differently.

             

            Since Oracle treats empty string equal to NULL, I didn't need to be using the string.IsNullOrEmpty() method, I should simply do this:

             

            var results = dbContext.TestTables

                 .OrderByDescending(x => x.StringCol != null)

                 .ThenByDescending(x => x.StringCol)

                 .ToList();


            This produces this SQL:

             

            SELECT "x"."ID", "x"."STRINGCOL"

            FROM "TEST_TABLE" "x"

            ORDER BY CASE

                WHEN "x"."STRINGCOL" IS NOT NULL

                THEN 1 ELSE 0

            END DESC, "x"."STRINGCOL" DESC

             

            Which appears to sort exactly as I expect.  Even though I can't make EF do NULLS LAST on Descending sorts, I can do this hack and it seems to work.

            • 3. Re: EF Core 2.2 + Oracle.ManagedDataAccess.Core 2.19.31 - Descending sort order, NULLS LAST?
              Mark Williams

              Glad you've found a workable solution though perhaps not ideal.

               

              I admit I had a bit of a chuckle when I saw the generated SQL for the IsNullOrEmpty in my environment:

               

              WHEN "x"."STRINGCOL" IS NOT NULL AND ("x"."STRINGCOL" IS NOT NULL )

               

              Yes, we're really making sure that column is NOT NULL. But, as you rightly say, empty string and NULL are treated the same in Oracle.

               

              I'm still curious about the "OrEmpty" part as it has expanded to different SQL in your environment and my environment:

               

              "x"."STRINGCOL" <> N'' for you vs. "x"."STRINGCOL" IS NOT NULL for me.

               

              I'm not sure what drove the NCHAR literal comparison in your environment whereas it did not in mine.

               

              Anyway, hope things keep working.

               

              Regards,

              Mark