ASP.NET MVC Search Page - Integer StartsWith On Linq + EF4

Question

So, in my last post I was asking how to build a dynamic search filter using LINQ and EF4 (See Here) and finally came up with the solution of building the expression as a string and parse it to an expression using the Dynamic LINQ library.

I that solved the problem. I was able to generate a Expression<Func<TSource, out bool>> and pass it to the Where() method of the DbSet. I am also trying to do this using MySql as a database behind EF4.

The problem came when I tried to apply string operations to integers, like searching a database record which consecutive number starts with 1234.

My initial expression was something like: record.ConsecutiveNumber.ToString().StartsWith("1234"). Sadly, as expected, things were not that easy as EF4 fails to query the DbSet with exception:

"LINQ to Entities does not recognize the method 'System.String ToString()' method, and this method cannot be translated into a store expression."

After some Google search I found that this is a common problem. But C'MON! Is there a way to perform a search function that can search records with a consecutive number starting by "1234"?

How pros implement search features with EF4? This is with a single property filter. What if I wanna add multiple filters? God, my head hurts... :/

Thanks!

EDIT:

Thought #1: What about a stored procedure? What about calling a MySql stored procedure from Linq? Am I aiming way too high?

score 5 · Answer 1 · answered Mar 27 '11 at 03:42

5

You can use the SqlFunctions.StringConvert method. It requires a double (or decimal) so you'll have to cast your int ConsecutiveNumber.

Replace:

record.ConsecutiveNumber.ToString().StartsWith("1234")

With:

SqlFunctions.StringConvert((double)record.ConsecutiveNumber).StartsWith("1234")

answered Mar 27 '11 at 03:42

Kirk Woll

76,112
22
180
195

Is this compatible with EF4 + MySql setup (Code-First)? – Luis Aguilar Mar 27 '11 at 03:48
Good question, I can't test that easily. But given the following post, I'm thinking it's unlikely. :( http://forums.mysql.com/read.php?47,396177,396177 – Kirk Woll Mar 27 '11 at 03:51
Uhm, too bad. I hope this is possible somehow.. :/ – Luis Aguilar Mar 27 '11 at 03:55
You should have mentioned MySQL in your question. – OJ. Mar 30 '11 at 04:17

score 0 · Accepted Answer · edited May 23 '17 at 12:14

0

Have you looked at the Dynamic LinQ Library: http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx

And for your question How to use "contains" or "like" in a dynamic linq query?

Previously I have gotten the code for this lib and just taken a look inside, it is pretty easy to follow.

edited May 23 '17 at 12:14

Community

1
1

answered Mar 30 '11 at 05:33

Slappy

4,042
2
29
41

I've tried that library. In the end it generates a Linq lambda expression, when passed to the where method, the app crashes with an exception stating that ToString is not supported in Linq2Entities. I can make contains or like evaluations on String data but what I want is to evaluate if there are any records whose consecutive number starts with some number. Consecutive number is not a string, is an integer, so my first approach was to convert it to string first and then use contains. But no ToString supported. – Luis Aguilar Mar 30 '11 at 14:50

score 0 · Answer 3 · edited May 23 '17 at 12:22

This would be my thought process on getting it to work. Hopefully it points you in the right direction.

According to other posts SqlFunctions.StringConvert((double)record.ConsecutiveNumber) works for Sql Server.

Problem with converting int to string in Linq to entities

And here is relevant information on linq conversions.

Linq int to string

And here is an answer hinting at writing your own sql function for stringconvert

Using a SQL Function in Entity Framework Select

If SqlFunctions.StringConvert doesn't work for you I'd suggest looking at figuring out how to do it in Sql and then writing your own [EdmFunction()] attribute based method.

score 0 · Answer 4 · answered Apr 01 '11 at 16:52

I haven't got a clue if this will work over Linq to EF or not but presuming that they mapped the Math operations, this might solve your need:

record.ConsecutiveNumber / Math.Pow(10, Math.Truncate(Math.Log10(record.ConsecutiveNumber) - 3)) == 1234

This is basically dividing the number by a power of 10 just big enough to leave the first 4 digits.

I know this is very hacky and inefficient even if it works, but there you go. :)

score 0 · Answer 5 · answered Apr 02 '11 at 06:15

Any method calls in a LINQ to Entities query that are not explicitly mapped to a canonical function will result in a runtime NotSupportedException exception being thrown.

Check mapping canonical function here: http://msdn.microsoft.com/en-us/library/bb738681.aspx

In this case, you can use Math function. (I don't think code first can use in product project at that time)

ASP.NET MVC Search Page - Integer StartsWith On Linq + EF4

5 Answers5