Logical NOT before contains does not include NULL check

[S1r-Lanzelot]

I have a piece of code that looks like the following:

public List<string> ResolveDomicileIdsByForeignGeoId(IEnumerable<int?> geoIds)
        {
            var domIds = myContext.DomicileMappings
                .Where(x => !geoIds.Contains(x.GeoId))
                .Select(x => x.DomicileId).Distinct().ToList();
            return domIds;
        }

The GeoId column in the DomicileMappings table is nullable.

The generated sql for this query is as follows:

SELECT DISTINCT [x].[DomicileId]
FROM [DomicileMappings] AS [x]
WHERE [x].[GeoId] NOT IN (77)

However I would expect it to check for nulls as well, as follows:

SELECT DISTINCT [x].[DomicileId]
FROM [DomicileMappings] AS [x]
WHERE [x].[GeoId] NOT IN (77) OR [x].[GeoId] IS NULL

since the column is nullable. Am I wrong to expect this?

Further technical details

EF Core version: v2.2.0
Database Provider: Sql Server (Azure)

[S1r-Lanzelot]

Furthermore, I would expect this functionality in Linq To Sql as it exists with in memory objects:

List<int?> DomicileMappings = new List<int?>();
List<int?> geoIds = new List<int?>();

geoIds.Add(1);
DomicileMappings.Add(1);
DomicileMappings.Add(2);
DomicileMappings.Add(null);

var domIds = DomicileMappings.Where(x => !geoIds.Contains(x)).ToList();
//domIds  = 2, null

[smitpatel]

This issue is lacking enough information for us to effectively reproduce. Please post a runnable project/solution or complete code listing that demonstrates the behavior you are seeing.

[smitpatel]

Following code

using System;
using System.Collections.Generic;
using System.Linq;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;

namespace EFSampleApp
{
    public class Program
    {
        public static void Main(string[] args)
        {
            using (var db = new MyContext())
            {
                // Recreate database
                db.Database.EnsureDeleted();
                db.Database.EnsureCreated();

                // Seed database

                db.AddRange(
                    new Blog { Value = null },
                    new Blog { Value = 2 }
                    );

                db.SaveChanges();
            }

            using (var db = new MyContext())
            {
                // Run queries
                var ids = new List<int?>
                {
                    1,
                    2,
                    null
                };
                var query = db.Blogs
                    .Where(b => !ids.Contains(b.Value))
                    .ToList();
                Console.WriteLine(query.Count);
            }

            Console.WriteLine("Program finished.");
        }
    }


    public class MyContext : DbContext
    {
        private static ILoggerFactory LoggerFactory => new LoggerFactory().AddConsole(LogLevel.Trace);

        // Declare DBSets
        public DbSet<Blog> Blogs { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            // Select 1 provider
            optionsBuilder
                .UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=_ModelApp;Trusted_Connection=True;Connect Timeout=5;ConnectRetryCount=0")
                //.UseSqlite("filename=_modelApp.db")
                //.UseInMemoryDatabase(databaseName: "_modelApp")
                .EnableSensitiveDataLogging()
                .UseLoggerFactory(LoggerFactory);
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            // Configure model
        }
    }

    public class Blog
    {
        public int Id { get; set; }
        public int? Value { get; set; }
    }
}

Generated SQL

      SELECT [b].[Id], [b].[Value]
      FROM [Blogs] AS [b]
      WHERE [b].[Value] NOT IN (1, 2) AND [b].[Value] IS NOT NULL

EF Core version 2.2.0-rtm-35687

[S1r-Lanzelot]

@smitpatel thanks for looking at this. I will work on submitting a project to reproduce the issue tonight. Looking at the example code briefly, I wonder if you instantiate the list with just:
var ids = new List<int?> { 1 };

it will replicate the issue... The generated query you posted is different from what I am after.

Regardless, Ill get a reproducable project up soon. Thanks.

[smitpatel]

@S1r-Lanzelot - That hint was useful. I got repro.
Null semantics are not being applied to InExpression. Assigning @maumar

[maumar]

dupe of #11464