LIKE in OData query

Question

I am converting a project from using linq2sql to use an odata source. I am trying to lookup a user like this...

FrameworkEntities db = new FrameworkEntities(
    new Uri("http://localhost/odata/FrameworkService.svc"));
User user = db.Users.Where(
    u => SqlMethods.Like(u.UserName, UserName)).FirstOrDefault();

but am getting the error "Method 'Boolean Like(System.String, System.String)' cannot be used on the client; it is only for translation to SQL." This was possible in linq2sql so I am just wondering how I would change this so that it would work with the odata service. I haven't found any articles about it.

Answer 1

Like the error says, SqlMethods.Like() is for translation to SQL. oData queries don't get translated to SQL, so you can't use the method. Your best bet is to use IndexOf with StringComparison.InvariantCultureIgnoreCase to emulate a case insensitive version of String.Contains.

Oh...and you can combine your calls to Where and FirstOrDefault:

User user = db.Users
              .Where(u => u.UserName.IndexOf(Username, 
                  StringComparison.InvariantCultureIgnoreCase) > 0)
              .FirstOrDefault();