Need to get total counts of a record from table in database using ASP.NET Core Web API-CodePudding

I need to count the total rows of a table when I pass a date as a parameter.

IService:

Task<int?> ScoresUpdated(DateTime date);

Service implementation:

public async Task<int?> ScoresUpdated(DateTime date)
{
    var result = await _eventScheduleContext
                           .ViewPersonEventScore
                           .Where(x => x.DateTimeUtc.Date == date
                                       && x.ExamModeId == 1
                                       && x.EventStatusId == 8).ToListAsync();
                           
    return result.Count();
}

Controller:

public async Task<IActionResult> ScoresUpdated(DateTime date)
{
     var result = await _examDetailService.ScoresUpdated(date);
     return Ok(result);
}

But the output I'm getting is 0. In the database, I can see 80 rows.

Is there anything wrong with my code? Can you give me some suggestions how to fix it?

CodePudding user response：

Check the date you are passing as a parameter may not be match with the DB value.

Debug the date and compare it with the Database value.

CodePudding user response：

Suspect that it is due to this line

x.DateTimeUtc.Date == date

In SQL expression, I believe it translated to

CONVERT(date, DateTimeUtc) = @date

which @date is DateTime type. So this results that you are unable to query any records.

Use date.Date so that the translated SQL expression will be:

CONVERT(date, DateTimeUtc) = CONVERT(date, @date)

public async Task<int?> ScoresUpdated(DateTime date)
{
    var result = await _eventScheduleContext
        .ViewPersonEventScore
        .Where(x => x.DateTimeUtc.Date == date.Date
            && x.ExamModeId == 1
            && x.EventStatusId == 8)
    .ToListAsync();
                           
    return result.Count();
}

While seems you are only returning the count of the query result, you may consider applying CountAsync<TSource>(IQueryable<TSource>, CancellationToken).

This would improve the query performance as it will return the count, but not return all columns to the result.

public async Task<int?> ScoresUpdated(DateTime date)
{
    return await _eventScheduleContext
        .ViewPersonEventScore
        .CountAsync(x => x.DateTimeUtc.Date == date.Date
            && x.ExamModeId == 1
            && x.EventStatusId == 8);
}