1

I am trying to check if a given search term can be found in an Id through Linq.

I tried with where(x => x.Id.ToString().Contains(term)) but that doesn't work as it gives an exception that .ToString is not supported

I also tried to use SqlFunctions.StringConvert((double)x.Id).Contains(term), but that doesn't work as well as it gives the exception 

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

How can I get this query working without loading everything into memory (which is not what I want)

The application is using EF 5.0 and is connected to an oracle DB

SqlFunctions.StringConvert((double?)x.Id) does not work.

Strike08
  • 267
  • 1
  • 2
  • 17
  • What type is `x.Id`? – Shane Ray Mar 12 '18 at 14:04
  • Why are you casting to `double`? It seems like it takes a `Nullable`. – juharr Mar 12 '18 at 14:04
  • 1
    Are you missing parentheses after ToString? – Mahdi Mar 12 '18 at 14:04
  • @ShaneRay x.Id is of the type short – Strike08 Mar 12 '18 at 14:04
  • https://stackoverflow.com/a/34054349/2946329 – Salah Akbari Mar 12 '18 at 14:04
  • @Mahdi that was a typo – Strike08 Mar 12 '18 at 14:05
  • @S.Akbari That loads everything in memory which is not what I want. I want to run the query on the database as much as possible – Strike08 Mar 12 '18 at 14:07
  • Honestly checking if a number "contain" a certain string of digits seems odd. What exactly are you trying to test for. There may be more mathematical ways to approach this. – juharr Mar 12 '18 at 14:07
  • 1
    Note that `SqlFunctions.StringConvert` can only take a `double?` - but you can cast your` int` to a `double?` – Jamiec Mar 12 '18 at 14:07
  • @juharr a user is able to type a search string that filters on the Id and/or on the description. I want to find every record where the id or description (description isn't in the example as I got that functioning without problem) contains the given term. – Strike08 Mar 12 '18 at 14:11
  • @Strike08 Well first you could try to convert that search string into an `int` and if it fails there's no point in doing the check on the Id as it will not match. If you can limit the numeric search to be a begins with then you could generate ranges to search which would be faster than the resulting `Like` that will require a complete table scan. But if it has to be anywhere in the `Id` then that of course will not work. – juharr Mar 12 '18 at 14:16
  • 1
    @Strike08 I've removed the duplicate so you can properly answer the question. Also I added the Oracle tag as that is relevant here. – juharr Mar 12 '18 at 15:06

1 Answers1

1

To get this working you need to call the To_Char method of the Oracle database. To do this you need to map the function in the Edmx like so:

<Function Name="To_Char" ReturnType="varchar2" Aggregate="false" BuiltIn="true" NiladicFunction="false" IsComposable="true" ParameterTypeSemantics="AllowImplicitConversion" StoreFunctionName="To_Char" Schema="{INSERT OWN SCHEMA HERE}">
      <Parameter Name="value" Type="number" Mode="In" />
</Function>

After this you need to create the following class + method:

public static class OracleFunctions
{
    [EdmFunction("Model.Store", "To_Char")]
    public static string To_Char(decimal input)
    {
        throw new NotImplementedException();
    }
}

And when you done this you can call it in the where statement like this

.Where(OracleFunctions.To_Char(x.Code).Contains(term))
Strike08
  • 267
  • 1
  • 2
  • 17