ODP.NET

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

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.

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.

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;

        }

    }

}