linq - How do I query an integer column for "starts with" in Entity Framework?

Question

I have a column that's defined as an integer in EF (Code First). I want to search it using "starts with." Now, I can do this:

Where(x => SqlFunctions.StringConvert((double)x.AccountNumber).StartsWith(searchTerm))

However, SqlFunctions.StringConvert() gets translated to the T-SQL function STR(), which left-pads the result for reasons which are beyond my comprehension.

Also, I can't use string.TrimStart() because it's not supported by the Entity Framework.

Can anyone lend any help?

Answer 1

Trim() and TrimStart() work in LINQ to Entities, so you can use:

Where(x => SqlFunctions.StringConvert((double)x.AccountNumber)
    .TrimStart().StartsWith(searchTerm))

TrimStart translates into LTRIM in SQL. With searchTerm = 123 for example you get something like:

WHERE LTRIM(STR( CAST( [Extent1].[AccountNumber] AS float))) LIKE N'123%'