Mapping POCO to string in Linq to Sql so that I can use Linq on my POCO

Question

I'm accessing a database column where a phone number is stored as a varchar in the format +a-b:c where a = country code, b = phone number and c = extension. E.g. +44-07700123456:123

So I have a type that handles serialization along the lines of:

public struct PhoneNumber {
  public PhoneNumber(string val) {  /*...*/ }
  public override string ToString() { /*...*/ }
  public static PhoneNumber TryParse(string val) { /*...*/ }
}

and a POCO:

public class Customer {
  public PhoneNumber? HomePhone;
}

Then in my data access code some Linq along the lines of:

public IQueryable<Customers> GetCustomers() {
  var customers = (from c in DataContext.Customers
  select new Customer {
    HomePhone = PhoneNumber.TryParse(c.HomePhone)
  });
  return customers;
}

So far so good, this all works fine when retrieving records from the database, but my problem is I can't perform a Linq query on the result like:

GetCustomers().Where(c => c != null && c.HomePhone.Value.ToString().Contains("123"));

I get a an error "Method 'System.Nullable`1[PhoneNumber] TryParse(System.String)' has no supported translation to SQL". Now I know I can perform the phone number search in GetCustomers() but this is not ideal.

Is there a way I can let Linq know how to translate my linq to sql? So that I can I do something like GetCustomers().Where(c => c.HomePhone.Value.ToString().Contains("123")) ?

P.S. Not sure on the title of this one, any alternatives are welcome.

Answer 1

The problem is that your PhoneNumber.TryParse() call is getting translated into an expression that's sent up to the server, which it doesn't understand. Furthermore, there's no way to tell SQL about your structure so you won't be able to do a server-side query based on the parsed value. One option is to capture the string value and move the PhoneNumber.TryParse() bit somewhere else. For example:

public class Customer {
  public string HomePhoneString;

  private bool _HomePhoneParsed;
  private PhoneNumber? _HomePhone;
  public PhoneNumber? HomePhone
  {
    get
    {
      if(!_HomePhoneParsed)
      {
        _HomePhone = PhoneNumber.TryParse(HomePhoneString);
        _HomePhoneParsed = true;
      }
      return _HomePhone;
    }
  }
}

public IQueryable<Customers> GetCustomers() {
  var customers = (from c in DataContext.Customers
  select new Customer {
    HomePhoneString = c.HomePhone
  });
  return customers;
}

And then you can either query server-side:

GetCustomers().Where(c => c.HomePhoneString != null && c.HomePhoneString.Contains("123"))

Or client-side:

GetCustomers().AsEnumerable()
              .Where(c => c.HomePhone != null && c.HomePhone.Value.Contains("123"))

Answer 2

Following up on this, an alternative solution is to change PhoneNumber to a class and add a 'Value' property to serialize/deserialize like:

public class PhoneNumber {

  public override string ToString() { /*...*/ }
  public Parse(string val) { /*...*/ }

  public string Value {
    get {
      return ToString();
    }
    set {
      Parse(value);
    }
  }
}

This kind of code is not really suitable for a property but it does allow you to initialize the HomePhone object in the Linq query like:

var customers = (from c in DataContext.Customers
  select new Customer {
    FirstName = c.FirstName,
    HomePhone = new PhoneNumber {
      Value = c.HomePhone
    }
  });

Which then means you can use the serializer like:

customers.Where(x => x.HomePhone.Value.Contains("123"));