locked
LINQ to EF - Forcing SQL to do rowcount instead of getting rowcount in C# RRS feed

  • Question

  • This is a variation on this post:

    http://social.msdn.microsoft.com/Forums/en/adodotnetentityframework/thread/5ab86d63-891e-4ac8-80e6-24a51f685294

    Is there a way to force the count to happen in SQL to avoid returning millions of rows to C#, and counting them there?
    I could probably right a stored proc to do it, but it would have to use dynamic SQL, and I have a similar GetTransactionList method, and would like to keep the code as similar as possible.

            public int CountTransactionList(
                                                          DateTime? startReceivedDateTime,
                                                          DateTime? stopReceivedDateTime,
                                                          int? BMRowID,
                                                          int? status,
                                                          string transactionType,
                                                          string transactionGUID
                                                  )
            {
                var query = from c in context.VW_TransactionXMLHistory_TransactionRequests
                            // orderby c.RowID descending
                            select c;


                if (BMRowID != null)
                {
                    query = query.Where(c => c.BMRowID == BMRowID);
                }

                if (status != null)
                {
                    query = query.Where(c => c.Status == status);
                }

                if (transactionType != null)
                {
                    query = query.Where(c => c.TransactionType == transactionType);
                }

                if (startReceivedDateTime != null)
                {
                    query = query.Where(c => c.ReceivedDateTime >= startReceivedDateTime);
                }

                if (stopReceivedDateTime != null)
                {
                    query = query.Where(c => c.ReceivedDateTime <= stopReceivedDateTime);
                }

                if (transactionGUID != null)
                {
                    query = query.Where(c => c.ReceivedTransactionGUID == transactionGUID);
                }



                //var queryTake = query.Take(maxRows);

                // TODO - might be able to make this more efficient
                // by using SQL to count instead of retrieving the records, then counting

                List<VW_TransactionXMLHistory_TransactionRequests> transList =
                    query.ToList<VW_TransactionXMLHistory_TransactionRequests>();
                return transList.Count;


            }

    Thanks,
    Neal

    Wednesday, February 23, 2011 4:48 PM

Answers

  • As soon as you call ToList() here, you are manifesting the results of the query.  If you are just interested in the Count(), you don't need ToList() at all.

    Change this:

    List<VW_TransactionXMLHistory_TransactionRequests> transList =
                    query.ToList<VW_TransactionXMLHistory_TransactionRequests>();

                return transList.Count; 

     

    To this:

    return query.Count();


    Regards,

    Joe

    • Marked as answer by Neal Walters Wednesday, February 23, 2011 6:03 PM
    Wednesday, February 23, 2011 4:58 PM

All replies

  • As soon as you call ToList() here, you are manifesting the results of the query.  If you are just interested in the Count(), you don't need ToList() at all.

    Change this:

    List<VW_TransactionXMLHistory_TransactionRequests> transList =
                    query.ToList<VW_TransactionXMLHistory_TransactionRequests>();

                return transList.Count; 

     

    To this:

    return query.Count();


    Regards,

    Joe

    • Marked as answer by Neal Walters Wednesday, February 23, 2011 6:03 PM
    Wednesday, February 23, 2011 4:58 PM
  • That works perfectly and quickly! 

    Thanks.

    Neal

     

    Wednesday, February 23, 2011 6:03 PM