CODEWITHSUNDEEP
asp.netentity-frameworkasp.net-coreentity-framework-core
Євгенія Поліщук
Євгенія Поліщук

Reputation: 11

Entity Framework Core 2.2.1 DbQuery error system.Data.SqlTypes.SqlNullValueException

Trying to work with DbQuery. I need to get non-entity type UserDetailDTO using raw sql. Added DbQuery to the context and call it from controller. But it's generate system.Data.SqlTypes.SqlNullValueException.

My context:

public class TrainingAppDbContext : DbContext
{
    public DbSet<User> Users { get; set; }
    public DbQuery<UserDetailDTO> UserDetailDTO { get; set; }

    public TrainingAppDbContext(DbContextOptions<TrainingAppDbContext> options)
    : base(options)
    {
        Database.EnsureCreated();
    }        
}

Controller:

public class AccountController : Controller
{
    private readonly TrainingAppDbContext ct;

    public AccountController(TrainingAppDbContext ct)
    {
        this.ct = ct;
    }

    public IActionResult Test()
    {
        var results = ct.UserDetailDTO.FromSql("SELECT * FROM users").ToList();
        return View();
    }
 }

When I calling my UserDetailDTO from context it's generates an error.

error picture

Upvotes: 0

Views: 846

Answers (2)

newbieCoder
newbieCoder

Reputation: 231

For those who have fields that can be nullable like DateTime, any Enums, make sure to keep it as a nullable field if your query returns null for those fields. Eg.

public DateTime? DateOfBirth { get; set; }

Upvotes: 0

Macros185
Macros185

Reputation: 231

Take a very close look at the UserDetailDTO class and any other classes that might have foreign keys to this table in your database. We recently found this error was being caused by a [Required] data annotation being added to our entities. In the example below, the [Required] attribute above FirstName is required and there should be no rows in your table where this column is null.

namespace Entities
{
    public class UserDetailDTO
    {
        public int Id { get; set; }

        [Required]
        public string FirstName { get; set; }

        public string LastName { get; set; }

        public string PhoneNumber { get; set; }

        public string EmailAddress { get; set; }
    }
}

In previous versions of EF Core, the [Required] data annotation was ignored. Version 2.2.1 started looking for these annotations in your entities and enforcing them. We had no idea this was mistakenly added to some of our entities a few months back until we updated our EF Core version and started experiencing this error. Since the annotation was incorrectly added in our case, removing the annotation solved our problem. If you find this is the cause for your issue and you actually want the column to be required then you probably have data in your table where this column is null which is what's actually causing the error. Fix that bad data, make that column not null, and the query should start working again.

Upvotes: 1

Related Questions