user trimstart in entity framework query

Question

How can I use trimstart so entity framework will understand what to do?

Here is my query:

string number="123";
Workers.Where(x => x.CompanyId == 8).Where(x => x.Number.TrimStart('0') == number);

How can I make this query work without the AsEnumerable (there are a lot of workers in company 8)?

Indeed. I guess I meant are the string contents always numeric (like your example) or is something like `AAK89234` a valid "number"? — Austin Salonen, Oct 29 '11 at 03:23

score 22 · Accepted Answer · answered Oct 29 '11 at 03:59

Try using SqlFunctions.PatIndex for this. I tested a query similar to the one below with the values "000123", "000One", "abcde" and it correctly selected rows with the values "123", "One", and "abcde".

Workers.Where(x => x.CompanyId == 8 && 
                   x.Number.Substring(SqlFunctions.PatIndex("%[^0]%", x.Number).Value - 1) == number);

score 1 · Answer 2 · answered Dec 13 '21 at 15:41

1

I had a case where simply using

.AsEnumerable()

made it work.

Workers.AsEnumerable().Where(x => x.CompanyId == 8).Where(x => x.Number.TrimStart('0') == number);

answered Dec 13 '21 at 15:41

Jan BA

11
2

score 0 · Answer 3 · answered Oct 29 '11 at 10:44

0

It is because trim functions in SQL can trim only whitespaces so EF supports TrimStart method but only without parameter.

Your requirement should in the first place lead to discussion about increasing quality of data in your database! Your data don't provide quality to support this type of queries in efficient way.

answered Oct 29 '11 at 10:44

Ladislav Mrnka

360,892
59
660
670

I understand what you meen, this is a problem that the data stored with zeroes. But right now this is the system and I need to find a solution for that. Thanks. – Naor Oct 29 '11 at 12:34
Downvoted - I agree the database is where the data should be cleaned up but third-party db's don't always give control of that. A more useful answer would be a solution to the problem like Jeff mentioned. -Too many theoretical answers and not enough practical on SO. – hewstone Apr 22 '15 at 18:49
Upvoted. It's good that some people point out the actual issues, this is how people learn from each other. Would be good to give a practical answer as well though, but Jeff already did. – Nick N. Aug 30 '18 at 14:31

user trimstart in entity framework query

3 Answers3

Linked