Skip to Main Content

ODP.NET

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

ORDER BY ASC and DESC nulls order using EF Core

4263965Jun 17 2020 — edited Jun 22 2020

I'm comparing OrderBy results using Oracle.EFCore 3.19.0-beta1 and Sqlite 3.1.4 and found one difference.

With Oracle the OrderByDescending creates a query

SELECT "m"."ID", "m"."CATEGORY", "m"."CODE" FROM "MY_DB"."MATERIAL" "m" ORDER BY "m"."CATEGORY" 

this means that the first rows will have NULL on category column, if any.

Using Sqlite the query is almost identical but the NULL will be in the last rows.

This behavior is intentional?

The Oracle OrderBy gerenates this query:

SELECT "m"."ID", "m"."CATEGORY", "m"."CODE" FROM "MY_DB"."MATERIAL" "m" ORDER BY "m"."SOS" NULLS FIRST

OrderByDescending shouldn't have a NULLS LAST?

This post has been answered by Alex Keh-Oracle on Jun 22 2020
Jump to Answer

Comments

Alex Keh-Oracle

SQLite considers NULLs to be smaller than any other value, whereas Oracle DB considers NULLS to be larger than any other value.

4263965

Hi Alex, thanks for the reply.

I understood the difference. But when using .OrderBy or .OrderByDescending in Oracle EF Core I always get the NULLs at the first rows. When using .OrderBy the "NULLS FIRST" is added the query to change the default Oracle behavior and getting the same results for Sqlite (and SqlServer I imagine). It seems not consistent to me not doing the same for the OrderByDescending.

Alex Keh-Oracle

Can you share the LINQ you are using in both cases? The model would also help so that we can reproduce what you are seeing.

4263965

The complete tests. I'wont share the ModelContext because is generated by the Scaffold Tool.

using Microsoft.Data.Sqlite;

using Microsoft.EntityFrameworkCore;

using Microsoft.Extensions.Logging;

using NUnit.Framework;

using OracleRepository.Models;

using System.Linq;

namespace OracleRepositoryTests

{

    public class OrderByTests

    {

        public static readonly ILoggerFactory DebugLoggerFactory

            = LoggerFactory.Create(builder => { builder.AddDebug(); });

        [Test]

        public void Material_OrderBy()

        {

            using var oracleContext = GetOracleContext();

            var oracleResult = oracleContext.Material.OrderBy(x => x.Sos).FirstOrDefault();

            Assert.IsNull(oracleResult.Sos);

            //SELECT "m"."ID", "m"."CATEGORY", "m"."CODE", "m"."DESCRIPTION", "m"."PART_NUMBER", "m"."SOS"

            //FROM "SINOPS_HML"."MATERIAL" "m"

            //ORDER BY "m"."SOS" NULLS FIRST

            //FETCH FIRST 1 ROWS ONLY

            using var sqlite = CreateSqliteContext();

            var sqliteResult = sqlite.Material.OrderBy(x => x.Sos).FirstOrDefault();

            Assert.IsNull(sqliteResult.Sos);

            //SELECT "m"."ID", "m"."CATEGORY", "m"."CODE", "m"."DESCRIPTION", "m"."PART_NUMBER", "m"."SOS"

            //FROM "MATERIAL" AS "m"

            //ORDER BY "m"."SOS"

            //LIMIT 1

        }

        [Test]

        public void Material_OrderByDescending()

        {

            using var oracleContext = GetOracleContext();

            var oracleResult = oracleContext.Material.OrderByDescending(x => x.Sos).FirstOrDefault();

            Assert.IsNull(oracleResult.Sos);

            //SELECT "m"."ID", "m"."CATEGORY", "m"."CODE", "m"."DESCRIPTION", "m"."PART_NUMBER", "m"."SOS"

            //FROM "SINOPS_HML"."MATERIAL" "m"

            //ORDER BY "m"."SOS" DESC

            //FETCH FIRST 1 ROWS ONLY

            using var sqlite = CreateSqliteContext();

            var sqliteResult = sqlite.Material.OrderByDescending(x => x.Sos).FirstOrDefault();

            Assert.IsNotNull(sqliteResult.Sos);

            //SELECT "m"."ID", "m"."CATEGORY", "m"."CODE", "m"."DESCRIPTION", "m"."PART_NUMBER", "m"."SOS"

            //FROM "MATERIAL" AS "m"

            //ORDER BY "m"."SOS" DESC

            //LIMIT 1

        }

        private static ModelContext GetOracleContext()

        {

            var context = new ModelContext(new DbContextOptionsBuilder<ModelContext>()

                .UseOracle("myconnectionstring")

                .UseLoggerFactory(DebugLoggerFactory)

                .EnableSensitiveDataLogging()

                .Options);

            return context;

        }

        private static ModelContext CreateSqliteContext()

        {

            var connection = new SqliteConnection($"DataSource=TestOrderBy;mode=memory;");

            connection.Open();

            var context = new ModelContext(new DbContextOptionsBuilder<ModelContext>()

                .UseSqlite(connection)

                .UseLoggerFactory(DebugLoggerFactory)

                .EnableSensitiveDataLogging()

                .Options);

            context.Database.EnsureCreated();

            context.AddRange(

                new Material { Code = "ABC", Sos = "Sos" },

                new Material { Code = "DEF", Sos = null }

            );

            context.SaveChanges();

            return context;

        }

    }

}

Alex Keh-Oracle
Answer

Thanks for the test case! Yes, this looks like a bug. I've created Bug 31524350 to track this issue and have it resolved.

Marked as Answer by 4263965 · Sep 27 2020
1 - 5

Post Details

Added on Jun 17 2020
5 comments
1,923 views