Search code examples
c#entity-frameworklinqlinq-to-entities

C# Linq string compare with indexOf

I have a table that has a string field that holds hostnames. They are mostly fully qualified domain names but over the years the domain bits after the first "dot" have changed as various DNS changes have fallen on us. So I might have the machine "tom" that is in the table as:

tom.company.com
tom.it.company.com
tom.newComapnyBranding.com
...

I often have to do comparisons against the "current" host name and this historical store. Doing something like:

WHERE
    UPPER(SUBSTRING(@foo, 1, CHARINDEX(".", @foo))) = 
    UPPER(SUBSTRING(myDB.myTable.machineName, 1, CHARINDEX(".", myDB.myTable.machineName)))

Ok, I am trying to convert one of these into a Linq query but am stumbling on the "index" part. I have come as close as:

myTable.machineName.ToUpper().Substring(0, myTable.machineName.IndexOf("."))
    .Equals(foo.ToUpper().Substring(0, foo.IndexOf(".")))

but visual studio is complaining about "IndexOf". It claims that I need to change the IndexOf to:

IndexOf(".", StringComparison.Ordinal))

but when i run it I get the exception message:

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

How do you do this kind of indexed based substring in Linq?

Solution

  • Expressions given to Entity Framework are restricted to ones that can be translated into SQL. EF doesn't know how to translate String.IndexOf into SQL.

    You can instead use SqlFunctions.CharIndex:

    SqlFunctions.CharIndex(machineName, ".")