CODEWITHSUNDEEP
c#linqentity-framework
gsiradze
gsiradze

Reputation: 4733

search decimal values in Linq query

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?

Upvotes: 1

Views: 2900

Answers (3)

NekoMisaki
NekoMisaki

Reputation: 99

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.

Upvotes: 0

Anshul Nigam
Anshul Nigam

Reputation: 1628

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 !!!

Upvotes: 0

Marcin J
Marcin J

Reputation: 378

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

Upvotes: 1

Related Questions