How to count Id

Question

I have a table where i want number of ID with entity framework core. I 'm using CountAsync but it it returning number 26.

But there ID 27. Picture result from querry in Azure Studio. Am I'm doing something wrong?

Example of method:

public async Task<int> GetNumberOfId()
{
    int number = await DB.mytable.CountAsync(); ; 
    return number;
}

Naimish Makwana · Answer

Hello Martin CountAsync method will only count the number of rows in the table and in your ID Column 16 ID is missing. So as per Count method it will return only 26 records. If you want the 27 ID as result then you have to use Max Method to find Maximum ID from table. public async Task GetMaxIdAsync() { return await DB.MyTable.MaxAsync(x => x.id); } Thanks Naimish Makwana

Tuhin Paul · Answer

It is possible that the ID column in your database table has gaps meaning that not all numbers from 1 to the maximum ID number are used. In this case using the CountAsync method on the table will only count the number of rows in the table and not the maximum ID value. To get the maximum ID value in the table you can use the MaxAsync method instead. Here is an example of how you can modify your method to return the maximum ID value: public async Task GetNumberOfId() { int maxId = await DB.mytable.MaxAsync(x => x.ID); return maxId; } This will return the highest ID value in the mytable table. If you want to count the total number of unique ID values in the table (including any gaps) you can use the Distinct() method before calling the CountAsync method like this: public async Task GetNumberOfId() { int count = await DB.mytable.Select(x => x.ID).Distinct().CountAsync(); return count; } This will return the total number of unique ID values in the mytable table.

Vishal Joshi · Answer

Hello Issue with you code is CountAsync it will return total number of rows availalbe in table. in case if we delete any row it won't return excet id inserted. There are several ways to get id. you can use OrderByDescending or MaxAsync. Try below code to get id 27 as per your expectation public async Task GetNumberOfId() { int number = await DB.mytable.OrderBy(x=>x.id).LastOrDefault(); return number; } Thanks Vishal