How do I handle the SqlException "No Records Found" when using LINQ to SQL?

Question

I'm using LINQ to SQL to call sprocs at my company. Normally it works great but on some queries, if nothing is found it will throw a SqlException "No Records Found".

How should I handle this case?

Here is an example call I would make:

        /// <summary>
        /// Gets the pending messages.
        /// </summary>
        /// <param name="historySearchCriteria">The history search criteria.</param>
        /// <returns><c>List</c> of pending messages.</returns>
        public List<PendingMessage> GetPendingMessages(HistorySearchCriteria historySearchCriteria)
        {
            using (MessageDataContext db = new MessageDataContext(DatabaseProperties.MessageConnectionString))
            {
                List<PendingMessage> pendingMessages = new List<PendingMessage>();

                pendingMessages.AddRange(db.usp_search_message_pending(historySearchCriteria.AccountId,
                    historySearchCriteria.TrackingNumber,
                    historySearchCriteria.StartDateTime,
                    historySearchCriteria.EndDateTime)
                    .Select(p => new PendingMessage()
                    {
                        Account = p.account,
                        ActionType = (OrderActionType) Enum.Parse(typeof(OrderActionType), p.action_type.ToString()),
                        AttemptsRemaining = p.attempts_remaining,
                        Message = p.message
                    }));

                return pendingMessages;
            }           
        }

What is the best way to handle the fact that I simply want to return an empty list if no records are found.

Answer 1

You could simply catch that Exception, and return new List<PendingMessage>; within the handler.

Answer 2

You could use DefaultIfEmpty.

Somthing like:

pendingMessages.AddRange(
                db.usp_search_message_pending
                   (
                       historySearchCriteria.AccountId,
                       historySearchCriteria.TrackingNumber,
                       historySearchCriteria.StartDateTime,
                       historySearchCriteria.EndDateTime
                   )
          .DefaultIfEmpty()
          .Select( /* select clause here */)
);

Answer 3

Where does the text "No Records Found" come from?

Execute the stored procedure from Management Studio using the same parameters that result in the exception.

Does SSMS report an error?

If not, separate the C# line into 3 steps:

• Invoking the stored procedure

• Check for != null and invoke Select()

• Check for != null and call AddRange()