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.
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; }