In search Funcality, search with decimal datatype in mvc

Question

I have question regarding search with decimal datatype. I have decimal datatype in table, which I have to call in Grid View, because its existing database and I dont have permission to change anything in database. I was following this link search decimal values in Linq query but didnot work. Please advise where I am doing mistake.

Here is my View

    @using (Html.BeginForm("Index", "Incident", FormMethod.Post, new { id = 
    "SearchForm" }))
    {
    <button class="glyphicon glyphicon-search" style="width: 50px; height: 
    50px;"></button>
    <input type="text" name="keyword" placeholder="Search..." 
   value="@Session["Search_Keyword"]" />
    <br />
    <br />
    @Html.Grid(Model).Named("ITSMSearchGrid").Columns(x =>
    {
        x.Add(y => y.IncidentNumber).Titled("Incident Number");
        x.Add(y => y.CreatedBy).Titled("Network Login");
        x.Add(y => y.ProfileFullName).Titled("Full Name");
        x.Add(y => y.Site);
        x.Add(y => y.Subject);
        x.Add(y => y.Service);
        x.Add(y => y.OwnerTeam).Titled("Team Owner");
        x.Add().Encoded(false)
            .Sanitized(false)
            .SetWidth(20)
            .RenderValueAs(y => Html.ActionLink(" Details", "Details", new { id = y.RecId }, new { @class = "btn btn-primary fa fa-chevron-circle-down" }));
    }).WithPaging(50).Sortable(true)
}

Here is my controller

    public ActionResult Index(string keyword)
    {
        List<Incident> incidentList = _context.Incidents.Where(x => 
    x.TypeOfIncident == "Incident").ToList();
        decimal dec;
        bool IsDecimal = decimal.TryParse(keyword, 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);
        if (keyword == null)
        {
            if (Session["Search_Keyword"] != null)
            {
                keyword = (string) Session["Search_Keyword"];
            }
            else
            {
                return View(incidentList.OrderBy(x => x.IncidentNumber).ThenBy(x => x.CreatedBy).ToList());
            }
        }
        else
        {
            Session["Search_Keyword"] = keyword;
        }
        return View((keyword == ""
            ? incidentList
            : incidentList.Where(x =>
            SqlFunctions.StringConvert(x.IncidentNumber).Contains(keyword.ToUpper())||
                x.CreatedBy.ToUpper().Contains(keyword.ToUpper())
               ).ToList()).OrderBy(x => x.IncidentNumber).ToList());
        // return View(incidentList);
    }

And this is throwing me error "System.NotSupportedException: This function can only be invoked from LINQ to Entities."

Can you elaborate a little more? What's wrong with what you have? — Matt Spinks, Jan 08 '18 at 21:39
its giving me error "System.NotSupportedException: This function can only be invoked from LINQ to Entities.." — Unexpected_error, Jan 08 '18 at 21:40
What error and where is it thrown? You need to include that with your question. — Camilo Terevinto, Jan 08 '18 at 21:40
It si throwing m this error "System.NotSupportedException: This function can only be invoked from LINQ to Entities." — Unexpected_error, Jan 08 '18 at 21:41
Why do you want to call a **`SQL`** function in an **in-memory** list? — Camilo Terevinto, Jan 08 '18 at 21:42
Ineed to search with decimal datatype which is "IncidentNumber". Its not working directly with hasValue. So I was trying like this.. but nothing worked. — Unexpected_error, Jan 08 '18 at 21:45
I followed this link https://stackoverflow.com/questions/29622158/search-decimal-values-in-linq-query?answertab=active#tab-top — Unexpected_error, Jan 08 '18 at 21:46
I would really reconsider materializing all incidents in the first line. You could prolly just work with *var*. Make use of defered execution move all the filtering to the DB and meterialize(**ToList()**) in the returns. — user6144226, Jan 08 '18 at 21:55

score 2 · Answer 1 · answered Jan 08 '18 at 21:47

The error message is pretty clear, you are attempting to call a SQL function in an in-memory list. Change your code to this:

if (!string.IsNullOrEmpty(keyword))
{
    incidentList = incidentList
        .Where(x => x.IncidentNumber.ToString().Contains(keyword.ToUpper())
                 || x.CreatedBy.ToUpper().Contains(keyword.ToUpper())
        .OrderBy(x => x.IncidentNumber)
        .ToList();
}

return View(incidentList);

Notice that I removed a .ToList() before the .OrderBy call that was only wasting resources.

In search Funcality, search with decimal datatype in mvc

1 Answers1