{"LINQ to Entities does not recognize the method 'Boolean IsLetter(Char)' method, and this method cannot be translated into a store expression."}

Question

I want to get all items in the database using LINQ where the Title starts with special characters or number, I already tried the code below but it's not working.

Thanks

result = (from asset in _db.Query<Asset>()
                          where !char.IsLetter(asset.Title[0])
                          select new AssociatedItem { Id = asset.AssetId, Title = asset.Title, Type = Constants.FeedbackTypes.ASSET }).ToList();

Related posts - [LINQ to Entities does not recognize the method](https://stackoverflow.com/q/7259567/465053) & [Entity Framework Specification Pattern Implementation](https://stackoverflow.com/q/2352764/465053) — RBT, Mar 01 '19 at 11:25
Another related post - [LINQ to Entities does not recognize the method 'System.String ToString()' method, and this method cannot be translated into a store expression](https://stackoverflow.com/q/5899683/465053) — RBT, Mar 01 '19 at 11:30

score 1 · Accepted Answer · answered Jan 12 '16 at 03:35

1

That's because char.IsLetter is not a dbFunction.

You can apply where after converting the results ToList()

result = (from asset in _db.Query<Asset>()                         
                      select new AssociatedItem { Id = asset.AssetId, Title = asset.Title, Type = Constants.FeedbackTypes.ASSET }).ToList()
.Where(a => !char.IsLetter(a.Title[0])).ToList();

PS: Try to identify some other where clause for the db query to limit the results.

answered Jan 12 '16 at 03:35

Kosala W

2,133
1
15
20

@Jeff: That's why I said _Try to identify some other where clause for the db query to limit the results._. What he is trying to do is bad from SQL aspect too, not just from LINQ aspect. Do you know why it is bad from SQL aspect? – Kosala W Jan 12 '16 at 03:44

score 1 · Answer 2 · answered Jan 12 '16 at 04:10

1

I'd give the SqlMethods class from the System.Data.Linq.SqlClient namespace a shot.

result = (from asset in _db.Query<Asset>()
                     where !SqlMethods.Like(asset.Title, "[a-Z]%")
                     select 
                            new AssociatedItem 
                            { 
                                Id = asset.AssetId, 
                                Title = asset.Title, 
                                Type = Constants.FeedbackTypes.ASSET 
                            }).ToList();

answered Jan 12 '16 at 04:10

Manuel Zelenka

1,616
2
12
26

This solution may work. But we should encourage OP to find a different filtering criteria for the where clause. This kind of application of functions on indexed fields is a bad practice from SQL perspective. – Kosala W Jan 12 '16 at 04:51
Just to learn something new: why is this considered bad practice from SQL perspective? – Manuel Zelenka Jan 12 '16 at 05:01
1

Only sargable predicates should be used in this kind of situations. Otherwise you will not be able to get the advantage of an index. https://en.wikipedia.org/wiki/Sargable – Kosala W Jan 12 '16 at 05:21
Well then basically I did the right thing anyway since my query is translating to a NOT LIKE operation. But I still could improve it by making it a LIKE statement – Manuel Zelenka Jan 12 '16 at 05:29

{"LINQ to Entities does not recognize the method 'Boolean IsLetter(Char)' method, and this method cannot be translated into a store expression."}

2 Answers2