FromSqlRaw returning null entity if all column values are null

[joshvote]

When using FromSqlRaw on a keyless entity and the underlying SQL result returns columns where EVERY column value is NULL - FromSqlRaw returns a null entity instead of an entity with all of its properties set to null.

Steps to reproduce

Say I have the following Models

public class Car
{
  public int CarID { get; set; }
  public string Name { get; set; }
}
public class CarView
{
  public string Name { get; set; }
  public int? Count { get; set; }
  public int? Price { get; set; }
}

Configured using

modelBuilder.Entity<Car>(entity =>
{
  entity.ToTable("Cars", "test");
  entity.HasKey(e => e.CarID);
  entity.Property(e => e.CarID).ValueGeneratedNever();
});

modelBuilder.Entity<CarView>(entity =>
{
  entity.HasNoKey();
});

If we simulate an SQL query returning NULL for all of the CarView properties

dc.Cars.Add(new Car { CarID = 1, Name = null });
await dc.SaveChangesAsync();

// Fake query simulating a response where ALL of the columns were NULL
List<CarView> allNullFields = await dc.CarViews.FromSqlRaw("SELECT NULL as [Name], NULL AS [Count], NULL AS [Price] FROM [test].[Cars]").ToListAsync();

// Fake query simulating a response where SOME but not all of the columns were NULL
List<CarView> someNullFields = await dc.CarViews.FromSqlRaw("SELECT NULL as [Name], 123 AS [Count], NULL AS [Price] FROM [test].[Cars]").ToListAsync();

Assert.Single(someNullFields); // success
Assert.NotNull(someNullFields.First()); // success (also all properties are mapped correctly)

Assert.Single(allNullFields); // success
Assert.NotNull(allNullFields.First()); // fails - the parsed list is a single element which is null

Full project is on a custom branch at https://github.com/joshvote/efcorerawsqlbug/tree/allnullresponse

Further technical details

EF Core version: 3.1
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET Core 3.1
Operating system: Windows 10
IDE: (e.g. Visual Studio 2019 16.4.2)

[ajcvickers]

Note for team: I was able to reproduce this. This isn't a behavior it would be possible to have when the entity type has a key, since it must be non-null. This may be why we are only seeing this now with keyless entities.

[ajcvickers]

Note for team: let's re-discuss. Consider the example below:

public static class ThreeOne
{
    public static void Main()
    {
        // using (var context = new SomeDbContext())
        // {
        //     context.Database.EnsureDeleted();
        //     context.Database.EnsureCreated();
        //
        //     context.Add(new Product { Name = null, Description = null });
        //     
        //     context.SaveChanges();
        // }

        using (var context = new SomeDbContext())
        {
            var products = context.Set<Product>().ToList();
            Console.WriteLine(products.Count);
            Console.WriteLine(products[0] == null);
        }
    }
}

public class SomeDbContext : DbContext
{
    private static readonly ILoggerFactory
        Logger = LoggerFactory.Create(x => x.AddConsole()); //.SetMinimumLevel(LogLevel.Debug));

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Product>().HasNoKey();
    }

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

public class Product
{
    //public int Id { get; set; }
    public string Name { get; set; }
    public string Description { get; set; }
}

This returns a list with a null instance, rather than an instance with two null values.

[smitpatel]

Poaching