Entity Framework search expression

Question

I have to make search through field EmployeeId. I also have parameter empId which can be something like this: '123' In my database there's next values:

'0123' - what I need 
'10123'
'1234'

and so on. My target - take a record which ends on empId and has no or one and more leading 0. I tried to write something like this:

var result = from member in MembersSet
             where SqlMethods.Like(member.EmployeeId, "%" + empId) &&
                   !(SqlMethods.Like(member.EmployeeId, "%[^0]%" + empId))
             select member.Id;

But I get an error LINQ to Entities does not recognize the method 'Boolean Like(System.String, System.String)' method, and this method cannot be translated into a store expression.

May be there is a workaround?

score 1 · Accepted Answer · edited May 23 '17 at 12:11

The SqlMethods is for Linq-To-Sql, not EntityFramework. There's an equivalent for EF - SqlFunctions (System.Data.Objects.SqlClient.SqlFunctions). You can use the PatIndex function to do what you want. It's worth noting that this will make your code SQL Server specific.

You can do the initial expression without using PatIndex by using the standard String EndsWith function, which will get translated by EF automatically.

var result = from member in MembersSet
             where member.Id.EndsWith("123")
                 && SqlFunctions.PatIndex("[^0]%123", member.Id) == 0
             select member.Id;

If the values of Id will always be numeric (even with leading zeros), you could try converting them to Integers on the server, by defining an additional method to handle parsing to int (EF won't translate Int32.Parse) - see this question for details.

Entity Framework search expression

1 Answers1