LINQ to SQL: If database return no values Count() throws an exception

Question

I have a linq to sql statement which returns a set of Customer details to a customer object

var customers = from .....

I then use

if(customers.Count() > 0)
{
    return customers.First();
}
else
{
    return null;
}

but customers.Count() throws a

'customers.Count()' threw an exception of type 'System.NotSupportedException'   int {System.NotSupportedException}

how else am I to check if nothing was returned???

=============

This is what the problem was. It was actually a problem in my LINQ statement I was doing

I have a function

bool TrimAndCompare(string s1, string s2)
{
   return customer.CustomerID.Trim() == customerID.Trim()
}

var customers = from customer in _context.Customers
                          where
                                TrimAndCompare(customer.CustomerID, customerID)
                          select customer;

When I did this everything is good

var customers = from customer in _context.Customers
                          where
                                customer.CustomerID.Trim() == customerID.Trim()
                          select customer;

Guess you can't reuse functions in a LINQ statement then

Answer 1

You can't use methods that have no equivalent in SQL in a Linq-To-SQL expression.

---

In order to test for results, don't use Count and First as this would execute the SQL query twice.
Instead, use FirstOrDefault:

return customers.FirstOrDefault();

FirstOrDefault returns the first element, or null if no elements were found: