Linq Select Statement slows down when receiving COUNT

Question

I am trying to get the total number of records from the method below using EntityFramework and Linq. He slowly returns the bill.

public static int totalTracking(int id) { using (var ctx = new GPEntities()) { var tr = ctx.Tracking .Where(c => c.clientID == Config.ClientID) .Where(c => c.custID == id) .Where(c => c.oOrderNum.HasValue) .ToList(); return tr.Count(); } } 

Answer 1

You can greatly simplify the request:

 using (var ctx = new GPEntities()) { return ctx.Tracking .Where(c => c.clientID == Config.ClientID) .Where(c => c.custID == id) .Where(c => c.oOrderNum.HasValue) .Count(); } 

When you call ToList , it will cause materialization, so the query will be issued to the database and all columns will be extracted. The actual bill will occur on the client.

If you are just Count , without a ToList , it issues a query when you call Count , and the server will return only one number, not a table.

It's not that important for performance, but I think the code would have looked a bit nice without this Where :

 using (var ctx = new GPEntities()) { return ctx.Tracking .Where(c => c.clientID == Config.ClientID && c.custID == id && c.oOrderNum.HasValue) .Count(); } 

or even

 using (var ctx = new GPEntities()) { return ctx.Tracking .Count(c => c.clientID == Config.ClientID && c.custID == id && c.oOrderNum.HasValue); } 

Answer 2

You can greatly simplify things and just use the Counting Method

You tried:

 public static int totalTracking(int id) { using (var ctx = new GPEntities()) { return ctx.Tracking.Count( c => c.clientID == Config.ClientID && c.custID == id && c.oOrderNum.HasValue); } } 

Answer 3

Remove the .ToList() call. It forces the request to print all the results to the client.

By removing this and simply calling .Count () directly from the results of tr (without ToList() ), Count() becomes part of the request itself and is executed remotely, which will greatly simplify this:

 public static int totalTracking(int id) { using (var ctx = new GPEntities()) { var tr = ctx.Tracking .Where(c => c.clientID == Config.ClientID) .Where(c => c.custID == id) .Where(c => c.oOrderNum.HasValue); // This can be added above, or left here - the end result is the same return tr.Count(); } }