LINQ To SQL and Extension Methods
(
Sep 11 2007 - 11:03:53 PM by
Timothy Khouri) - [
print blog
post]
As I continue to use LINQ more and more, I'm learning some neat things that I never knew before (obviously)... so today's little "gotcha" was something that I found to be very insightful. Basically, without boring anyone with too many details, I made my own C# 3.0 extension method that adds a method to the System.String class that would allow me to return a "phone number" formatted string.
The reason for this is that we have our "PhoneNumber" field in the database as a VARCHAR(10) (meaning if you entered your phone number, it would be with no spaces, hyphons, etc). So, because the phone number would be returned in an ugly format - 9417080905 - my extension method would return it nicely formatted - (941) 708-0905.
Even though my code complied without errors, what I didn't know was that at runtime, LINQ to SQL would try to build T-SQL statements out of my LINQ expression including my custom function! Needless to say, there is no T-SQL equivilant for "ReturnAsPhoneNumber", so the whole thing bombed out :)
public IQueryable FindCustomers(string searchFirstName, string searchLastName, string searchZip)
{
return from customer in MyLinqDataContext.Customers
where customer.FirstName.Contains(searchFirstName)
&& customer.LastName.Contains(searchLastName)
&& customer.Zip.Contains(searchZip)
select new
{
FirstName = customer.FirstName,
LastName = customer.LastName,
Zip = customer.Zip,
PhoneNumber = customer.PhoneNumber.ReturnAsPhoneNumber()
};
}
That above was my function, which would bomb out with this error: Method 'System.String ReturnAsPhoneNumber(System.String)' has no supported translation to SQL.
I thought that the string value would be returned, and THEN my "ReturnAsPhoneNumber" method would be called, but that's not the way LINQ expressions work. Instead, they translate all that above into SQL statements FIRST, and then they return the processed result set directly from SQL.
The solution? Simple... don't use an extension method. If I change the "PhoneNumber = ..." line slightly, it works fine. Example:
select new
{
FirstName = customer.FirstName,
LastName = customer.LastName,
Zip = customer.Zip,
PhoneNumber = ReturnAsPhoneNumber(customer.PhoneNumber)
};
That is totaly acceptable as far as LINQ is concerned... Learn something new every day!