Efcore: 2.2: System.Data.SqlTypes.SqlNullValueException: 'Data is Null. This method or property cannot be called on Null values.'

[SecretivePerson]

This issue has already been reported a few other times in:
https://github.com/aspnet/EntityFrameworkCore/issues/13169

I'm using Entity Framework Core 2.2.1, annotations.

I have a customer object, with many nullable columns, but a specific column is throwing an error if it contains nulls.

The column is not required - either in annotations or in the fluentapi.
The column did used to have an index, which I thought might be misinterpreted and forcing no nulls, but I've removed both the code for this, and the index from the DB, but it's still happening.

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace MyApplication.Data.Models
{
    public partial class Customers
    {
        [StringLength(10)]
        public string CustomerNumber { get; set; }
        [Required]
        [StringLength(50)]
        public string CompanyType { get; set; }
        [Required]
        [StringLength(35)]
        public string Type { get; set; }
        [StringLength(15)]
        public string Salutation { get; set; }
        [StringLength(25)]
        public string ContactFirstName { get; set; }
        [StringLength(50)]
        public string ContactLastName { get; set; }
        [StringLength(50)]
        public string CompanyName { get; set; }
        [StringLength(50)]
        public string Address1 { get; set; }
        [StringLength(50)]
        public string Address2 { get; set; }
        [StringLength(50)]
        public string Address3 { get; set; }
        [StringLength(50)]
        public string Address4 { get; set; }
        [StringLength(10)]
        public string PostCode { get; set; }
        [StringLength(25)]
        public string Telephone { get; set; }
        [StringLength(255)]
        public string Email { get; set; }
    }
}

modelBuilder.Entity<Customers>(entity =>
{
    entity.HasKey(e => e.CustomerNumber)
        .HasName("PK_Customers")
        .ForSqlServerIsClustered(false);

    entity.HasIndex(e => e.Type)
        .HasName("CustomerTypes");

    //entity.HasIndex(e => e.CompanyName)
    //    .HasName("CustomersCompanyName");

    entity.Property(e => e.CustomerNumber)
        .IsUnicode(false)
        .ValueGeneratedNever();

    entity.Property(e => e.Type)
        .IsUnicode(false)
        .HasDefaultValueSql("('Standard')");

    entity.Property(e => e.AddedBy).IsUnicode(false);

    entity.Property(e => e.Address1).IsUnicode(false);

    entity.Property(e => e.Address2).IsUnicode(false);

    entity.Property(e => e.Address3).IsUnicode(false);

    entity.Property(e => e.Address4).IsUnicode(false);

    entity.Property(e => e.CompanyType)
        .IsUnicode(false)
        .HasDefaultValueSql("('Company')");

    entity.Property(e => e.CompanyName).IsUnicode(false);

    entity.Property(e => e.Email).IsUnicode(false);

    entity.Property(e => e.contactFirstName).IsUnicode(false);

    entity.Property(e => e.ContactLastName).IsUnicode(false);

    entity.Property(e => e.PostCode).IsUnicode(false);

    entity.Property(e => e.Telephone).IsUnicode(false);

    entity.Property(e => e.Salutation).IsUnicode(false);
});



md5-45ac21dd40f9167ee9c0877e4471cd89



at Microsoft.EntityFrameworkCore.Storage.TypedRelationalValueBufferFactoryFactory.ThrowReadValueException[TValue](Exception exception, Object value, IPropertyBase property)
   at lambda_method(Closure , DbDataReader )
   at Microsoft.EntityFrameworkCore.Storage.Internal.TypedRelationalValueBufferFactory.Create(DbDataReader dataReader)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.BufferlessMoveNext(DbContext _, Boolean buffer)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.MoveNext()
   at System.Linq.Enumerable.TryGetFirst[TSource](IEnumerable`1 source, Boolean& found)
   at System.Linq.Enumerable.First[TSource](IEnumerable`1 source)
   at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ResultEnumerable`1.GetEnumerator()
   at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider._TrackEntities[TOut,TIn](IEnumerable`1 results, QueryContext queryContext, IList`1 entityTrackingInfos, IList`1 entityAccessors)+MoveNext()
   at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext()
   at System.Linq.Enumerable.TryGetFirst[TSource](IEnumerable`1 source, Boolean& found)
   at System.Linq.Enumerable.First[TSource](IEnumerable`1 source)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass15_1`1.<CompileQueryCore>b__0(QueryContext qc)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression)
   at System.Linq.Queryable.First[TSource](IQueryable`1 source)
   at <censored>
   at lambda_method(Closure , Object , Object[] )
   at Microsoft.Extensions.Internal.ObjectMethodExecutor.Execute(Object target, Object[] parameters)
   at Microsoft.AspNetCore.Mvc.Internal.ActionMethodExecutor.SyncActionResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
   at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.InvokeActionMethodAsync()



md5-c533e4b2dc1d0283255a836b330902ea



at System.Data.SqlClient.SqlBuffer.get_String()
   at System.Data.SqlClient.SqlDataReader.GetString(Int32 i)
   at lambda_method(Closure , DbDataReader )

It's the CompanyName column that threw the error when it contained nulls. The address columns, for example, are all fine.

[dst3p]

I'm having this same issue. I did make changes in my database to make the columns nullable, but they are no longer marked as required is my code.

[dst3p]

Ok, I figured out my issue. I had to grab the generated query from the Output tab in Visual Studio and execute that query directly against the database to find it. It ended up being a primary key column of a completely different table/entity that was included in my result set because, and I'm speculating here, in FluentAPI I have a .HasForeignKey reference to that table. I can provide more information if needed, but I would suggest grabbing the generated query from the output and executing that by itself if you haven't already.

[ajcvickers]

@SecretivePerson I put your code into a console application and was not able to reproduce what you are seeing. Can you post a small, runnable project/solution or complete code listing, like that below, that demonstrates the behavior you are seeing.

```C#
public partial class Customers
{
[StringLength(10)] public string CustomerNumber { get; set; }
[Required] [StringLength(50)] public string CompanyType { get; set; }
[Required] [StringLength(35)] public string Type { get; set; }
[StringLength(15)] public string Salutation { get; set; }
[StringLength(25)] public string ContactFirstName { get; set; }
[StringLength(50)] public string ContactLastName { get; set; }
[StringLength(50)] public string CompanyName { get; set; }
[StringLength(50)] public string Address1 { get; set; }
[StringLength(50)] public string Address2 { get; set; }
[StringLength(50)] public string Address3 { get; set; }
[StringLength(50)] public string Address4 { get; set; }
[StringLength(10)] public string PostCode { get; set; }
[StringLength(25)] public string Telephone { get; set; }
[StringLength(255)] public string Email { get; set; }
}

public class BloggingContext : DbContext
{
private static readonly LoggerFactory Logger
= new LoggerFactory(new[] { new ConsoleLoggerProvider((_, __) => true, true) });

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder
        .UseLoggerFactory(Logger)
        .UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=Test;ConnectRetryCount=0");
}

public DbSet<Customers> Customers { get; set; }

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Customers>(entity =>
    {
        entity.HasKey(e => e.CustomerNumber)
            .HasName("PK_Customers")
            .ForSqlServerIsClustered(false);

        entity.HasIndex(e => e.Type)
            .HasName("CustomerTypes");

        //entity.HasIndex(e => e.CompanyName)
        //    .HasName("CustomersCompanyName");

        entity.Property(e => e.CustomerNumber)
            .IsUnicode(false)
            .ValueGeneratedNever();

        entity.Property(e => e.Type)
            .IsUnicode(false)
            .HasDefaultValueSql("('Standard')");

        //entity.Property(e => e.AddedBy).IsUnicode(false);

        entity.Property(e => e.Address1).IsUnicode(false);

        entity.Property(e => e.Address2).IsUnicode(false);

        entity.Property(e => e.Address3).IsUnicode(false);

        entity.Property(e => e.Address4).IsUnicode(false);

        entity.Property(e => e.CompanyType)
            .IsUnicode(false)
            .HasDefaultValueSql("('Company')");

        entity.Property(e => e.CompanyName).IsUnicode(false);

        entity.Property(e => e.Email).IsUnicode(false);

        //entity.Property(e => e.contactFirstName).IsUnicode(false);

        entity.Property(e => e.ContactLastName).IsUnicode(false);

        entity.Property(e => e.PostCode).IsUnicode(false);

        entity.Property(e => e.Telephone).IsUnicode(false);

        entity.Property(e => e.Salutation).IsUnicode(false);
    });
}

}

public class Program
{
public static void Main()
{
using (var context = new BloggingContext())
{
context.Database.EnsureDeleted();
context.Database.EnsureCreated();

        context.Add(new Customers
        {
            CustomerNumber = "123",
            CompanyType = "Cheese",
        });

        context.SaveChanges();
    }

    using (var context = new BloggingContext())
    {
        var type = "Standard";

        var CustNo = context.Customers.Where(x => x.Type == type).OrderByDescending(x => x.CustomerNumber).FirstOrDefault();
    }
}

}
```

[SecretivePerson]

@ajcvickers I have downloaded the EF source, and believe I have identified the issue.

The CompanyName field is being treated as a foreign key, even though it isn't one, and because of that, https://github.com/aspnet/EntityFrameworkCore/issues/14126 is preventing it allowing nulls.
I'm afraid I wasn't permitted to post our actual code, so gave you a trimmed-down and amended copy that unfortunately missed out what was causing the problem.

The below amended code should replicate the issue

public partial class Customers
{
    [StringLength(10)] public string CustomerNumber { get; set; }
    [Required] [StringLength(50)] public string OrganisationName { get; set; }
    [Required] [StringLength(35)] public string Type { get; set; }
    [StringLength(15)] public string Salutation { get; set; }
    [StringLength(25)] public string ContactFirstName { get; set; }
    [StringLength(50)] public string ContactLastName { get; set; }
    [StringLength(50)] public string CompanyName { get; set; }
    [StringLength(50)] public string Address1 { get; set; }
    [StringLength(50)] public string Address2 { get; set; }
    [StringLength(50)] public string Address3 { get; set; }
    [StringLength(50)] public string Address4 { get; set; }
    [StringLength(10)] public string PostCode { get; set; }
    [StringLength(25)] public string Telephone { get; set; }
    [StringLength(255)] public string Email { get; set; }
    [ForeignKey("OrganisationName")]
    [InverseProperty("Customers")]
    public virtual Organisation OrganisationNameNavigation { get; set; }
}
public partial class Organisation
{
    [StringLength(50)] public string CompanyName { get; set; }
    [InverseProperty("OrganisationNameNavigation")]
    public virtual ICollection<Customers> Customers { get; set; }
}

public class BloggingContext : DbContext
{
    private static readonly LoggerFactory Logger
        = new LoggerFactory(new[] { new ConsoleLoggerProvider((_, __) => true, true) });

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder
            .UseLoggerFactory(Logger)
            .UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=Test;ConnectRetryCount=0");
    }

    public DbSet<Customers> Customers { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Customers>(entity =>
        {
            entity.HasKey(e => e.CustomerNumber)
                .HasName("PK_Customers")
                .ForSqlServerIsClustered(false);

            entity.HasIndex(e => e.Type)
                .HasName("CustomerTypes");

            //entity.HasIndex(e => e.CompanyName)
            //    .HasName("CustomersCompanyName");

            entity.Property(e => e.CustomerNumber)
                .IsUnicode(false)
                .ValueGeneratedNever();

            entity.Property(e => e.Type)
                .IsUnicode(false)
                .HasDefaultValueSql("('Standard')");

            //entity.Property(e => e.AddedBy).IsUnicode(false);

            entity.Property(e => e.Address1).IsUnicode(false);

            entity.Property(e => e.Address2).IsUnicode(false);

            entity.Property(e => e.Address3).IsUnicode(false);

            entity.Property(e => e.Address4).IsUnicode(false);

            entity.Property(e => e.CompanyType)
                .IsUnicode(false)
                .HasDefaultValueSql("('Company')");

            entity.Property(e => e.CompanyName).IsUnicode(false);

            entity.Property(e => e.Email).IsUnicode(false);

            //entity.Property(e => e.contactFirstName).IsUnicode(false);

            entity.Property(e => e.ContactLastName).IsUnicode(false);

            entity.Property(e => e.PostCode).IsUnicode(false);

            entity.Property(e => e.Telephone).IsUnicode(false);

            entity.Property(e => e.Salutation).IsUnicode(false);
        });

        modelBuilder.Entity<Organisation>(entity =>
        {
            entity.HasKey(e => e.CompanyName)
                .HasName("PK_Organisation")
                .ForSqlServerIsClustered(false);

            entity.Property(e => e.CompanyName)
                .IsUnicode(false)
                .ValueGeneratedNever();
        });
    }
}

public class Program
{
    public static void Main()
    {
        using (var context = new BloggingContext())
        {
            context.Database.EnsureDeleted();
            context.Database.EnsureCreated();

            context.Add(new Customers
            {
                CustomerNumber = "123",
                CompanyType = "Cheese",
            });

            context.SaveChanges();
        }

        using (var context = new BloggingContext())
        {
            var type = "Standard";

            var CustNo = context.Customers.Where(x => x.Type == type).OrderByDescending(x => x.CustomerNumber).FirstOrDefault();
        }
    }
}

The foreign key is Customers.OrganisationName -> Organisation.CompanyName

In ForeignKeyPropertyDiscoveryConvention, the method IPrimaryKeyChangedConvention.Apply is trying to discover all the foreign keys referencing Organisation, but isn't using the ForeignKey attribute on the inverse navigation property, and picked up CompanyName instead (presumably because it's got the same name)

[ajcvickers]

Notes for triage: see model below and note that Customer.CompanyName has been made required when it should not be.

May be a dupe @AndriySvyryd

Model: 
  EntityType: Customers
    Properties: 
      CustomerNumber (string) Required PK AfterSave:Throw MaxLength10 Ansi 0 0 0 -1 -1
      Address1 (string) MaxLength50 Ansi 1 1 -1 -1 -1
      Address2 (string) MaxLength50 Ansi 2 2 -1 -1 -1
      Address3 (string) MaxLength50 Ansi 3 3 -1 -1 -1
      Address4 (string) MaxLength50 Ansi 4 4 -1 -1 -1
>>>      CompanyName (string) Required MaxLength50 Ansi 5 5 -1 -1 -1
      ContactFirstName (string) MaxLength25 6 6 -1 -1 -1
      ContactLastName (string) MaxLength50 Ansi 7 7 -1 -1 -1
      Email (string) MaxLength255 Ansi 8 8 -1 -1 -1
      OrganisationName (string) Required FK Index MaxLength50 9 9 1 -1 -1
      PostCode (string) MaxLength10 Ansi 10 10 -1 -1 -1
      Salutation (string) MaxLength15 Ansi 11 11 -1 -1 -1
      Telephone (string) MaxLength25 Ansi 12 12 -1 -1 -1
      Type (string) Required Index ValueGenerated.OnAdd MaxLength35 Ansi 13 13 -1 -1 0
    Navigations: 
      OrganisationNameNavigation (<OrganisationNameNavigation>k__BackingField, Organisation) ToPrincipal Organisation Inverse: Customers 0 -1 2 -1 -1
    Keys: 
      CustomerNumber PK
    Foreign keys: 
      Customers {'OrganisationName'} -> Organisation {'CompanyName'} ToDependent: Customers ToPrincipal: OrganisationNameNavigation
  EntityType: Organisation
    Properties: 
      CompanyName (string) Required PK AfterSave:Throw MaxLength50 Ansi 0 0 0 -1 -1
    Navigations: 
      Customers (<Customers>k__BackingField, ICollection<Customers>) Collection ToDependent Customers Inverse: OrganisationNameNavigation 0 -1 1 -1 -1
    Keys: 
      CompanyName PK

[AndriySvyryd]

Dupe of #13169
Should be fixed in 2.2.2

[SecretivePerson]

@AndriySvyryd @ajcvickers
I created this duplicate as the original was closed as by design without any bug identified or fix applied.
Can you confirm this bug has been fixed? I'm concerned it may have been mistakenly closed on the assumption that the duplicate was closed because it was fixed.

[AndriySvyryd]

@SecretivePerson Yes, please try using 2.2.2

[AndriySvyryd]

This is the fixed issue: https://github.com/aspnet/EntityFrameworkCore/issues/14269