Is there a way to get the row count of a complex Linq query and millions of records without hitting the db twice or writing 2 separate queries??
I might have my own suggestion. Write a stored procedure, but I'm good with MySQL not MSSQL.
Any better suggestions would be great. Also, if anyone knows if Microsoft is working on adding this feature to the entity framework.
I'd suggest using the Take() function. This can be used to specify the number of records to take from a linq query or List. For example
List<customers> _customers = (from a in db.customers select a).ToList(); var _dataToWebPage = _customers.Take(50);
I use a similar technique in an MVC app where I write the _customers list to the session and then use this list for further pagination queries when the user clicks on page 2, 3 etc. This saves multiple database hits. However if your list is very large then writing it too the session is probably not a good idea.
For pagination you can use the Skip() and Take() function together. For example to get page 2 of the data :
var _dataToWebPage = _customers.Skip(50).Take(50);