How to get correct DateTime in Dapper-CodePudding

I am using Dapper to handle users in ASP.NET Core with SQL Server. Everything works fine except the DateTime.

A correct DateTime is stored in the database (04.10.2021 16:11:45), however retrieving it from the database returns a default datetime (0001-01-01T00:00:00).

This is the User model class:

public class User
{
    public DateTime CreatedAt { get; set; }
}

And I use this method in the User repository to add a User to the database:

public async Task<int> AddAsync(User entity)
{
    entity.CreatedAt = DateTime.Now;

    string sql = "insert into users (id, username, email, password, created_at) values (@Id, @Username, @Email, @Password, @CreatedAt)";

    using var connection = new SqlConnection(_configuration.GetConnectionString("Default"));
    connection.Open();
    var result = await connection.ExecuteAsync(sql, entity);

    return result;
}

This is the method that gets the User by its id:

public async Task<User> GetByIdAsync(string id)
{
    string sql = "select * from users where id = @Id";

    using var connection = new SqlConnection(_configuration.GetConnectionString("Default"));
    connection.Open();
    var result = await connection.QuerySingleOrDefaultAsync<User>(sql, new { Id = id });

    return result;
}

The User data is fetched in a controller:

[HttpGet("{id}")]
public async Task<IActionResult> GetById(string id)
{
    var data = await _unitOfWork.Users.GetByIdAsync(id);
    if (data == null)
    {
        return Ok();
    }

    return Ok(data);
}

The column type of the table is DATETIME.

CodePudding user response：

Either modify you select statement so it returns CreatedAt columns

select ... , created_at as [CreatedAt], ... from

or make sure your application calls this line at application start

Dapper.DefaultTypeMap.MatchNamesWithUnderscores = true;