search decimal values in Linq query

Question

I have this search terms

[HttpPost]
public ActionResult Index(string searchString)
{
    if (!String.IsNullOrEmpty(searchString))
    {
        students = students.Where(s => s.FIRST_NAME.Contains(searchString) 
            || s.LAST_NAME.Contains(searchString)
            || s.PERSONAL_NUMBER.Contains(searchString)
            || s.ACD_UNI_DEGREES.DEGREE.Contains(searchString)
            || s.ACD_UNI_FACULTIES.FACULTY.Contains(searchString)
            || s.ACD_UNI_SPECIALIZATIONS.SPECIALIZATION.Contains(searchString)
            || SqlFunctions.StringConvert(s.SEMESTER).Contains(searchString) 
            || s.COR_PAYER_STATUS.NAME.Contains(searchString)
            || SqlFunctions.StringConvert(s.CREDIT_COUNT).Contains(searchString));
    }

    return View(students.ToList());
}

but on debugging it throws an exception:

System.NotSupportedException: The specified method 'System.String StringConvert(System.Nullable`1[System.Decimal])' on the type 'System.Data.Objects.SqlClient.SqlFunctions' cannot be translated into a LINQ to Entities store expression.

The problem is here:

SqlFunctions.StringConvert(s.SEMESTER).Contains(searchString)

SEMESTER is decimal and searchString is string. How can I improve that?

make a sproc, convert the decimal col to a string and do the 'like' comparisom on the server. call the sproc via SqlClient — Ewan, Apr 14 '15 at 08:13
Since it's nullable, you could first check `s.SEMESTER != null && s.SEMESTER.HasValue` and then `s.SEMESTER.Value.ToString().Contains(searchString)` — Icepickle, Apr 14 '15 at 08:13
according to [this thread](http://stackoverflow.com/questions/5771299/linq-to-entities-stringconvertdouble-cannot-be-translated-to-convert-int-to-s) the `SqlFunctions` is database dependent, i.e. it only works with SQL Server. — default, Apr 14 '15 at 08:20
select * from students s where convert(varchar[50] , s.creditcount) like '%4%' — Ewan, Apr 14 '15 at 08:28
however it should be noted that what you are trying to do is going to be very inefficient. in order to work out if the number 4 is contained in any of the decimals, the db will have to convert them all to strings before doing the comparisom. consider instead changing the db col to a varchar instead of decimal — Ewan, Apr 14 '15 at 08:29
or really, you could change half those conditions to equals rather than contains — Ewan, Apr 14 '15 at 08:31
well, you can always call `.ToList()` earlier and then do further checks. But according to the [documentation](https://msdn.microsoft.com/en-us/library/dd487127%28v=vs.110%29.aspx) you *cannot call this function directly. This function can only appear within a LINQ to Entities query.* So you would have to write the logic some other way (I guess via `s.Semester.ToString()`?). So `.Where(s => s./* methods that can be translated*/).ToList().Where(s => s./* methods that cannot be translated*/);` — default, Apr 14 '15 at 08:33
Did you try if `s.SEMESTER.ToString()` is supported? (it is for Sql Server in EF 6.1+) — Gert Arnold, Apr 14 '15 at 12:23

score 1 · Accepted Answer · answered Apr 14 '15 at 11:42

1

You should ensure that you are using System.Data.Entity.SqlServer.SqlFunctions. I rewrited your query and everything works fine.

answered Apr 14 '15 at 11:42

Marcin J

378
4
15

Anshul Nigam · Answer 2 · 2015-05-25T07:23:52.130

Though it is a old question posting answer so that other will do it correctly
First thing to correct is we need to convert first integer to decimal and then search, assuming datatype for searchable column is also decimal

So first convert your search string to decimal

               decimal dec;
               bool IsDecimal = decimal.TryParse(searchString, out dec);

               // Truncating search to 2 digits this should be equal to your db            
               // column precision so that rounding case are handled properly 
               decimal TruncateDigits = -1;
               if (IsDecimal)
                   TruncateDigits = Convert.ToDecimal(Math.Truncate(dec * 100) / 100);

Now your search filed is same as your db coloumn precison and ready to get searched

    students = students.Where(s => s.FIRST_NAME.Contains(searchString) 
            || s.LAST_NAME.Contains(searchString)
            || s.PERSONAL_NUMBER.Contains(searchString)
            || s.ACD_UNI_DEGREES.DEGREE.Contains(searchString)
            || s.ACD_UNI_FACULTIES.FACULTY.Contains(searchString)
            || s.ACD_UNI_SPECIALIZATIONS.SPECIALIZATION.Contains(searchString)
            || (IsDecimal && (decimal.Round(s.SEMESTER, 2) == TruncateDigits))

Important point to note

      (IsDecimal && (decimal.Round(s.SEMESTER, 2) == TruncateDigits))

Here again we will round to db column precision to handle rounding.

Also SqlFunctions.StringConvert will do rounding internally so if your number is db is 68.88 and your are searching for 68 it wont search because StringConvert will give you 69 (rounding) so if you want to search for 68 then you need to use Floor function
Something like

     SqlFunctions.StringConvert(decimal.Floor(s.SEMESTER)).Contains(searchString)

Hope this helps !!!

NekoMisaki · Answer 3 · 2019-01-02T16:56:21.513

I am facing the same problem right now with exactly the same principle you are aiming for. After a long search, I finally came to this:

[HttpPost]
public ActionResult Index(string searchString){
    searchString = searchString.Replace(",", ".");

    if (!String.IsNullOrEmpty(searchString)){
        students = students.Where(s => s.FIRST_NAME.Contains(searchString) 
        || s.LAST_NAME.Contains(searchString)
        || s.PERSONAL_NUMBER.Contains(searchString)
        || s.ACD_UNI_DEGREES.DEGREE.Contains(searchString)
        || s.ACD_UNI_FACULTIES.FACULTY.Contains(searchString)
        || s.ACD_UNI_SPECIALIZATIONS.SPECIALIZATION.Contains(searchString)
        || (s.SEMESTER.ToString()).Contains(searchString) 
        || s.COR_PAYER_STATUS.NAME.Contains(searchString)
        || SqlFunctions.StringConvert(s.CREDIT_COUNT).Contains(searchString));
    }
    return View(students.ToList());
}

You are casting the decimal to string normally with ToString(). However the bracetts are the magic weapon here: (s.SEMESTER.ToString()) this makes a difference.

This syntax will allow you to search with the notation . (Example: 1203.4 instead of 1203,4). Which is not really user-friendly.

To solve this, use the .Replace(',', '.') on your searchString. This will apply the trick on background and no one will notice.

I hope it helped anyone struggling with the same problem.

search decimal values in Linq query

3 Answers3

Linked