Why can't I reuse a function in LINQ to SQL

Question

Why does the below LINQ to SQL statment throw an exception?

I have a function

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

...some other function i call the above function in my linq statement

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

The above LINQ to SQL statment function throws an exception but the below doesn't Why??

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

I get a 'System.NotSupportedException' where I try to access customers

Answer 1

In the second snippet, the logic is being translated into a lambda expression which is then compiled into an expression tree... the two query translations are:

_context.Customers
        .Where(customer => TrimAndCompare(customer.CustomerID, customerID);

vs

_context.Customers
        .Where(customer => customer.CustomerID.Trim() == customerID.Trim());

LINQ to SQL knows how to handle expression trees, and it knows about Trim, and string equality. It doesn't know how to handle calls to arbitrary methods you've written.

The simplest approach to this is probably to change your method to this:

Expression<Func<Customer, bool>> CustomerIdMatches(string customerID)
{
   return customer => customer.CustomerID.Trim() == customerID.Trim()
}

You can then use:

from customer in context.Customers.Where(CustomerIdMatches(customerID))
... rest of query here

Or you could even make your own extension method to do the Where part as well.

Making it more generally reusable for different fields is slightly tricky. In particular, it's not going to work well from query expressions... it's doable, but not terribly pretty.

The usage would look something like this:

var query = context.Customers
                   .Where(TrimAndCompare(customer => customer.CustomerID,
                                         customer => customerID));

Not terribly nice :(