5 Replies Latest reply on Jun 22, 2020 2:59 PM by Alex Keh - Product Manager-Oracle

    ORDER BY ASC and DESC nulls order using EF Core

    4263965

      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?

        • 1. Re: ORDER BY ASC and DESC nulls order using EF Core
          Alex Keh - Product Manager-Oracle

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

          • 2. Re: ORDER BY ASC and DESC nulls order using EF Core
            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.

            • 3. Re: ORDER BY ASC and DESC nulls order using EF Core
              Alex Keh - Product Manager-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.

              • 4. Re: ORDER BY ASC and DESC nulls order using EF Core
                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;

                        }

                    }

                }

                1 person found this helpful
                • 5. Re: ORDER BY ASC and DESC nulls order using EF Core
                  Alex Keh - Product Manager-Oracle

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