The problem is when I am loading all the records at a same time it is taking a lot of time (more than 20 seconds) which is bad user experience. So, I am planning to fetch the 50 records but display the message "50 of 1000 items loaded" for the first time.
I have a button "load more" which when clicked will fetch another 50 records and goes on, on the each click till all the records is fetched.
I am able to achieve the goal of fetching the record but not able to get the total records available in the table when the page is loaded for the first time.
As multiple comments (also on deleted answers) pointed out that you need to run an explicit
COUNT(*) query for this. This is simple enough and can be done like this:
var itemCount = dbContext.Foo.Count();
This will return an integer of the amount of items stored in the table.
dbContext.Foo is an assumption (and I know what assumptions make of you and me) but OP hasn't/ wont post any code. I will try and watch this question and update this answer once OP posted some code