EF does not search on nullable field

Search for database entries with null fields

Configuration of the EF does have bearing on the implementation.

Consider case:

   public CustomerMap()
        {
            // Primary Key
            this.HasKey(t => t.CustomerId);

            // Properties
            this.Property(t => t.Name)
                .IsRequired()
                .HasMaxLength(50);

            // Table & Column Mappings
            this.ToTable("Customers");
            this.Property(t => t.CustomerId).HasColumnName("CustomerId");
            this.Property(t => t.Name).HasColumnName("Name");
            this.Property(t => t.UserId).HasColumnName("UserId").IsRequired();
        }

this will never work with the query

    context.Customers.Where(x=>x.UserId==null)

nor with

    context.Customers.Where(x=>!x.UserId.HasValue)

this is because of incorrect mapping:

    this.Property(t => t.UserId).HasColumnName("UserId").IsRequired();

Solution:


Mapping configuration has to be changed to

    this.Property(t => t.UserId).HasColumnName("UserId").IsOptional();

or remove it all together, this depends on version of EF

    this.Property(t => t.UserId).HasColumnName("UserId");